R访问MySQL

前言

关系型数据库对于数据的查询效率,这是大家公认的,尤其对于当前的 Data 超Big时代。而对于数据的处理显得极为吃力,恰逢其时,统计软件应运(数据分析)而生,将二者完美结合起来使用,何乐而不为?但这样的一个共同接口,真是众里寻他千百度。纵观统计软件,R(其他的统计软件,笔者未涉水,不敢狮子大开口)开发者提供了各色接口,来实现这种完美结合。论起数据库,MySQL在笔者的身体还有那么一点点的存在感,现将其抛出来,一探R与MySQL二人结合的完美世界。

R访问MySQL

系统环境:

Windows 10 64bit
R 3.2.3
MySQL 5.7.11

RODBC

ODBC(Open DataBase Connectivity ) ,俗名为开放式数据库连接,它允许通过一个共同的接口访问各种数据库.也许RODBC包是为R访问MySQL提供共同接口而生的,当然不止MySQL,SQL Server ,Excel等.

RODBC 的运转流程:

配置DSN(数据源)

建立DSN(Data Source Name) ,俗称数据源名称 ,步骤如下:
- 右击此电脑 $->$ 控制面板 $->$ 管理工具 $->$ 数据源,出现如下的界面;
- 点击系统系统DSN,选择MySQL,点击添加,如下的界面所示;

选择所要安装的数据源驱动程序,这里选择MySQL ODBC Unicode Driver,点击完成,出现如下的界面;

按照提示,填写相应的信息
- 自定义数据源名称,这里命名为RMySQL;
- 由于数据库安装在本地,TCP/IP Server 填写为:localhost 或者127.0.0.1;
- 用户名、密码自行设置;
- 选择所要查询的数据库,这里以MySQL自带的数据库world进行测试.
- 最后点击Test进行测试,检验配置是否正确.最后出现如下的界面.

配置好的数据源已经为我们提供了连接和访问数据库的所有必要信息.

连接数据库

调用RODBC R包中的odbcConnect()进行数据库的连接.

> library(RODBC)
> channel <- odbcConnect(dsn = "RMySQL")
> str(channel)
Class 'RODBC'  atomic [1:1] 2
  ..- attr(*, "connection.string")= chr "DSN=RMySQL;"
  ..- attr(*, "handle_ptr")=<externalptr> 
  ..- attr(*, "case")= chr "tolower"
  ..- attr(*, "id")= int 54353
  ..- attr(*, "believeNRows")= logi TRUE
  ..- attr(*, "colQuote")= chr "`"
  ..- attr(*, "tabQuote")= chr "`"
  ..- attr(*, "interpretDot")= logi TRUE
  ..- attr(*, "encoding")= chr ""
  ..- attr(*, "rows_at_time")= num 100
  ..- attr(*, "isMySQL")= logi TRUE
  ..- attr(*, "call")= language odbcDriverConnect(connection = "DSN=RMySQL")

R与MySQL

调用RODBC R包中的函数进行数据库的访问.

odbcGetInfo()函数获取数据库的连接信息;

> odbcGetInfo(channel = channel)
             DBMS_Name               DBMS_Ver        Driver_ODBC_Ver 
               "MySQL"           "5.7.11-log"                "03.80" 
      Data_Source_Name            Driver_Name             Driver_Ver 
              "RMySQL"         "myodbc5w.dll"           "05.03.0004" 
              ODBC_Ver            Server_Name 
          "03.80.0000" "localhost via TCP/IP"

sqlQuery()函数调用MySQL语句进行查询操作;

[查询world中的表] [lang: R&SQL]

> sqlQuery(channel = channel ,query = "SHOW TABLES")
  Tables_in_world
1            city
2         country
3 countrylanguage

sqlFetch()获取表的内容.

> library(dplyr)

载入程辑包: ‘dplyr’

The following objects are masked from ‘package:stats’:

    filter, lag

The following objects are masked from ‘package:base’:

    intersect, setdiff, setequal, union

> sqlFetch(channel = channel ,sqtable = "city") %>% head()
  ID           Name CountryCode      District Population
1  1          Kabul         AFG         Kabol    1780000
2  2       Qandahar         AFG      Qandahar     237500
3  3          Herat         AFG         Herat     186800
4  4 Mazar-e-Sharif         AFG         Balkh     127800
5  5      Amsterdam         NLD Noord-Holland     731200
6  6      Rotterdam         NLD  Zuid-Holland     593321

...

odbcClose() 关闭数据库连接

1	> odbcClose(channel = channel)

RMySQL

调用DBI R包的dbDriver函数加载MySQL中的驱动程序,使得DBI接口知道它连接的数据库类型;

> library(DBI)
> library(RMySQL)
> drv <- dbDriver("MySQL")
> str(drv)
Formal class 'MySQLDriver' [package "RMySQL"] with 1 slot
  ..@ Id: int 0

调用RMySQL R包中的dbConnect()函数进行数据库的连接

> con <- dbConnect(drv = drv , dbname = "world" , user = "root" ,
+ password = "******" , host = "localhost")
> str(con)
Formal class 'MySQLConnection' [package "RMySQL"] with 1 slot
  ..@ Id: int [1:2] 0 3

dbGetQuery() 调用MySQL语句检索数据

[world.city表中数据的汇总] [lang: R&SQL]

> library(dplyr)

载入程辑包:‘dplyr’

The following objects are masked from ‘package:Hmisc’:

    combine, src, summarize

The following objects are masked from ‘package:stats’:

    filter, lag

The following objects are masked from ‘package:base’:

    intersect, setdiff, setequal, union

> city_data <- dbGetQuery(con ,"SELECT * FROM city")
> city_data <- dbGetQuery(con ,"SELECT * FROM city") %>% str()
'data.frame':	4079 obs. of  5 variables:
 $ ID         : int  1 2 3 4 5 6 7 8 9 10 ...
 $ Name       : chr  "Kabul" "Qandahar" "Herat" "Mazar-e-Sharif" ...
 $ CountryCode: chr  "AFG" "AFG" "AFG" "AFG" ...
 $ District   : chr  "Kabol" "Qandahar" "Herat" "Balkh" ...
 $ Population : int  1780000 237500 186800 127800 731200 593321 440900
 234323 201843 193238 ...

dbDisconnect() 关闭未使用的DBI连接对象

1 2	> dbDisconnect(conn = con) [1] TRUE

更多RMySQL知识详见http://blog.fens.me/r-mysql-rmysql/.

两种方式的差异

RODBC访问模式稳定、安全,但操作比较机械呆板,程序繁琐;
RMySQL访问模式灵活,安全系数不高(数据库的密码就这样爆在各位看官面前了),操作不稳定。

结束语

R与MySQL的结合体在大数据时代的能量真是超乎你的想象。实用案例,下回再见!