打通Linux上的ODBC
source link: http://blog.fens.me/r-rodbc-linux/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
R的极客理想系列文章，涵盖了R的思想，使用，工具，创新等的一系列要点，以我个人的学习和体验去诠释R的强大。
R语言作为统计学一门语言，一直在小众领域闪耀着光芒。直到大数据的爆发，R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入，R语言的社区在迅速扩大成长。现在已不仅仅是统 计领域，教育，银行，电商，互联网….都在使用R语言。
要成为有理想的极客，我们不能停留在语法上，要掌握牢固的数学，概率，统计知识，同时还要有创新精神，把R语言发挥到各个领域。让我们一起动起来吧，开始R的极客理想。
关于作者：
- 张丹，分析师/程序员/Quant: R,Java,Nodejs
- blog:http://fens.me
- email: [email protected]
转载请注明出处：
http://blog.fens.me/r-rodbc-linux/ 
前言
ODBC是由微软公司开发的，用于数据库连接的统一连接方案，在基于Windows的操作系统上被广泛地使用。在Linux上，其实也有ODBC的解决方案。今天我就来试试，在Linux上配置ODBC进行数据库的连接和交互。关于Window的ODBC解决方案，请参考文章用RODBC连接数据库
目录
- 在Linux在安装ODBC
- 通过RODBC连接PostgreSQL数据库
- 通过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 * * [email protected] * * [email protected] * **********************************************
装完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上的数据连接操作，这样就可以方便地解决数据库多样性的问题，不仅能让代码保持一致的风格，而且对于复杂的配置过程也可以一次性的解决，是一个不错的方案。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK