Blog Archives

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-linux/

前言

ODBC是由微软公司开发的，用于数据库连接的统一连接方案，在基于Windows的操作系统上被广泛地使用。在Linux上，其实也有ODBC的解决方案。今天我就来试试，在Linux上配置ODBC进行数据库的连接和交互。关于Window的ODBC解决方案，请参考文章用RODBC连接数据库

目录

1. 在Linux在安装ODBC
2. 通过RODBC连接PostgreSQL数据库
3. 通过RODBC连接MySQL数据库

1. 在Linux在安装ODBC

我们先来整理一下ODBC在 Linux 环境中系统架构，我们需要找到Linux的ODBC软件程序 unixodbc ，unixodbc 就是解决方案核心的连接器。通过 unixodbc 一端绑定数据库，另一端绑定编程语言的API。

我在测试的过程中，ODBC只能是绑定本地的数据库，不能连接远程数据库，所以需要在本地计算机提前装好数据库。MySQL的数据库安装请参考文章在Ubuntu上安装MySQL，PostgreSQL的数据库安装请参考文章在Ubuntu上安装PostgreSQL。

我们具体的操作步骤：

• 第一步，在Linux中安装unixodbc驱动程序
• 第二步，绑定MySQL/PostgreSQL数据库到unixodbc。
• 第三步，用RDOBC与unixodbc建立连接

我使用Linux Ubuntu 操作系统，可以通过apt 来直接安装 unixodbc 软件包。

我的操作系统环境：

• Linux Ubuntu 20.04.1 LTS 64bit
• R version 3.6.3 64bit

在Linux中，安装unixodbc 软件，就是一条命令。

~ sudo apt install unixodbc

在odbc软件安装好后，在 /etc/目录下面，就可以看到2个文件，odbc.ini和odbcinst.ini文件。

• odbc.ini，文件中主要是配置数据库连接，要手动配置
• odbcinst.ini，配置数据库驱动文件，各数据库驱动安装时，自动配置。

通过odbcinst 的工具，来查看一下odbc程序的基本情况。

~ odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/conan/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

另外，可以通过isql的命令行工具，来测试一下odbc的数据库连接，后面配置好数据源后就可以进行测试。

打开odbc客户端 isql 。

~ isql
**********************************************
* unixODBC - isql                            *
**********************************************
* Syntax                                     *
*                                            *
*      isql DSN [UID [PWD]] [options]        *
*                                            *
* Options                                    *
*                                            *
* -b         batch.(no prompting etc)        *
* -dx        delimit columns with x          *
* -x0xXX     delimit columns with XX, where  *
*            x is in hex, ie 0x09 is tab     *
* -w         wrap results in an HTML table   *
* -c         column names on first row.      *
*            (only used when -d)             *
* -mn        limit column display width to n *
* -v         verbose.                        *
* -lx        set locale to x                 *
* -q         wrap char fields in dquotes     *
* -3         Use ODBC 3 calls                *
* -n         Use new line processing         *
* -e         Use SQLExecDirect not Prepare   *
* -k         Use SQLDriverConnect            *
* -L         Length of col display (def:300) *
* --version  version                         *
*                                            *
* Commands                                   *
*                                            *
* help - list tables                         *
* help table - list columns in table         *
* help help - list all help options          *
*                                            *
* Examples                                   *
*                                            *
*      isql WebDB MyID MyPWD -w < My.sql     *
*                                            *
*      Each line in My.sql must contain      *
*      exactly 1 SQL command except for the  *
*      last line which must be blank (unless *
*      -n option specified).                 *
*                                            *
* Please visit;                              *
*                                            *
*      http://www.unixodbc.org               *
*      nick@lurcher.org                      *
*      pharvey@codebydesign.com              *
**********************************************

装完ODBC后，我们就可以尝试把不同的数据库和ODBC进行绑定，再通过程序来连接ODBC，从而实现对数据库的访问。

2. 通过RODBC连接PostgreSQL数据库

我们开始配置PostgreSQL数据库，进行ODBC数据源的绑定。

2.1 配置PostgreSQL的ODBC数据源

安装PostgresSQL在Ubuntu上的odbc驱动

~ sudo apt install odbc-postgresql

安装后驱动程序后，我们会发现在 /etc/odbcinst.ini 文件中，已经配置了好PostgreSQL 驱动程序信息。

查看文件 /etc/odbcinst.ini

~ cat /etc/odbcinst.ini
[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

之后，我们需要修改 /etc/odbc.ini，把PostgreSQL数据库绑定到ODBC数据源中，就可以使用ODBC了。

编辑文件 /etc/odbc.ini，

~ vi /etc/odbc.ini

[postgres01]
Description = post01
Driver      = PostgreSQL Unicode
Database = testdb
Servername = 127.0.0.1
UserName = test
Password = test
Port = 5432
ReadOnly = 0
ConnSettings = set client_encoding to UTF8

参数解释

• [postgres01]：为用户DNS，这个名称是用于配置到编程语言程序中的。
• Driver: PostgreSQL Unicode，驱动名词，这个名词为 /etc/odbcinst.ini 所对应的驱动名词。
• Database ：testdb，PostgreSQL的数据库名称
• Servername ： 127.0.0.1，PostgreSQL的数据库服务器IP
• UserName： test，PostgreSQL的数据库用户名
• Password： test，PostgreSQL的数据库用户密码
• Port： 5432，PostgreSQL的数据库端口
• ReadOnly： 0，是否是只读，0不是，1是
• ConnSettings ：客户端的连接字符编码

用命令检测数据源是否配置成功

~ isql -v postgres01
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

2.2 用RODBC进行数据库连接

RODBC的安装，比较简单，在R语言环境中用install.packages(“RODBC”)就能成功安装了。也可以参考Window的ODBC解决方案，请参考文章用RODBC连接数据库

下面我们在Ubuntu的R语言环境中调用ODBC。

# 安装RODBC
> install.packages("RODBC")
> library(RODBC)

建立ODBC的数据库连接。

# 建立连接
> o1<-odbcConnect("postgres01")

# 查看连接对象
> o1
RODBC Connection 6
Details:
  case=tolower
  DSN=postgres01
  DATABASE=testdb
  SERVER=127.0.0.1
  PORT=5432
  UID=test
  PWD=******
  SSLmode=disable
  ReadOnly=0
  Protocol=7.4
  FakeOidIndex=0
  ShowOidColumn=0
  RowVersioning=0
  ShowSystemTables=0
  Fetch=100
  UnknownSizes=0
  MaxVarcharSize=255
  MaxLongVarcharSize=8190
  Debug=0
  CommLog=0
  UseDeclareFetch=0
  TextAsLongVarchar=1
  UnknownsAsLongVarchar=0
  BoolsAsChar=1
  Parse=0
  ExtraSysTablePrefixes=
  LFConversion=0
  UpdatableCursors=1
  TrueIsMinus1=0
  BI=0
  ByteaAsLongVarBinary=1
  UseServerSidePrepare=1
  LowerCaseIdentifier=0
  D6=-101

# 查看连接信息
> odbcGetInfo(o1)
       DBMS_Name         DBMS_Ver  Driver_ODBC_Ver Data_Source_Name
    "PostgreSQL"         "12.0.4"          "03.51"     "postgres01"
     Driver_Name       Driver_Ver         ODBC_Ver      Server_Name
  "psqlodbcw.so"     "12.01.0000"          "03.52"      "127.0.0.1"

接下来，做一下数据库的操作。

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

# 读取iris表
> idat<-sqlFetch(o1, "iris");idat
  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

# 增加插入数据
> iris[6:7,]
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
6          5.4         3.9          1.7         0.4  setosa
7          4.6         3.4          1.4         0.3  setosa

# 执行插入命令
> sqlSave(o1, iris[6:7,] ,"iris",append=TRUE)
> sqlFetch(o1, "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
6         5.4        3.9         1.7        0.4  setosa
7         4.6        3.4         1.4        0.3  setosa

2.3 错误排查

用RODBC进行连接时，可能会报这个错误，主要是 /etc/odbc.ini 文件中的Driver没有写对造成的，详细核对一下配置就行了。

> o1<-odbcConnect("postgres01")
Warning messages:
1: In RODBC::odbcDriverConnect("DSN=postgres01") :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In RODBC::odbcDriverConnect("DSN=postgres01") : ODBC connection failed

3. 通过RODBC连接MySQL数据库

同样的步骤，我们先安装Linux ubuntu中MySQL的ODBC驱动程序，

3.1 配置MySQL的ODBC数据源

首先，安装MySQL的ODBC恭驱动。在apt源中，找到mysql-connector-odbc的包尝试安装，但会出现错误。

# 从apt源中安装mysql odbc
~ sudo apt install mysql-connector-odbc
Reading package lists... Done
Building dependency tree
Reading state information... Done
Package mysql-connector-odbc is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source

E: Package 'mysql-connector-odbc' has no installation candidate

安装失败，我们就需要换一种方式进行安装。从MySQL官网手动下载MySQL驱动 https://dev.mysql.com/downloads/connector/odbc/ 上传到服务器/home/conan/soft 目录，我下载的包为 mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit.tar.gz

上传到/home/conan/soft 目录，进行解压。

~ cd /home/conan/soft
~ tar xvf mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit.tar.gz
~ cd  mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit

在 /usr/lib 下面新建mysql-odbc目录，把mysql-odbc类库复制过来。

~ sudo mkdir /usr/lib/mysql-odbc
~ sudo cp -r /home/conan/soft/mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit/lib/* /usr/lib/mysql-odbc

通过myodbc-installer命令，手动安装mysql-odbc的驱动，指定libmyodbc8w.so库对应的位置。

 ~ sudo bin/myodbc-installer -d -a -n "MySQL ODBC 8.0 Unicode Driver" -t "DRIVER=/usr/lib/mysql-odbc/libmyodbc8w.so"
Success: Usage count is 1

安装完成后，在 odbcinst.ini 文件中，会自动创建MySQL的驱动，建立和ODBC的连接。

查看odbcinst.ini文件，增加了 [MySQL ODBC 8.0 Unicode Driver] 的驱动声明。

~ cat /etc/odbcinst.ini

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib/mysql-odbc/libmyodbc8w.so
UsageCount=1

接下来，我们配置MySQL数据库，与ODBC进行连接。编辑odbc.ini文件，增加[mysql01]的DNS命名。

[mysql01]
Description = post01
Driver      = MySQL ODBC 8.0 Unicode Driver
Database = world
Servername = 192.168.1.6
UserName = root
Password = bsspirit
Port = 3306
ReadOnly = 0
ConnSettings = set client_encoding to UTF8

用isql检查一下，ODBC是否配置成功。

~ sudo isql -v mysql01
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from aa;
+-----------+
| id        |
+-----------+
| 1         |
| 2         |
+-----------+
SQLRowCount returns 2
2 rows fetched
SQL>

3.2 用RODBC进行数据库连接

# 加载程序包
> library(RODBC)

# 连接ODBC
> o2<-odbcConnect("mysql01")

# 查看连接信息
> o2
RODBC Connection 1
Details:
  case=nochange
  DSN=mysql01
> odbcGetInfo(o2)
                DBMS_Name                  DBMS_Ver           Driver_ODBC_Ver
                  "MySQL" "8.0.22-0ubuntu0.20.04.2"                   "03.80"
         Data_Source_Name               Driver_Name                Driver_Ver
                "mysql01"          "libmyodbc8w.so"              "08.00.0022"
                 ODBC_Ver               Server_Name
                  "03.52"    "localhost via TCP/IP"

最后，我们进行一些数据库操作，来验证一下数据库连接成功。

# 查询数据表
> sqlFetch(o2, "aa")
  id
1  1
2  2

# 查入数据表
> sqlQuery(o2,"insert into aa values(3)")
character(0)

# 查询数据表
> sqlFetch(o2, "aa")
  id
1  1
2  2
3  3

经过测试我们就完成了RODBC在Linux上的数据连接操作，这样就可以方便地解决数据库多样性的问题，不仅能让代码保持一致的风格，而且对于复杂的配置过程也可以一次性的解决，是一个不错的方案。

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