python3连接db2数据库_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项目地址：

IBM i access for Linux 客户端地址(下载需要IBM账户)：

为了方便安装和使用，我们使用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)

执行后输出表信息