oracle连接池

oracle连接池

# -*- coding: utf-8 -*-
"""
@Time    : 2021/7/20 11:34
@Author  : Little Duo
@File    : OracleUtil.py
"""

import os
import cx_Oracle
from dbutils.pooled_db import PooledDB
from util.LoggerUtil import logger as Log

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'


class OracleConnection(object):
    def __init__(self, oracleConfig):
        """
        初始化oracle连接池
        @param oracleConfig:

        """
        self.pool = PooledDB(
            user=oracleConfig['user'],
            password=oracleConfig['password'],
            dsn=cx_Oracle.makedsn(oracleConfig['host'], oracleConfig['port'], oracleConfig['db']),
            maxconnections=int(oracleConfig['maxconnections']),
            maxcached=int(oracleConfig['maxcached']),
            mincached=int(oracleConfig['mincached']),
            creator=cx_Oracle,
            blocking=True,
            maxusage=None
        )
        self.queryTableColumnSQL = "SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '{}' ORDER BY COLUMN_ID"
        self.isExistsTableSQL = "SELECT COUNT(1) AS COUNTS FROM USER_TABLES WHERE TABLE_NAME = '{}'"
        self.queryTableCountsSQL = "SELECT COUNT(1) AS COUNTS FROM {}"
        self.truncateTableSQL = "TRUNCATE TABLE {}"

    @staticmethod
    def transDictCursor(cursor):
        """
        将执行sql的结构转化为字典类型
        @param cursor:
        @return:
        """
        column_names = list(map(lambda x: x, [d[0] for d in cursor.description]))
        rows = list(cursor.fetchall())
        result = [dict(zip(column_names, row)) for row in rows]
        return result

    def __del__(self):
        """
        在实例资源被回收时，关闭该连接池
        """
        try:
            self.pool.close()
        except Exception as e:
            Log.error(e)

    def query(self, sql, transDict: bool = True):
        """

        执行sql返回结果
        @param sql:
        @param transDict:是否返回字典格式
        @return:
        """
        try:
            connect = self.pool.connection()
            cursor = connect.cursor()
            cursor.execute(sql)
            if transDict:
                result = self.transDictCursor(cursor)
            else:
                result = cursor.fetchall()
            Log.info('| ORACLE | 数据查询成功 | SQL:{}'.format(sql))
            return result
        except Exception as e:
            Log.error('| ORACLE | 数据查询失败 | 原因:{} | SQL:{}'.format(e, sql))

    def queryTableColumns(self, tableName):
        """
        查询表的所有列
        @param tableName:
        @return:
        """
        result = self.query(self.queryTableColumnSQL.format(tableName.upper()), transDict=False)
        if len(result) > 0:
            columnNameList = []
            for i in result:
                columnNameList.append(i[0])
            return columnNameList

    def isExistsTable(self, tableName):
        """
        判断改表是否存在

        @param tableName:
        @return:
        """
        result = self.query(self.isExistsTableSQL.format(tableName.upper()))
        try:
            if result[0]['counts'] > 0:
                return True
        except Exception as e:
            Log.error(e)
        return False

    def queryTableCounts(self, tableName):
        """
        查询表总条数

        @param tableName:
        @return:
        """
        return self.query(self.queryTableCountsSQL.format(tableName.upper()))

    def insertBatch(self, sql, nameParams: list):
        """
        批量插入数据

        :param sql:
        :param nameParams:
        :return:
        """
        try:
            connect = self.pool.connection()
            cursor = connect.cursor()
            cursor.prepare(sql)
            result = cursor.executemany(None, nameParams)
            effrows = len(nameParams)
            connect.commit()
            # Log.info('| ORACLE | 数据批量插入成功! 影响行数:{} SQL:{}'.format(effrows, sql))
            Log.info('| ORACLE | 数据批量插入成功! 影响行数:{} '.format(effrows))
            return effrows
        except Exception as e:
            Log.error('| ORACLE | 数据批量插入失败! 原因:{} SQL:{}'.format(e, sql))

    def execSQL(self, sql):
        """
        执行SQL

        :param sql:
        :return:
        """

        try:
            connect = self.pool.connection()
            cursor = connect.cursor()
            cursor.execute(sql)
            connect.commit()
            Log.info('| ORACLE | 执行SQL成功! | SQL: {}'.format(sql))
            return True
        except Exception as e:
            Log.error('| ORACLE | 执行SQL失败! 原因:{} | SQL:{} ,'.format(e, sql))
            return False

    def truncateTable(self, tableName):
        """
        截断表

        :param tableName:
        :return:
        """
        return self.execSQL(self.truncateTableSQL.format(tableName))

    def execProcedure(self, procedureName, hasOutparam: bool = False, inparams: list = None):
        """
        执行存储过程

        @param procedureName: 存储过程名称
        @param hasOutparam: 是否有出参
        @param inparams: 入参
        @return: 出参
        """

        Log.info('| ORACLE | 开始执行执行存储过程 | PROCEDURE_NAME:{} | INPARAMS: {}'.format(procedureName, inparams))
        try:
            connect = self.pool.connection()
            cursor = connect.cursor()
            outparam = None
            if inparams is None:
                inparams = []
            if hasOutparam:
                outmsg = cursor.var(cx_Oracle.STRING)  # plsql出参类型
                inparams.append(outmsg)
                cursor.callproc(procedureName, inparams)
                # print(outmsg)
                # print(outmsg.getvalue())
                outparam = outmsg.getvalue()
                return outparam
            else:
                cursor.callproc(procedureName, inparams)
            Log.info('| ORACLE | 执行执行存储过程完毕 | PROCEDURE_NAME:{} | INPARAMS: {} | OUTARAMS: {} '.format(procedureName, inparams, outparam))
        except Exception as e:
            Log.error('| ORACLE | 执行执行存储过程失败 原因:{} | PROCEDURE_NAME:{} '.format(e, procedureName))