GBase 8s数据库连接 – Python

Linux下通过PYODBC连接到GBase 8s数据库
操作系统:RHEL 6 64bit
软件需求:
GBase 8s CSDK
Python 2.6 及Python-devel 2.6
unixODBC
pyodbc-4.0.25

1,系统环境确认
1.1 python、python-devel及unixODBC版本
Linux下root用户通过rpm确认python、python-devel,unixODBC、unixODBC-devel均已经安装,如下:

[root@localhost ~]#  rpm -qa python python-devel unixODBC unixODBC-devel
unixODBC-devel-2.2.14-14.el6.x86_64
python-2.6.6-66.el6_8.x86_64
unixODBC-2.2.14-14.el6.x86_64
python-devel-2.6.6-66.el6_8.x86_64 

1.2 GBase 8s CSDK已经安装
Linux下gbasedbt用户通过esql -V命令确认GBase 8s CSDK已经安装,如下:

[gbasedbt@localhost ~]$ esql -V
GBase CSDK Version 4.10, GBASE-ESQL Version 4.10.FC4G1_2.0.1A2_2
Software Serial Number AAA#B000000

2,ODBC配置
2.1 ODBC配置文件
修改/etc/odbc.ini配置文件,增加GBase 8s数据库连接信息

;---------------------------------------------------------------------------
; GBase ODBC Sample File
;
; File:         odbc.ini
;
;---------------------------------------------------------------------------
[ODBC Data Sources]
utf8=GBase ODBC DRIVER
;
; Define ODBC Database Driver's Below - Driver Configuration Section
;
[utf8]
Driver=/opt/gbase8s/lib/cli/iclis09b.so
Description=GBase ODBC DRIVER
Database=utf8
LogonID=gbasedbt
pwd=GBase123
Servername=gbasedbt
CursorBehavior=0
CLIENT_LOCALE=zh_CN.utf8
DB_LOCALE=zh_CN.utf8
TRANSLATIONDLL=/opt/gbase8s/lib/esql/igo4a304.so
;
; UNICODE connection Section
;
[ODBC]
;uncomment the below line for UNICODE connection
;UNICODE=UCS-4
;
; Trace file Section
;
Trace=0
TraceFile=/tmp/odbctrace.out
InstallDir=/opt/gbase8s
TRACEDLL=idmrs09a.so

2.2 验证ODBC配置
加载GBase 8s CSDK环境变量,包含连接到GBase 8s数据库所需的GBASEDBTDIR、LD_LIBRARY_PATH,以及选用的字符集DB_LOCALE、CLIENT_LOCALE、LANG等。加载ODBCINI环境变量。环境变量内容可保存为配置文件,如:gbase8s_odbc_env,以供调用。

export GBASEDBTDIR=/opt/gbase8s                    # GBase 8s CSDK安装目录
# 数据库使用的字符集
export DB_LOCALE=zh_CN.utf8
export CLIENT_LOCALE=zh_CN.utf8

export LD_LIBRARY_PATH=$GBASEDBTDIR/lib:$GBASEDBTDIR/lib/esql:$LD_LIBRARY_PATH
# ODBCINI
export ODBCINI=/etc/odbc.ini

通过isql命令检查ODBC配置

[root@localhost ~]#  . gbase8s_odbc_env
[root@localhost ~]#  isql -v utf8
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select dbservername::varchar(40) from systables where tabid = 1;
+-----------------------------------------+
|                                         |
+-----------------------------------------+
| gbasedbt                                |
+-----------------------------------------+
SQLRowCount returns -1
1 rows fetched

3,编译安装pyodbc
将pyodbc-4.0.25.tar.gz上传至GBase 8s CSDK所在的服务器上,使用root用户解压。

[root@localhost pyodbc]#  tar -zxvf pyodbc-4.0.25.tar.gz

在生成的pyodbc-4.0.25目录里执行python setup.py install

[root@localhost pyodbc-4.0.25]#  python setup.py install

内容如下:

running install
running bdist_egg
running egg_info
writing pyodbc.egg-info/PKG-INFO
writing top-level names to pyodbc.egg-info/top_level.txt
writing dependency_links to pyodbc.egg-info/dependency_links.txt
reading manifest file 'pyodbc.egg-info/SOURCES.txt'
reading manifest template 'MANIFEST.in'
writing manifest file 'pyodbc.egg-info/SOURCES.txt'
installing library code to build/bdist.linux-x86_64/egg
running install_lib
running build_ext
creating build/bdist.linux-x86_64/egg
copying build/lib.linux-x86_64-2.6/pyodbc.so -> build/bdist.linux-x86_64/egg
creating stub loader for pyodbc.so
byte-compiling build/bdist.linux-x86_64/egg/pyodbc.py to pyodbc.pyc
creating build/bdist.linux-x86_64/egg/EGG-INFO
copying pyodbc.egg-info/PKG-INFO -> build/bdist.linux-x86_64/egg/EGG-INFO
copying pyodbc.egg-info/SOURCES.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
copying pyodbc.egg-info/dependency_links.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
copying pyodbc.egg-info/top_level.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
writing build/bdist.linux-x86_64/egg/EGG-INFO/native_libs.txt
zip_safe flag not set; analyzing archive contents...
creating 'dist/pyodbc-4.0.25-py2.6-linux-x86_64.egg' and adding 'build/bdist.linux-x86_64/egg' to it
removing 'build/bdist.linux-x86_64/egg' (and everything under it)
Processing pyodbc-4.0.25-py2.6-linux-x86_64.egg
creating /usr/lib64/python2.6/site-packages/pyodbc-4.0.25-py2.6-linux-x86_64.egg
Extracting pyodbc-4.0.25-py2.6-linux-x86_64.egg to /usr/lib64/python2.6/site-packages
Adding pyodbc 4.0.25 to easy-install.pth file

Installed /usr/lib64/python2.6/site-packages/pyodbc-4.0.25-py2.6-linux-x86_64.egg
Processing dependencies for pyodbc==4.0.25
Finished processing dependencies for pyodbc==4.0.25

完成后在/usr/lib64/python2.6/site-packages/目录下,将生成以下4个文件:

[root@localhost site-packages]# ls *odbc*
EGG-INFO  pyodbc.py  pyodbc.pyc  pyodbc.so

4,Python通过ODBC连接GBase 8s数据库测试
编写测试脚本程序test_odbc.py,内容如下:

#!/usr/bin/python
# coding:utf-8

import sys
import pyodbc

reload(sys)
sys.setdefaultencoding('utf-8')
conn = pyodbc.connect("DSN=utf8")

mycursor = conn.cursor()
mycursor.execute('drop table if exists tpyodbc;')

mycursor.execute('create table tpyodbc(id serial, name varchar(60), sex char(3));')


mycursor.execute("insert into tpyodbc(name,sex) values('张三','男')")
mycursor.execute('insert into tpyodbc(name,sex) values("韩美美","女")')
conn.commit()

cursor1 = conn.cursor()
cursor1.execute('select id,name,sex from tpyodbc')
rows = cursor1.fetchall();

for i,(id,name,sex) in enumerate(rows):
  print "ID: %d \t姓名:%s\t性别: %s" % (id, str(name), str(sex))

conn.close()
sys.exit(0);

python test_odbc.py执行并输出结果:

[root@localhost pyodbc-4.0.25]#  python test_odbc.py
ID: 1   姓名: 张三      性别: 男
ID: 2   姓名: 韩美美    性别: 女

Python通过pyodbc连接到GBase 8s数据库,测试成功。

标签: pyodbc, gbase 8s, odbc

评论已关闭