python 链接activemq python 链接多个数据库

python链接不同数据库会使用到不同的第三方库，SQLAlchemy可以很好的整合大部分常用数据库，利用SQLAlchemy可以很好的完成自用的数据库链接模块。因python中数据存储常用到DataFrame，所以在查询中增加了统一DataFrame格式输出，方便后续的操作。
因为工作原因，目前只完成了Oracle、MySQL、sqlserver、postgre四种数据库的查询部分，后续会陆续完善其余部分的相关操作。

# 更新时间：2022-01-20
import pandas as pd
from sqlalchemy import create_engine
from urllib import parse
import common.save_log as sl

'''
select 查询
insert 单条新增
update 修改
truncate 清空
connect 数据库连通性测验
insert_table 全表插入
'''
SQL_TYPE = ['select', 'insert', 'update', 'truncate', 'connect', 'insert_table']


def conn_oracle(database, sql_type, sql, like_str=None, data=None, table=None, schema=None):
    if sql_type not in SQL_TYPE:
        result = 'sql_type值不符合规定'
        return result
    else:
        if 'service_name' in database.keys():
            engine = create_engine(
                'oracle+cx_oracle://%s:%s@%s:%s/?service_name=%s' % (
                    database['username'], parse.quote(database['password']), database['ip'], database['port'],
                    database['service_name']))
        else:
            engine = create_engine(
                'oracle+cx_oracle://%s:%s@%s:%s/%s' % (
                    database['username'], parse.quote(database['password']), database['ip'], database['port'],
                    database['sid']))
        conn = engine.connect()
        if sql_type == 'select':
            if like_str is not None:
                data = conn.execute(sql, like_str)
            else:
                data = conn.execute(sql)
            df_data = __select_data_to_df(data)
        elif sql_type == 'insert':
            conn.execute(sql)
            df_data = 0
        elif sql_type == 'update':
            conn.execute(sql)
            df_data = 0
        elif sql_type == 'truncate':
            conn.execute(sql)
            df_data = 0
        elif sql_type == 'insert_table':
            pd.io.sql.to_sql(data, table, engine, index=False, schema=schema, if_exists='append')
            df_data = 0
        conn.close()
        return df_data


def conn_mysql(database, sql_type, sql, like_str=None):
    if sql_type not in SQL_TYPE:
        result = 'sql_type值不符合规定'
        return result
    else:
        engine = create_engine(
            'mysql+pymysql://%s:%s@%s:%s/%s' % (
                database['username'], parse.quote(database['password']), database['ip'], database['port'],
                database['db_name']))
        is_connect = False
        for i in range(2):
            try:
                conn = engine.connect()
                is_connect = True
                if sql_type == 'connect':
                    return True
            except BaseException as e:
                sl.put_log_txt(e, '数据库链接')
                continue
        if is_connect:
            if sql_type == 'select':
                if like_str is not None:
                    data = conn.execute(sql, like_str)
                else:
                    data = conn.execute(sql)
                df_data = __select_data_to_df(data)
            elif sql_type == 'insert':
                df_data = conn.execute(sql)
            elif sql_type == 'update':
                df_data = conn.execute(sql)
            conn.close()
        else:
            return False
        return df_data


def conn_sqlserver(database, sql_type, sql, like_str=None):
    if sql_type not in SQL_TYPE:
        result = 'sql_type值不符合规定'
        return result
    else:
        username = database['username']
        pwd = parse.quote(database['password'])
        ip = database['ip']
        db_name = database['db_name']
        engine = create_engine('mssql+pyodbc://%s:%s@%s/%s?driver=ODBC+Driver+17+for+SQL+Server' % (username, pwd, ip, db_name))# ODBC的版本要和运行环境安装的版本一致
        is_connect = False
        for i in range(2):
            try:
                conn = engine.connect()
                is_connect = True
                if sql_type == 'connect':
                    return True
            except BaseException as e:
                sl.put_log_txt(e, '数据库链接')
                continue
        if is_connect:
            if sql_type == 'select':
                if like_str is not None:
                    data = conn.execute(sql, like_str)
                else:
                    data = conn.execute(sql)
                df_data = __select_data_to_df(data)
            elif sql_type == 'insert':
                df_data = conn.execute(sql)
            elif sql_type == 'update':
                df_data = conn.execute(sql)
            conn.close()
        else:
            return False
    return df_data


def conn_postgre(database, sql_type, sql, like_str=None):
    if sql_type not in SQL_TYPE:
        result = 'sql_type值不符合规定'
        return result
    else:
        username = database['username']
        pwd = parse.quote(database['password'])
        ip = database['ip']
        if 'port' in database:
            port = ':%s' % database['port']
        else:
            port = ''
        db_name = database['db_name']
        if 'charset' in database:
            charset = '?charset=%s' % database['charset']
        else:
            charset = ''
        engine = create_engine('postgresql+psycopg2://%s:%s@%s%s/%s%s' % (username, pwd, ip, port, db_name, charset))
        conn = engine.connect()
        if sql_type == 'select':
            if like_str is not None:
                data = conn.execute(sql, like_str)
            else:
                data = conn.execute(sql)
            df_data = __select_data_to_df(data)
        elif sql_type == 'insert':
            df_data = conn.execute(sql)
        elif sql_type == 'update':
            df_data = conn.execute(sql)
        conn.close()
    return df_data


def __select_data_to_df(data):
    '''
    函数内容：数据库select所得数据转存为DataFrame
    参数：data 数据库select所得数据
    返回值：df_data DataFrame格式的数据库select数据，包含列名（数据库中对应列名）
    '''
    list_data = []
    list_column = [i[0] for i in data.cursor.description]
    for item in data:
        list_data.append(item)
    df_data = pd.DataFrame(data=list_data, columns=list_column)
    return df_data

第三方库的导入问题
除了py文件中明确import的第三方库，不同数据库还需要各自的第三方库：
oracle==>cx_Oracle
mysql==>PyMySQL
sqlserver==>pyodbc
postgre==>psycopg2
数据库连接问题
1、密码中若有特殊符号，例如@时，若直接传参，会令程序识别出错，需要转义传参，如下：

engine = create_engine( 'mysql+pymysql://%s:%s@%s:%s/%s' % (
         database['username'], parse.quote(database['password']), database['ip'],database['port'],database['db_name']))

2、sql server来连接器有多种，在尝试了pymssql和pyodbc后，选择了pyodbc，好处是能够避免读写时中文乱码问题。但需注意运行环境需要安装odbc的驱动。
驱动下载地址：https://docs.microsoft.com/zh-cn/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15sql执行问题
1、select语句执行like时，会用到%，但在python中会将其认为传参字符，需特殊处理，链接代码见conn_mysql函数部分，传参方式如下

like_str= ['%%%s%%' % string]
sql = "SELECT id FROM table where column like %s"
data = conn_database.conn_mysql(OA_DATABASE_DETAIL, 'select', sql,like_str=like_str)

2、sql值中含有单引号时，sql会错误识别，导致执行失败，若要带单引号，需将单引号转化为两个单引号，但转化时要注意，不能整个sql做替换，要在值中做替换。示例如下：

s = "a'b"
sql = "select * from table_name where name='%s'" %s
# 正确替换
s = s.replace("'","''")
# 错误替换
sql = sql.replace("'","''")

最后
该代码是自用，适用场景有限，代码格式也不算太标准，若有任何建议欢迎反馈。