打通Linux上的ODBC

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/

打赏作者

This entry was posted in R语言实践