Python多线程对列执行oracle的sql及存储过程

1.多线程运行sql，使用cx_Oracle

a.首先安装pip install cx_Oracle
b.oracle的数据库实例下载及安装：https://www.lfd.uci.edu/~gohlke/pythonlibs/#cx_oracle
c.关于加载oracle出现的问题见下面：
instance实例下载地址: https://www.lfd.uci.edu/~gohlke/pythonlibs/#cx_oracle
实例报错问题：https://blog.csdn.net/qq_36227528/article/details/102758559

#封装的cx_Oracle文件

# 封装的cx_Oracle文件# exec_proc.py
import cx_Oracle

# conn=cx_Oracle.connect('deep/deep@22.33.44.26/xe')
# cur=conn.cursor()
# result = cur.execute('select * from classinfo')
# print(cur.fetchall())
# cur.close()
# conn.close()

class HandleSql():
    """
    处理封装Oracle
    """

    def __init__(self):
        self.conn=cx_Oracle.connect('deep/deep@22.33.44.26/xe')
        self.cursor = self.conn.cursor()

    def makeDictFactory(self,cursor):
        columnNames = [d[0] for d in cursor.description]

        def createRow(*args):
            return dict(zip(columnNames, args))

        return createRow

    def close_oracle(self):
        """
        关闭sql连接
        :return:
        """
        self.cursor.close()
        self.conn.close()

    def run_sql(self, sql, args=None, is_more=False):
        """
        执行sql语句
        :param sql:
        :param args:
        :param is_more:
        :return:
        """
        try:
            if args:
                self.cursor.execute(sql, args)
            else:
                self.cursor.execute(sql)
            self.cursor.rowfactory = self.makeDictFactory(self.cursor)
            self.conn.commit()
        except Exception as err:
            print("执行存储过程报错异常信息为：{}".format(err))
            raise

        if is_more:
            return self.cursor.fetchall()
        else:
            return self.cursor.fetchone()

    def run_proc(self, proc, args=None):
        """
        调用存储过程
        :param proc:
        :param args:
        :return:
        """
        try:
            if args:
                result = self.cursor.callproc(proc, args)
            else:
                result = self.cursor.callproc(proc)
            self.conn.commit()
        except Exception as err:
            print("执行存储过程报错异常信息为：{}".format(err))
            raise
        return result


if __name__ == '__main__':
    sql = "select * from classinfo"
    hs_obj = HandleSql()
    # print(hs_obj.run_sql(sql))
    # print(hs_obj.run_proc("procedure1",args=["laowang"]))
    result = hs_obj.run_proc("procedure2", args=[5,""])
    print(result[1])

执行的多线程文件

# multi2.py
from threading import Thread
from queue import Queue
from exec_proc import HandleSql

"""
create or replace procedure procedure2 
(
  param1 in  NUMBER, 
  param2 out VARCHAR2
) as 
var_a VARCHAR2(10);
begin
    
 select t.classname into param2 from classinfo t where t.classid=param1;
-- DBMS_OUTPUT.PUT_LINE(param2);	
end procedure2;
"""

def runsql(q:Queue):
    while True:
        hand_sql = HandleSql()
        if q.empty():
            print("队列中的用例执行完成,程序退出")
            hand_sql.close_oracle()
            return True
        value = q.get()
        result = hand_sql.run_proc("procedure2", args=[value, ""])
        if result[1] is None:
            print("返回结果有误")
        print(result)


def main():
    thread_list = []
    q=Queue()

    for value in range(1, 7):
        q.put(value)

    for i in range(3):
        th = Thread(target=runsql, args=(q,))
        th.start()
        thread_list.append(th)

    for j in thread_list:
        j.join()


if __name__ == '__main__':
    main()
    print("结束了")

2.使用连接池

连接池文件的封装db_unils.py

# db_unils.py 
from dbutils.pooled_db import PooledDB
import cx_Oracle


class HandleSql():
    """
    处理封装Oracle
    """

    def __init__(self,num):
        self.__pool = self.get_poll(num)

    def get_poll(self,num):
        dsn = cx_Oracle.makedsn("22.33.44.26", 1521, service_name="XE")
        pool = PooledDB(cx_Oracle, mincached=num, blocking=True, user="deep", password="deep", dsn=dsn)
        return pool

    def get_conn(self):
        conn = self.__pool.connection()
        cursor = conn.cursor()
        return conn,cursor

    @staticmethod
    def close_conn(conn,cur):
        cur.close()
        conn.close()

    def makeDictFactory(self,cursor):
        columnNames = [d[0] for d in cursor.description]

        def createRow(*args):
            return dict(zip(columnNames, args))
        return createRow

    def run_sql(self, sql, args=None, is_more=False):
        """
        执行sql语句
        :param sql:
        :param args:
        :param is_more:
        :return:
        """
        try:
            conn, cursor = self.get_conn()
            if args:
                cursor.execute(sql, args)
            else:
                cursor.execute(sql)
            cursor.rowfactory = self.makeDictFactory(cursor)
            conn.commit()
        except Exception as err:
            print("执行sql报错异常信息为：{}".format(err))
            raise
        if is_more:
            return cursor.fetchall()
        else:
            return cursor.fetchone()
        self.close_conn(conn,cursor)

    def run_proc(self, proc, args=None):
        """
        调用存储过程
        :param proc:
        :param args:
        :return:
        """
        conn, cursor = self.get_conn()
        try:
            if args:
                result = cursor.callproc(proc, args)
            else:
                result = cursor.callproc(proc)
            conn.commit()
        except Exception as err:
            print("执行存储过程报错异常信息为：{}".format(err))
            raise

        self.close_conn(conn, cursor)
        return result


if __name__ == '__main__':
    # sql = "select * from classinfo"
    hs_obj = HandleSql(3)
    # print(hs_obj.run_sql(sql,is_more=True))
    print(hs_obj.run_proc("procedure1",args=["laowang"]))
    result = hs_obj.run_proc("procedure2", args=[5,""])
    print(result[1])

from threading import Thread
from queue import Queue
from db_unils import HandleSql

"""
create or replace procedure procedure2 
(
  param1 in  NUMBER, 
  param2 out VARCHAR2
) as 
var_a VARCHAR2(10);
begin
    
 select t.classname into param2 from classinfo t where t.classid=param1;
-- DBMS_OUTPUT.PUT_LINE(param2);	
end procedure2;
"""
hand_sql = HandleSql(3)

def runsql(q:Queue):
    while True:
        if q.empty():
            print("队列中的用例执行完成,程序退出")
            return True
        value = q.get()
        result = hand_sql.run_proc("procedure2", args=[value, ""])
        if result[1] is None:
            print("返回结果有误")
        print(result)


def main():
    thread_list = []
    q=Queue()

    for value in range(1, 7):
        q.put(value)

    for i in range(3):
        th = Thread(target=runsql, args=(q,))
        th.start()
        thread_list.append(th)

    for j in thread_list:
        j.join()


if __name__ == '__main__':
    main()
    print("结束了")