粉丝日志

Posted:

Nov 10, 2020

Tags:

DSN MySQL PostgreSQL R RODBC Ubuntu WIndows 数据库

Comments:

用RODBC连接数据库

R的极客理想系列文章，涵盖了R的思想，使用，工具，创新等的一系列要点，以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言，一直在小众领域闪耀着光芒。直到大数据的爆发，R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入，R语言的社区在迅速扩大成长。现在已不仅仅是统计领域，教育，银行，电商，互联网….都在使用R语言。

要成为有理想的极客，我们不能停留在语法上，要掌握牢固的数学，概率，统计知识，同时还要有创新精神，把R语言发挥到各个领域。让我们一起动起来吧，开始R的极客理想。

关于作者：

张丹，分析师/程序员/Quant: R,Java,Nodejs
blog: http://fens.me
email: bsspirit@gmail.com

转载请注明出处：
http://blog.fens.me/r-rodbc/

前言

在进行数据开发的时候，数据源经常是多样，有时候是Oracle，有时候是MySQL，有时候是PostgreSQL等，不同的数据库有不同的特征和不同的API。很难保证，开发人员对每种数据库都熟悉，而且一个项目如果使用超过3个数据库，程序复杂度会骤然升高。

微软提供了一个基于Window的数据开放接口统一方案就是ODBC，让所有的数据库都通过ODBC进行注册统一管理，我们只需要基于ODBC进行编程，就可以连接所有数据库了。RODBC包，则作为R语言连接ODBC的数据访问接口程序。

ODBC和RODBC包介绍
通过RODBC连接MySQL数据库
通过RODBC连接PostgreSQL数据库

1. ODBC和RODBC包介绍

ODBC(Open Database Connectivity，开放数据库互连)，是微软公司开放服务结构(WOSA，Windows Open Services Architecture)中有关数据库的一个组成部分，它建立了一组规范，并提供了一组对数据库访问的标准API（应用程序编程接口）。这些API利用SQL来完成其大部分任务。ODBC本身也提供了对SQL语言的支持，用户可以直接将SQL语句送给ODBC。一组数据的位置，可以使用 ODBC驱动程序访问该位置。

一个基于ODBC的应用程序对数据库的操作不依赖任何DBMS，不直接与DBMS打交道，所有的数据库操作由对应的DBMS的ODBC驱动程序完成。也就是说，不论是MySQL 还是 Oracle数据库，均可用ODBC API进行访问。由此可见，ODBC的最大优点是能以统一的方式处理所有的数据库。

RODBC包，为R语言实现了ODBC数据库连接。在RODBC中提供了两种主要功能函数，主要是内部 odbc* 开头的函数，命令使用相似的名称实现对C语言级别低级访问。 sql*开头函数，在更高级别上运行以读取，保存，复制和操作数据之间的数据框架和SQL表。

2. 通过RODBC连接MySQL数据库

我们尝试在Window操作系统进行ODBC的连接。

Window系统的环境：

win10 64bit professional
R version 3.6.1 64bit

2.1 配置ODBC数据源

首先，需要安装MySQL，具体安装过程请参考文章在Ubuntu中安装MySQL。

我们需要在win10操作中先配置MySQL到ODBC连接，所以需要安装ODBC的驱动程序，请下载Connector/ODBC，然后安装。

打开 “ODBC数据源管理器” 界面，配置MySQL到ODBC连接。

选用户DSN ，然后添加一个配置，会弹出选择数据源驱动程序，这个驱动是需要安装的，用来让数据库可以和ODBC进行连接。Window系统中，通常会默认支持MS SQL Server数据库。

选择 MySQL ODBC 后，输入MySQL连接的配置信息。

这样，MySQL和ODBC的连接就算是建立好了，我们就可以用R语言通过RODBC的通道和MySQL数据库进行通信了。DSN就是外部调用的名字。

2.2 安装RODBC包

接下来，我们安装RODBC，非常简单，一条语句就行了。

安装和加载RPostgreSQL包

> install.packages("RODBC")
> library(RODBC)

2.3 RODBC包API操作

基本的ODBC的API操作。

odbcConnect(“dsn”, uid=”username”, pwd=”password”),建立ODBC数据库连接
sqlTables(channel),查看数据库中的表
sqlFetch(channel, sqtable), 读取数据表并返回一个数据框
sqlQuery(channel, query), 向数据库提交一个查询，并返回结果
sqlSave(channel, mydf, tablename = sqtable, append = FALSE), 将一个数据框写入或更新(append=True)到数据库
sqlColumns(channel, sqtable) , 返回数据库表sqtable列的信息
sqlDrop(channel, sqtable), 从数据库删除一个表
sqlClear(channel, sqtable), 删除表中的内容
close(channel), 关闭连接

2.4 建立ODBC数据库连接

接下来，我们就可以使用RODBC进行ODBC的数据源连接了。


# 查看ODBC支持的数据源
> odbcDataSources()
                          mysql 
"MySQL ODBC 8.0 Unicode Driver" 

# 建立ODBC连接，设置DSN名字
> ocon1<-odbcConnect("mysql")

# 查看连接信息
> ocon1
RODBC Connection 4
Details:
  case=tolower
  DSN=mysql

# 查看连接信息
> odbcGetInfo(ocon1)
             DBMS_Name               DBMS_Ver        Driver_ODBC_Ver       Data_Source_Name            Driver_Name 
               "MySQL"               "8.0.19"                "03.80"                "mysql"         "myodbc8w.dll" 
            Driver_Ver               ODBC_Ver            Server_Name 
          "08.00.0019"           "03.80.0000" "localhost via TCP/IP"

2.5 查看数据库基本信息

查看数据库中所有表


# 使用SQL，查看数据库中所有的表
> sqlQuery(ocon1, "SHOW TABLES")
  Tables_in_world
1            city
2           city2
3         country
4 countrylanguage

# 通过函数，查看数据库有所有的表
> sqlTables(ocon1)
  TABLE_CAT TABLE_SCHEM      TABLE_NAME TABLE_TYPE REMARKS
1     world                        city      TABLE        
2     world                       city2      TABLE        
3     world                     country      TABLE        
4     world             countrylanguage      TABLE

2.5 查看数据表的结构基本信息

查看数据表的结构


# 用SQL查看city表数据结构
> sqlQuery(ocon1, "DESCRIBE city")
        Field     Type Null Key Default          Extra
1          ID      int   NO PRI      NA auto_increment
2        Name char(35)   NO          NA               
3 CountryCode  char(3)   NO MUL      NA               
4    District char(20)   NO          NA               
5  Population      int   NO           0    

# 用函数，查看city表数据结构
> sqlColumns(ocon1, "city")
  TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE
1                  <na>       city          ID         4   integer          10             4              0             10        1
2                  <na>       city        Name         1      char          35            35             NA             NA        0
3                  <na>       city CountryCode         1      char           3             3             NA             NA        0
4                  <na>       city    District         1      char          20            20             NA             NA        0
5                  <na>       city  Population         4   integer          10             4              0             10        0
  REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
1                  0             4               NA                NA                1         YES
2                 ''             1               NA                35                2          NO
3                 ''             1               NA                 3                3          NO
4                 ''             1               NA                20                4          NO
5                  0             4               NA                NA                5          NO

2.6 用SQL执行查询

通常的数据库操作，我们都是基于SQL语句的，可以使用sqlQuery操作进行查询。


> sqlQuery(ocon1, paste("SELECT Code,Name,Continent,Region,GNP FROM country WHERE GNP > 500000 ORDER BY GNP DESC"))
   Code           Name     Continent          Region     GNP
1   USA  United States North America   North America 8510700
2   JPN          Japan          Asia    Eastern Asia 3787042
3   DEU        Germany        Europe  Western Europe 2133367
4   FRA         France        Europe  Western Europe 1424285
5   GBR United Kingdom        Europe British Islands 1378330
6   ITA          Italy        Europe Southern Europe 1161755
7   CHN          China          Asia    Eastern Asia  982268
8   BRA         Brazil South America   South America  776739
9   CAN         Canada North America   North America  598862
10  ESP          Spain        Europe Southern Europe  553233

2.7 批量数据操作

R语言中，经常会有对数据批量处理，ODBC的API也提供了，批量进行增、删、改、查操作的API函数。


# 增加，将一个数据框写入或更新
> sqlSave(ocon1, iris[1:5,] ,"iris")

# 查询
> sqlFetch(ocon1, "iris")
  sepallength sepalwidth petallength petalwidth species
1         5.1        3.5         1.4        0.2  setosa
2         4.9        3.0         1.4        0.2  setosa
3         4.7        3.2         1.3        0.2  setosa
4         4.6        3.1         1.5        0.2  setosa
5         5.0        3.6         1.4        0.2  setosa

# 增量写入
> sqlSave(ocon1, iris[6:10,] ,"iris", append=TRUE)

# 查询
> df1<-sqlFetch(ocon1, "iris");df1
   sepallength sepalwidth petallength petalwidth species
1          5.1        3.5         1.4        0.2  setosa
2          4.9        3.0         1.4        0.2  setosa
3          4.7        3.2         1.3        0.2  setosa
4          4.6        3.1         1.5        0.2  setosa
5          5.0        3.6         1.4        0.2  setosa
6          5.4        3.9         1.7        0.4  setosa
7          4.6        3.4         1.4        0.3  setosa
8          5.0        3.4         1.5        0.2  setosa
9          4.4        2.9         1.4        0.2  setosa
10         4.9        3.1         1.5        0.1  setosa

# 修改
> df1[,1]<-rnorm(1:10)
> df2<-df1[2:4,];df2
  sepallength sepalwidth petallength petalwidth species
2  -0.1721062        3.0         1.4        0.2  setosa
3   1.2726538        3.2         1.3        0.2  setosa
4   0.4437929        3.1         1.5        0.2  setosa
> sqlUpdate(ocon1, df2, "iris")

# 查看2-4行的修改结果
> sqlFetch(ocon1, "iris")
   sepallength sepalwidth petallength petalwidth species
1    5.1000000        3.5         1.4        0.2  setosa
2   -0.1721062        3.0         1.4        0.2  setosa
3    1.2726538        3.2         1.3        0.2  setosa
4    0.4437929        3.1         1.5        0.2  setosa
5    5.0000000        3.6         1.4        0.2  setosa
6    5.4000000        3.9         1.7        0.4  setosa
7    4.6000000        3.4         1.4        0.3  setosa
8    5.0000000        3.4         1.5        0.2  setosa
9    4.4000000        2.9         1.4        0.2  setosa
10   4.9000000        3.1         1.5        0.1  setosa

# 清空内数据
> sqlClear(ocon1, "iris")
> sqlFetch(ocon1, "iris")
[1] sepallength sepalwidth  petallength petalwidth  species    
<0 行> (或0-长度的row.names)

# 删除表
> sqlDrop(ocon1, "iris", errors = FALSE)
> sqlFetch(ocon1, "iris")
Error in odbcTableExists(channel, sqtable) : 
  ‘iris’: table not found on channel

2.8 事务处理

事务操作，默认的ODBC数据库连接的自动提交模式，必须手动设置了autoCommit = FALSE 后在odbcSetAutoCommit()函数中，才会进行手动管理。一个事务完成后，还需要再次调用odbcSetAutoCommit()函数。进行下一条语句的事务管理。


# 设置
> odbcSetAutoCommit(ocon1,autoCommit = FALSE)
[1] 0

# 创建表
> sqlSave(ocon1, iris[1:5,] ,"iris")

# 执行查询，表示已经成功
> sqlFetch(ocon1, "iris")
  sepallength sepalwidth petallength petalwidth species
1         5.1        3.5         1.4        0.2  setosa
2         4.9        3.0         1.4        0.2  setosa
3         4.7        3.2         1.3        0.2  setosa
4         4.6        3.1         1.5        0.2  setosa
5         5.0        3.6         1.4        0.2  setosa

# 事务不提交，则进行回滚
> odbcEndTran(ocon1,commit = FALSE)
[1] 0

# 再次查询时，表中没有数据。
> sqlFetch(ocon1, "iris")
[1] sepallength sepalwidth  petallength petalwidth  species    
<0 行> (或0-长度的row.names)

从上面的例子中，我们可以看到事务可以控制数据的插入(DML)操作，但不能控制表的创建(DDL)。

最后，关闭数据库连接。


# 关闭单个数据库连接
> odbcClose(ocon1)

# 关闭所有数据库连接
> odbcCloseAll()

# 查看连接信息
> odbcGetInfo(ocon1)
Error in odbcGetInfo(ocon1) : argument is not an open RODBC channel

3. 通过RODBC连接PostgreSQL数据库

整个过程与上文中MySQL的过程类似，简略介绍。首先，安装PostgreSQL数据库，我在远程服务器进行了安装，安装过程请参考文章在Ubuntu上安装PostgreSQL

下载window上的postgreSQL的ODBC数据源驱动psqlODBC ，https://odbc.postgresql.org/

下载后进行驱动程序的安装，然后在ODBC的驱动程序的管理控制台，可以查看刚安装的PostgreSQL ODBC 驱动程序。

为postgreSQL数据库，添加用户DSN 。

PostgreSQL数据库的配置信息。

接下为，用R语言连接配置好的ODBC 的数据源。


# 加载RODBC包
> library(RODBC)

# 查看odbc数据源支持，这时又多了一个postgresql的数据源
> odbcDataSources()
                          mysql                      postgresql 
"MySQL ODBC 8.0 Unicode Driver"       "PostgreSQL Unicode(x64)" 

# 建立数据连接
> ocon2<-odbcConnect("postgresql")

# 查看连接信息
> odbcGetInfo(ocon2)
        DBMS_Name          DBMS_Ver   Driver_ODBC_Ver  Data_Source_Name       Driver_Name        Driver_Ver          ODBC_Ver 
     "PostgreSQL"          "12.0.4"           "03.51"      "postgresql" "PSQLODBC35W.DLL"      "12.02.0000"      "03.80.0000" 
      Server_Name 
    "192.168.1.5" 

# 查看数据库中，所有数所表
> sqlTables(ocon2)
  TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1    testdb      public        acc      TABLE        
2    testdb      public    account      TABLE        
3    testdb      public       iris      TABLE        

# 读取account表的数据
> sqlFetch(ocon2, "account")
  user_id username password
1       3       a3     密码
2       5       fd   mddddd

# 关闭数据连接
> odbcClose(ocon1)

在我们配置好了 ODBC 数据源后，从R的代码的角度，对MySQL和PostgreSQL的数据库操作API都是一致的，都可以基本RODBC包提供的API来完成。ODBC很好的解决了，不同数据库之间数据一致性访问的问题。

转载请注明出处：
http://blog.fens.me/r-rodbc/

打赏作者

排行榜

Blog Archives

Posted:

Tags:

Comments:

用RODBC连接数据库

1. ODBC和RODBC包介绍

2. 通过RODBC连接MySQL数据库

3. 通过RODBC连接PostgreSQL数据库

站内导航

最新文章

最新评论

最热文章