R语言链接数据库RODBC的使用

biostar

R语言链接数据库：
包 RODBC
CRAN 里面的包 RODBC 提供了支持 ODBC 规范的访问数据源的接口。它有广泛的需求，可以使一样的 R 代码访问不同的数据库系统。在Unix/Linux以及Windows系统都能运行包 RODBC，而且几乎所有的数据库系统都支持ODBC。我们已经在 Windows 平台上的Microsoft SQL Server，Access，MySQL 和 PostgreSQL 以及Linux平台上的MySQL，Oracle，PostgreSQL和SQLite都测试过了。

ODBC是一种客户端-服务器系统。我们可以从Windows客户端连接Unix服务器上运行的DBMS，反之也然。

Windows 通常都会支持ODBC，最新版本作为MDAC的一部分可以从 http://www.microsoft.com/data/odbc/ 下载。在Unix/Linux系统，你需要一个 ODBC 驱动管理，比如 unixODBC (http://www.unixODBC.org) 或 iOBDC (http://www.iODBC.org)，还需要为你的数据库系统安装一个驱动。FreeODBC 项目（http://www.jepstone.net/FreeODBC/）是和 ODBC 信息相关的一个知识库。

Windows不仅为DBMSs提供驱动，还为Excel电子表格（.xls），DBase（.dbf）文件甚至文本文件提供驱动。（不需要安装命名过的软件。）现在已经有Excel 2007和Access 2007版本的驱动了（去http://download.microsoft.com，选择`Office'并且找到`ODBC'，然后下载AccessDatabaseEngine.exe）。

大量同时访问是可能的。调用 odbcConnect 或 odbcDriverConnect （在Windows图形化界面下，可以通过对话框选择数据库）可以打开一个连接，返回一个用于随后数据库访问的控制（handle）。打印一个连接会给出ODBC连接的一些细节，而调用 odbcGetInfo 会给出客户端和服务器的一些细节信息。

连接可以通过调用函数 close 或 odbcClose 来关闭。没有 R 对象对应或不在 R 会话后面的连接也可以调用这两个函数来关闭，但会有警告信息。

在一个连接中的表的细节信息可以通过函数 sqlTables 获得。

函数 sqlSave 会把 R 数据框复制到一个数据库的表中，而函数 sqlFetch 会把一个数据库中的表拷贝到一个 R 的数据框中。

一个 SQL 查询可以通过调用sqlQuery 传给数据库。返回的结果是 R 的数据框。（sqlCopy把一个查询传给数据库，返回结果在数据库中以表的方式保存。）一种比较好的控制方式是首先调用 odbcQuery， 然后用 sqlGetResults 取得结果。后者可用于一个循环中每次获得有限行，就如函数 sqlFetchMore 的功能。

这里是用PostgreSQL的一个例子，其中ODBC 驱动把列和数据框的名字映射成小写。我们用一个事先创建的数据库 testdb，还有一个个设置在unixODBC下文件 ~/.odbc.ini 的数据源名字（Data Source Name，DSN）。同样的代码在MyODBC访问Linux 或Windows上的MySQL数据库时一样有效（其中，MySQL依然会把名字映射成小写）。在 Windows，DSN在控制面板的 ODBC 工具里面设置（在Windows 2000/XP，设置`管理工具'部分的`数据源（ODBC）'）。


     > library(RODBC)
     ## 让函数把名字映射成小写
     > channel <- odbcConnect("testdb", uid="ripley", case="tolower")
     ## 把一个数据框导入数据库
     > data(USArrests)
     > sqlSave(channel, USArrests, rownames = "state", addPK = TRUE)
     > rm(USArrests)
     ## 列出数据库的表
     > sqlTables(channel)
       TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS
     1                              usarrests      TABLE
     ## 列出表格
     > sqlFetch(channel, "USArrests", rownames = "state")
                    murder assault urbanpop rape
     Alabama          13.2     236       58 21.2
     Alaska           10.0     263       48 44.5
         ...
     ## SQL查询，原先是在一行的
     > sqlQuery(channel, "select state, murder from USArrests
                where rape > 30 order by murder")
            state murder
     1 Colorado      7.9
     2 Arizona       8.1
     3 California    9.0
     4 Alaska       10.0
     5 New Mexico   11.4
     6 Michigan     12.1
     7 Nevada       12.2
     8 Florida      15.4
     ## 删除表
     > sqlDrop(channel, "USArrests")
     ## 关闭连接
     > odbcClose(channel)

作为 Windows下面用 ODBC 连接 Excel电子表格的一个简单例子，我们可以如下读取电子表格

     > library(RODBC)
     > channel <- odbcConnectExcel("bdr.xls")
     ## 列出电子表格
     > sqlTables(channel)
       TABLE_CAT TABLE_SCHEM        TABLE_NAME   TABLE_TYPE REMARKS
     1 C:\\bdr            NA           Sheet1$ SYSTEM TABLE      NA
     2 C:\\bdr            NA           Sheet2$ SYSTEM TABLE      NA
     3 C:\\bdr            NA           Sheet3$ SYSTEM TABLE      NA
     4 C:\\bdr            NA Sheet1$Print_Area        TABLE      NA
     ## 获得表单1的内容，可以用下面任何一种方式
     > sh1 <- sqlFetch(channel, "Sheet1")
     > sh1 <- sqlQuery(channel, "select * from [Sheet1$]")

注意，数据库表的规范和 sqlTables 返回的名字是不一样的： sqlFetch 可以映射这种差异。