python通过odbc连接DM7

一.安装ODBC驱动（本次以2.3.0为例）

1.首先将附件中的包上传到服务器

[root@localhost unixODBC]# cd /root/unixODBC

2.解压tar包

[root@localhost unixODBC]# tar -xvf unixODBC-2.3.0.tar.gz 

3.安装unix odbc

[root@localhost unixODBC]# cd unixODBC-2.3.0/
[root@localhost unixODBC-2.3.0]# ./configure --enable-gui=no
[root@localhost unixODBC-2.3.0]# make&make install
[root@localhost unixODBC-2.3.0]#  odbc_config --version

查看odbc配置文件路径

[root@localhost unixODBC-2.3.0]# odbcinst -j

--编辑对应的配置文件
[root@localhost unixODBC-2.3.0]# vim  /usr/local/etc/odbcinst.ini
[DM7 ODBC DRIVER]
Description = ODBC DRIVER FOR DM7
Driver = /data/dmdbms/dmdbms/bin/libdodbc.so


--编辑对应的配置文件
[root@localhost unixODBC-2.3.0]# vim  /usr/local/etc/odbc.ini
[dm]
description = DM ODBC DSN
Driver = DM7 ODBC DRIVER
SERVER = 192.168.3.100
UID = SYSDBA
PWD = SYSDBA
TCP_PORT = 5236

4.验证isql 是否能连接数据库

[root@localhost ~]# isql -v dm SYSDBA SYSDBA

二.安装python(我安装的是Python-2.7.18版本的)

1.首先将安装包上传到服务器上

[root@localhost ~]# cd /root/python/

2.解压安装包（需要提前安装gcc gcc-c++包）

[root@localhost python]# tar -xvf Python-2.7.18.tgz

3.安装python

[root@localhost ~]# cd /root/python/Python-2.7.18
[root@localhost Python-2.7.18]# ./configure
[root@localhost Python-2.7.18]# make
[root@localhost Python-2.7.18]# make install 
查看python的版本
[root@localhost Python-2.7.18]# python --version
[root@localhost Python-2.7.18]# python setup.py install

三.安装pyodbc(以pyodbc-4.0.30为例）

1.上传pyodbc-4.0.30安装包

[root@localhost ~]# cd /root/python/

2.解压安装包

[root@localhost python]# tar -xvf  pyodbc-4.0.30.tar.gz

3.安装pyodbc

[root@localhost ~]# cd /root/python/ pyodbc-4.0.30
[root@localhost  pyodbc-4.0.30]# ./configure
[root@localhost  pyodbc-4.0.30]# make
[root@localhost  pyodbc-4.0.30]# make install 
[root@localhost pyodbc-4.0.30]# python setup.py install

四.验证python 连接数据库

[root@localhost lib]# cd /root/python/pyodbc-4.0.30/build/lib.linux-x86_64-2.7/
[root@localhost lib.linux-x86_64-2.7]# ls
libodbcinst.so  libodbc.so.1  pyodbc.so  test1.py
[root@localhost lib.linux-x86_64-2.7]# vim test1.py 
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import pyodbc
# 打开数据库连接
conn = pyodbc.connect(dsn='dm')
# 使用cursor()方法获取操作游标 
cur = conn.cursor()
# 如果数据表已经存在使用 execute() 方法删除表。
cur.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建数据表SQL语句
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
cur.execute(sql)
# 关闭数据库连接
conn.close()


[root@localhost lib.linux-x86_64-2.7]# python test1.py
[root@localhost lib.linux-x86_64-2.7]# isql dm SYSDBA SYSDBA
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from EMPLOYEE;
+---------------------+---------------------+------------+----+-------------------------------------------------------+
| FIRST_NAME          | LAST_NAME           | AGE        | SEX| INCOME                                                |
+---------------------+---------------------+------------+----+-------------------------------------------------------+
+---------------------+---------------------+------------+----+-------------------------------------------------------+
SQLRowCount returns 0
SQL> 

验证成功，创建了新的EMPLOYEE表；

备注：找不到库文件报错解决办法：

[root@localhost ~]# find / -name libodbc.so.1
find找到后将这个库文件所在的目录加到 LD_LIBRARY_PATH环境变量里
[root@localhost ~]# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/root/python/pyodbc-4.0.30/build/lib.linux-x86_64-2.7/