环境版本

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项目地址:

http://www.unixodbc.org/

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)

执行后输出表信息

发表评论

电子邮件地址不会被公开。 必填项已用*标注