【Python】【接口自动化】对数据库的封装

只写了mysql、oracle、db2、sqlServer的
其他sql数据库和nosql数据库，也都可以扩展进来

依照最后面的样例配置数据库连接信息
通过传入数据库名称和环境连接数据库，各类数据库都相同的方法进行调用

db = CommonDB(‘A系统1库’,env=‘st’)
sql = " insert xxx xxx "
db.exec(sql)
sql = “select xxxxxxxx”
data = db.query(sql,resType=‘dict’)
print(data)

• 其他说明
  oracle貌似是没法一次执行多条sql的，可以在oracle的exec方法里处理下sql
  db2没写返回list[tuple]结构数据的处理，可以pandas转DataFrame处理掉key为数值的列，再转list

import cx_Oracle
import pymysql
import ibm_db
import pymssql
import pandas as pd
from .configs import dbInfos

class CommonDB():

    session = None
    lst_resType = None

    def __init__(self,dbNm,env='st'):
    # 获取连接信息
        self.dbNm = dbNm
        self.env = env
        self.dbInfo = dbInfos.get(dbNm).get(env)
        if not self.dbInfo:
            raise Exception('该数据库信息不存在，请更新数据库连接配置信息')
        dbType = dbInfos.get(dbNm).get('dbType')
        if not dbType:
            raise Exception('未配置数据库类型信息')
        self.dbType = dbType.lower()
        if self.dbType == 'ora':
            self.dbType = 'oracle'
        elif self.dbType.replace(' ', '') == 'sqlserver':
            self.dbType = 'mssql'

    def connect(self,resType='dict'):
    	# 根据dbType实例化对应数据库类，并进行连接
        self.session = eval(self.dbType+'(self.dbInfo)')
        self.session.connect(resType)
        print(f'已连接数据库{self.dbNm,self.env}')

    def reconnect(self,resType='dict'):
        self.close()
        self.connect(resType)

    def query(self,sql,resType='dict'):
    	# 如果已连接数据库就不再次连接
        if not self.session:
            self.connect(resType)
        # 这个用不着，正常是都会要dict数据，不会一次要dict一次要tuple
        if self.lst_resType is not None and self.lst_resType != resType:
            self.reconnect(resType)
        self.lst_resType = resType
        return self.session.query(sql)

    def exec(self,sql):
        if not self.session:
            self.connect()
        self.session.exec(sql)

    def close(self):
        self.session.close()
        print(f'已断开数据库连接{self.dbNm, self.env}')

    def __del__(self):
        self.close()



class mysql():
    def __init__(self,dbInfo):
        self.host = dbInfo.get('host')
        self.port = dbInfo.get('port',1521)
        self.db = dbInfo.get('db')
        self.user = dbInfo.get('user')
        self.password = dbInfo.get('password')
        self.charset = dbInfo.get('charset','utf8')

    def connect(self,resType='dict'):
        self.close()
        self.conn = pymysql.connect(host=self.host,
                                    port=self.port,
                                    database=self.db,
                                    user=self.user,
                                    password=self.password,
                                    charset=self.charset,
                                    autocommit=True)
        if resType == 'dict':
            self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        else:
            self.cursor = self.conn.cursor()

    def query(self,sql):
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        return data

    def exec(self,sql):
        self.cursor.execute(sql)

    def close(self):
        try:
            self.cursor.close()
        except:
            pass
        try:
            self.conn.close()
        except:
            pass

    def __del__(self):
        self.close()



class oracle():
    def __init__(self,dbInfo):
        self.host = dbInfo.get('host')
        self.port = dbInfo.get('port',3306)
        self.db = dbInfo.get('db')
        self.user = dbInfo.get('user')
        self.password = dbInfo.get('password')

    def connect(self,resType='dict'):
        conn_str = f'{self.user}/{self.password}@{self.host}:{self.port}/{self.db}'
        self.conn = cx_Oracle.connect(conn_str,autocommit=True)
        self.cursor = self.conn.cursor()
        self.resType = resType

    def query(self, sql):
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        if self.resType == 'dict':
            columns = self.cursor.description
            df = pd.DataFrame(data,columns=columns)
            data = df.to_dict(orient='records')
        return data

    def exec(self, sql):
        self.cursor.execute(sql)
        self.conn.commit()

    def close(self):
        try:
            self.cursor.close()
        except:
            pass
        try:
            self.conn.close()
        except:
            pass

    def __del__(self):
        self.close()



class db2():
    def __int__(self, dbInfo):
        self.host = dbInfo.get('host')
        self.port = dbInfo.get('port')
        self.db = dbInfo.get('db')
        self.user = dbInfo.get('user')
        self.password = dbInfo.get('password')
        self.protocol = dbInfo.get('protocol','TCPIP')

    def connect(self):
        conn_str = f'DATABASE={self.db};HOSTNAME={self.host};PORT={self.port};PROTOCOL={self.protocol};UID={self.user};PWD={self.password};'
        self.conn = ibm_db.connect(conn_str,'','')

    def query(self, sql):
        stmt = ibm_db.exec_immediate(self.conn, sql)
        fetch = ibm_db.fetch_both(stmt)
        data = [fetch]
        while (fetch):
            fetch = ibm_db.fetch_both(stmt)
            data.append(fetch)
        return data

    def exec(self, sql):
        ibm_db.exec_immediate(self.conn, sql)
        ibm_db.commit(self.conn)

    def close(self):
        try:
            self.conn.close()
        except:
            pass

    def __del__(self):
        self.close()

class mssql():
    """sqlServer"""

    def __init__(self, dbInfo):
        self.host = dbInfo.get('host')
        self.port = dbInfo.get('port', 1433)
        self.db = dbInfo.get('db')
        self.user = dbInfo.get('user')
        self.password = dbInfo.get('password')

    def connect(self,resType='dict'):
        self.conn = pymssql.connect(server=self.host,
                                    port=self.port,
                                    database=self.db,
                                    user=self.user,
                                    password=self.password,
                                    autocommit=True
                                    )
        if resType == 'dict':
            self.cursor = self.conn.cursor(as_dict=True)
        else:
            self.cursor = self.conn.cursor()

    def query(self, sql):
        # mssql varchar字段，中文会乱码
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        return data

    def exec(self, sql):
        self.cursor.execute(sql)

    def close(self):
        try:
            self.cursor.close()
        except:
            pass
        try:
            self.conn.close()
        except:
            pass

    def __del__(self):
        self.close()

if __name__ == '__main__':
    dbInfos = {
        'A系统1库': {
            'dbType': 'oracle',
            'st': {
                'host': '192.168.2.102',
                'port': 1521,
                'db': 'orcl',
                'user': 'user',
                'password': 'password'
            },
            'uat': {
                'host': '192.168.2.103',
                'port': 1521,
                'db': 'orcl',
                'user': 'user',
                'password': 'password'
            }
        },
        'A系统2库': {
            'dbType': 'mysql',
            'st': {
                'host': '192.168.2.101',
                'port': 3306,
                'db': 'prac',
                'user': 'user',
                'password': 'password'
            }
        },
        'B系统1库': {
            'dbType': 'mssql',
            'st': {
                'host': '192.168.2.102',
                'port': 1433,
                'db': 'MS',
                'user': 'user',
                'password': 'password'
            }
        }
    }


    db = CommonDB('A系统1库',env='st')
    sql = 'select *from prac.userInfo;'
    data = db.query(sql)
    print(data)

    db = CommonDB('A系统2库', env='st')
    sql = 'select *from dbo.games;'
    data = db.query(sql)
    print(data)

    db = CommonDB('B系统1库', env='st')
    sql = 'select *from cc.students;'
    data = db.query(sql)
    print(data)