Centos7下Python3连接IBM i Access(DB2 for i)
环境版本
Centos版本：7.6
Python版本：3.6.5
IBM i Access版本：DB2/400 SQL 7.3.0015
简介
IBM i Access是IBM AS400小型机上的DB2数据库，使用Python3连接该数据库，需要使用unixODBC、pyodbc模块和IBM i access for Linux 客户端
unixODBC项目地址：
pyodbc项目地址：
https://github.com/mkleehammer/pyodbc
IBM i access for Linux 客户端地址（下载需要IBM账户）：
https://www.ibm.com/support/pages/ibm-i-access-client-solutions
为了方便安装和使用，我们使用YUM安装
unixODBC安装
[root@4804876a42b7 ~]# yum install -y unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel ##安装完成后会在etc下生成两个文件 [root@4804876a42b7 ~]# ls /etc/odbc* /etc/odbc.ini /etc/odbcinst.ini
IBM i access for Linux 客户端
[root@4804876a42b7 ~]# rpm -ivh ibm-iaccess-1.1.0.12-1.0.x86_64.rpm Preparing... ################################# [100%] Updating / installing... 1:ibm-iaccess-1.1.0.12-1.0 ################################# [100%] ##安装完成后，自动添加配置文件到odbcinst.ini [root@4804876a42b7 ~]# cat /etc/odbcinst.ini ##[]为名称，ODBC调用驱动时使用 [IBM i Access ODBC Driver] Description=IBM i Access for Linux ODBC Driver ##32位驱动，驱动文件不存在 Driver=/opt/ibm/iaccess/lib/libcwbodbc.so Setup=/opt/ibm/iaccess/lib/libcwbodbcs.so ##64位驱动，rpm包安装的驱动 Driver64=/opt/ibm/iaccess/lib64/libcwbodbc.so Setup64=/opt/ibm/iaccess/lib64/libcwbodbcs.so Threading=0 DontDLClose=1 UsageCount=1 [IBM i Access ODBC Driver 64-bit] Description=IBM i Access for Linux 64-bit ODBC Driver Driver=/opt/ibm/iaccess/lib64/libcwbodbc.so Setup=/opt/ibm/iaccess/lib64/libcwbodbcs.so Threading=0 DontDLClose=1 UsageCount=1
pyodbc安装
[root@4804876a42b7 ~]# pip3 install pyodbc
Python3连接测试脚本
# -*- coding: UTF-8 -*-
import pyodbc
class Db2iEngine():
def __init__(self,server,database,port,username,password):
self.conn = None
self.server = server
self.database = database
self.port = port
self.username = username
self.password = password
def get_connection(self, db_name=None):
"""数据库连接"""
con_str='DRIVER={IBM i Access ODBC Driver 64-bit};SYSTEM='+self.server+\
';DATABASE='+self.database+';PORT='+self.port+\
';UID='+self.username+';PWD='+self.password+';CommitMode=0'
if self.conn:
return self.conn
self.conn = pyodbc.connect(con_str)
return self.conn
@property
def name(self):
"""获取数据库名称"""
conn = self.get_connection()
#pyodbc中定义SQL_DBMS_NAME=17
dbname = conn.getinfo(17)
return tuple([dbname])
@property
def server_version(self):
"""获取数据库版本"""
conn = self.get_connection()
#pyodbc中定义SQL_DBMS_VER=18
version = conn.getinfo(18)
return tuple([n for n in version.split('.')[:3]])
def get_all_databases(self):
"""获取数据库"""
sql = '''SELECT SCHEMA_NAME FROM SYSIBM.SCHEMATA'''
result_rows = self.query(sql=sql)
result = [rows[0] for rows in result_rows]
return result
def get_all_tables(self, db_name):
"""获取数据下的表"""
sql = f'''SELECT TABLE_NAME FROM SYSIBM.TABLES WHERE TABLE_SCHEMA='{db_name.upper()}' '''
result_rows = self.query(sql=sql)
result = [rows[0] for rows in result_rows]
return result
def get_all_columns_by_tb(self, db_name, tb_name):
"""获取表的所有字段"""
sql = f"""SELECT COLUMN_NAME,DATA_TYPE,COLUMN_DEFAULT,IS_NULLABLE FROM SYSIBM.COLUMNS
WHERE TABLE_SCHEMA='{db_name.upper()}'
AND TABLE_NAME='{tb_name.upper()}'"""
result = self.query(sql=sql)
return result
def query(self, sql='', close_conn=True):
"""执行SQL语句"""
try:
conn = self.get_connection()
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
print(e)
finally:
if close_conn:
self.close()
return rows
def close(self):
"""关闭实例连接"""
if self.conn:
self.conn.close()
self.conn = None
if __name__ == '__main__':
conn = Db2iEngine(server='10.10.1.1',database='TEST',port='8471',username='TEST',password='TEST')
tables = conn.get_all_columns_by_tb('SYSIBM','columns')
print(tables)
执行后输出表信息
