用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的数据访问接口程序。

目录

  1. ODBC和RODBC包介绍
  2. 通过RODBC连接MySQL数据库
  3. 通过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/

打赏作者

This entry was posted in R语言实践

0 0 votes
Article Rating
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x