Python绑定变量操作Oracle数据库连接池

# -*- coding: utf-8 -*-
# @Time    : 2020/8/13 5:28 下午
# @modify  : 风过无言花易落
# @FileName: OraclePool.py
# @Software: PyCharm
# 基于cx_Oracle自带SessionPool连接池, 线程中使用, 则配置threaded=True
# 官方文档摘要:
# Oracle Real-World Performance Group的建议是使用固定大小的连接池。min和max的值应相同（且增量等于零）。
# 该防火墙，资源管理器 或用户配置文件IDLE_TIME 不应该过期空闲会话。这避免了会降低吞吐量的连接风暴。请参阅《防止连接风暴的指南：使用静态池》，其中包含有关池大小调整的详细信息。

import cx_Oracle
import os,sys


path = os.getcwd()
parent_path = os.path.dirname(path)
sys.path.append(parent_path+'\ShareLib')
from log import logs

log = logs('DEBUG',os.path.basename(__file__).split(".")[0])

class OraclePool(object):
    def __init__(self, db_msg, isomerism=False, thread=False):
        """
        创建池
        :param db_msg: 数据库信息   (user, password, dsn)
        :param isomerism: 是否异构
        :param thread: 是否线程
        """
        self.__isomerism = isomerism
        self.__thread = thread
        self.__pool = self.__get_pool(db_msg)
        log.debug(db_msg)

    def __get_pool(self, db_msg):
        """ 创建连接池 """
       
        if self.__isomerism is True:
            pool = cx_Oracle.SessionPool(dsn=db_msg[2], homogeneous=False,
                                         min=20, max=50, increment=0,
                                         encoding='UTF-8', threaded=self.__thread)
        else:
            pool = cx_Oracle.SessionPool(db_msg[0], db_msg[1], db_msg[2], homogeneous=False,
                                         min=20, max=50, increment=0,
                                         encoding='UTF-8', threaded=self.__thread)
        
        return pool

    def __get_conn(self, voucher=None):
        """ 获取连接 """
        if voucher is None:
            connection = self.__pool.acquire()
        else:
            user, password = voucher
            connection = self.__pool.acquire(user, password)

        cursor = connection.cursor()

        return connection, cursor

    def __reset_conn(self, connection):
        """ 将连接放回连接池 """
        self.__pool.release(connection)

    def __execute(self, sql, data = None, voucher=None):
        """ 获取连接, 执行sql """
        if voucher is None:
            connection, cursor = self.__get_conn()
        else:
            connection, cursor = self.__get_conn(voucher)

        if data is None:
            cursor.execute(sql)
        else:
            cursor.execute(sql,data)

        return connection, cursor

    def fetch_one(self, sql, voucher=None):
        """ 查询一条结果 """
        try:
            if self.__isomerism is True:
                connection, cursor = self.__execute(sql=sql,voucher=voucher)
            else:
                connection, cursor = self.__execute(sql)

            result = cursor.fetchone()
            self.__reset_conn(connection)
            return result
        except Exception as e:
            log.error('FetchOne:' + str(e) + 'SQL:' + sql)

    def fetch_no_result(self, sql, voucher=None):
        """ 查询所有结果 """
        try:
            if self.__isomerism is True:
                connection, cursor = self.__execute(sql=sql, voucher=voucher)
            else:
                connection, cursor = self.__execute(sql)

            cursor.fetchall()
            self.__reset_conn(connection)
            return 'True'
        except Exception as e:
            log.error('FetchAll:' + str(e) + 'SQL:' + sql)

    def fetch_all(self, sql,data = None, voucher = None):
        """ 查询所有结果 """
        try:
            if self.__isomerism is True:
                connection, cursor = self.__execute(sql=sql,data = data_dict, voucher=voucher)
            else:
                if data is None:
                    connection, cursor = self.__execute(sql)
                else:
                    connection, cursor = self.__execute(sql,data)

            result = cursor.fetchall()
            self.__reset_conn(connection)
            return result
        except Exception as e:
            log.error('fetch_all_Binding_variables:' + str(e) + 'SQL:' + sql + str(data_dict))

    def execute_sql(self, sql, data = None, voucher=None):
        """ 执行SQL语句 """
        try:
            if self.__isomerism is True:
                connection, cursor = self.__execute(sql=sql,data = data ,voucher=voucher)
            else:
                connection, cursor = self.__execute(sql ,data)
            connection.commit()
            self.__reset_conn(connection)
            return True
        except Exception as e:
            log.error('ExecuteSql:' + str(e) + 'SQL:' + sql)

    def executemany_sql(self, sql_template, data_list):
        try:
            connection, cursor = self.__get_conn()
            cursor.executemany(sql_template, data_list)
            connection.commit()
            self.__reset_conn(connection)
            return True
        except Exception as e:
            log.error('executemany_sql:' + str(e) + 'SQL:' + sql_template)

    def __del__(self):
        self.__pool.close()


if __name__ == '__main__':
    # test1 = OraclePool(db_msg='1', thread=True)
    # result = test1.fetch_one('select 1 from dual')
    # print(result)
    #
    # test2 = OraclePool(db_msg='1', isomerism=True)
    # result = test2.fetch_one('select 1 from dual', ('db', 'db'))
    # print(result)

    oracle_pool = OraclePool(('db', 'db', '123.123.123.12:1530/DB'), thread=True)
    # result = oracle_pool.fetch_one("select '11'||to_char(db.SVC_NUMBER_IDSEQ.nextval, 'FM000000000') from dual")
    # print(result)
    sql = ''' select svcnumber,mvnomark from modify_lsms 
        where PROV_CODE = {PROV_CODE} and EPARCHY_CODE = {EPARCHY_CODE} '''.format(PROV_CODE=86,
                                                                                   EPARCHY_CODE=860)
    print(oracle_pool.fetch_all(sql))