数据库连接池(dbutils)

Mysql

0:导入模块

# !/usr/bin/python3
# -*- coding:utf-8 -*-
import os
import json
import threading

import pandas as pd
import pymysql
import cx_Oracle

import configparser
from pymysql.cursors import DictCursor
from dbutils.pooled_db import PooledDB

1:获取配置文件信息:

class Config(object):
    """
    用于从配置文件中获取连接信息
    # Config().get_content("user_information")
    配置文件里面的参数
    ***************
    [mysql]
    area={'user': ***, 'password': '***', 'sid': '***', 'database': '***'}
    ***************
    """

    def __init__(self, config_filename="config.ini"):
        """
        初始化 Config 对象
        参数:
        config_filename: str, 默认值为 "config.ini"
            配置文件的文件名
        """
        file_path = os.path.join(os.path.dirname(__file__), config_filename)
        self.cf = configparser.ConfigParser()
        self.cf.read(file_path)

    def get_sections(self):
        """
        获取配置文件中的所有节的名称
        返回值:
        sections: list
            包含配置文件中所有节的名称的列表
        """
        return self.cf.sections()

    def get_options(self, section, area):
        """
        获取 [section] 节点下 [area] 对应的各个指标数据,并以dict格式返回
        参数:
        section: str
            节的名称
        area: str
            区域的名称
        返回值:
        keys: list
            包含指标名称的列表
        values: dict
            包含指标值的字典
        """
        return json.loads(self.cf[section][area].replace("'", '"')).keys(), json.loads(
            self.cf[section][area].replace("'", '"'))

    def get_content(self, area, section):
        """
        以字典格式返回特定区域和节的内容
        参数:
        area: str
            区域的名称
        section: str
            节的名称
        返回值:
        result: dict
            包含特定区域和节的内容的字典
        """
        result = {}
        key, data = self.get_options(section, area)
        return data

2:数据池信息基类

class BasePymysqlPool(object):
    """
        数据池基类(mysql)
    """
    def __init__(self, host, port, user, password, database, charset, area=None):
        self.area = area
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.charset = charset
        self.conn = None
        self.cursor = None

3:数据库连接池主体

class MyPymysqlPool(BasePymysqlPool):
    """
    MYSQL数据库对象,负责产生数据库连接 , 此类中的连接采用连接池实现
        获取连接对象:conn = Mysql.getConn()
        释放连接对象;conn.close()或del conn
    """
    _instances = {}
    _lock = threading.Lock()

    def __new__(cls, area):
        """
            单例模式的具体实现逻辑:保证每个area都只有同一个实例.
        """
        if area not in cls._instances:
            with cls._lock:
                if area not in cls._instances:
                    cls._instances[area] = super().__new__(cls)
                    cls._instances[area]._resources = []  # 初始化资源列表
        return cls._instances[area]

    def __init__(self, area, conf_name='mysql'):
        self.conf_name = conf_name
        self.conf = Config().get_content(area, conf_name)
        super(MyPymysqlPool, self).__init__(**self.conf)
        self.__pool = PooledDB(creator=pymysql,
                               mincached=2,
                               maxcached=5,
                               host=self.host,
                               port=self.port,
                               user=self.user,
                               passwd=self.password,
                               database=self.database,
                               use_unicode=True,
                               charset="utf8",
                               cursorclass=DictCursor,
                               # thread=True,
                               )
        # 数据库构造函数,从连接池中取出连接,并生成操作游标
        # conn = self.__getConn()
        # cursor = conn.cursor()

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_value, traceback):
        return None

    def __getConn(self):
        """
        @summary: 静态方法,从连接池中取出连接
        @return conn, cursor
        """
        conn = self.__pool.connection()  # 从连接池获取一个链接
        cursor = conn.cursor()
        return conn, cursor

    def getAll(self, sql, param=None):
        """
        @summary: 执行查询,并取出所有结果集
        @param sql:查询SQL,如果有查询条件,请只指定条件列表,并将条件值使用参数[param]传递进来
        @param param: 可选参数,条件列表值(元组/列表)
        @return: pd.DataFrame /boolean 查询到的结果集
        """
        try:
            conn, cursor = self.__getConn()
            result = pd.DataFrame()
            if param is None:
                count = cursor.execute(sql)
            else:
                count = cursor.execute(sql, param)
            if count > 0:
                result = cursor.fetchall()

            else:
                result = False
            column_names = [row[0].lower() for row in cursor.description]
            if result:
                result = pd.DataFrame(result)
        except Exception as e:
            raise e
        finally:
            self.dispose(cursor, conn)
        return result

    def getOne(self, sql, param=None):
        """
        @summary: 执行查询,并取出第一条
        @param sql:查询SQL,如果有查询条件,请只指定条件列表,并将条件值使用参数[param]传递进来
        @param param: 可选参数,条件列表值(元组/列表)
        @return: result list/boolean 查询到的结果集
        """
        try:
            conn, cursor = self.__getConn()

            if param is None:
                count = cursor.execute(sql)
            else:
                count = cursor.execute(sql, param)
            if count > 0:
                result = cursor.fetchone()
            else:
                result = False
        except Exception as e:
            raise e
        finally:
            self.dispose(cursor, conn)
        return result

    def getMany(self, sql, num, param=None):
        """
        @summary: 执行查询,并取出num条结果
        @param sql:查询SQL,如果有查询条件,请只指定条件列表,并将条件值使用参数[param]传递进来
        @param num:取得的结果条数
        @param param: 可选参数,条件列表值(元组/列表)
        @return: result pd.DataFrame/boolean 查询到的结果集
        """
        try:
            conn, cursor = self.__getConn()

            result = pd.DataFrame()
            if param is None:
                count = cursor.execute(sql)
            else:
                count = cursor.execute(sql, param)
            if count > 0:
                result = cursor.fetchmany(num)
            else:
                result = False
            column_names = [row[0].lower() for row in cursor.description]
            result = pd.DataFrame(result, columns=column_names)
            if not isinstance(result, pd.DataFrame):
                return pd.DataFrame()
        except Exception as e:
            raise e
        finally:
            self.dispose(cursor, conn)

        return result

    def insertMany(self, sql, values):
        """
        @summary: 向数据表插入多条记录
        @param sql:要插入的SQL格式
        @param values:要插入的记录数据tuple(tuple)/list[list]
        @return: count 受影响的行数
        """
        try:
            conn, cursor = self.__getConn()
            count = cursor.executemany(sql, values)
        except Exception as e:
            raise e
        finally:
            self.dispose(cursor, conn)

        return count

    def __query(self, sql, param=None):
        """执行编辑相关操作,并提交事务"""
        try:
            conn, cursor = self.__getConn()
            if param is None:
                count = cursor.execute(sql)
                conn.commit()
            else:
                count = cursor.execute(sql, param)
                conn.commit()
        except Exception as e:
            raise e
        finally:
            self.dispose(cursor, conn)

        return count

    def update(self, sql, param=None):
        """
        @summary: 更新数据表记录
        @param sql: SQL格式及条件,使用(%s,%s)
        @param param: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql, param)

    def insert(self, sql, param=None):
        """
        @summary: 更新数据表记录
        @param sql: SQL格式及条件,使用(%s,%s)
        @param param: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """

        return self.__query(sql, param)

    def insert_data(self, table, data):
        column_names_str = ','.join(data.columns)
        values_str = self._make_values_str(data)
        sql = '''insert into {0} ({1}) values {2}'''.format(table, column_names_str, values_str)
        return self.__query(sql)

    def insert_update_data(self, table, data):
        """ 批量插入或更新数据
            :param table: 表格
            :param data: 數據集
        """
        # 製作批量插入SQL語句
        column_names_str = ','.join(data.columns)
        update_column_names_str = ','.join(data.columns + '=VALUES(' + data.columns + ')')
        values_str = self._make_values_str(data)
        sql = '''insert into {0} ({1}) values {2} ON DUPLICATE KEY UPDATE {3}'''.format(table,
                                                                                        column_names_str,
                                                                                        values_str,
                                                                                        update_column_names_str)
        return self.__query(sql)

    @staticmethod
    def _make_values_str(frame):
        temp_frame = frame.copy()

        # 將時間類型的列轉為字符串類型
        for i, j in zip(temp_frame.columns, temp_frame.dtypes):
            if "datetime" in str(j):
                temp_frame[i] = temp_frame[i].apply(lambda x: x if pd.isnull(x) else x.strftime('%Y-%m-%d %H:%M:%S'))

        values_list = [str(tuple(x)) for x in temp_frame.values]

        # 將None, NAN, NaT, inf替換 為null
        values_str = ','.join(values_list). \
            replace("nan", "null").replace("NaT", "null").replace("inf", "null").replace("None", "null")

        return values_str

    def delete(self, sql, param=None):
        """
        @summary: 删除数据表记录
        @param sql: SQL格式及条件,使用(%s,%s)
        @param param: 要删除的条件 值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql, param)

    def begin(self, conn):
        """
        @summary: 开启事务
        """
        conn.autocommit(0)

    def dispose(self, conn, cursor):
        """
        @summary: 释放该连接到链接池
        """

        cursor.close()
        conn.close()

Oracle

1:获取配置文件信息

class Config(object):
    """
    用于从配置文件中获取连接信息
    # Config().get_content("user_information")
    配置文件里面的参数
    ***************
    [mysql]
    area={'user': ***, 'password': '***', 'sid': '***', 'database': '***'}
    ***************
    """

    def __init__(self, config_filename="config.ini"):
        """
        初始化 Config 对象
        参数:
        config_filename: str, 默认值为 "config.ini"
            配置文件的文件名
        """
        file_path = os.path.join(os.path.dirname(__file__), config_filename)
        self.cf = configparser.ConfigParser()
        self.cf.read(file_path)

    def get_sections(self):
        """
        获取配置文件中的所有节的名称
        返回值:
        sections: list
            包含配置文件中所有节的名称的列表
        """
        return self.cf.sections()

    def get_options(self, section, area):
        """
        获取 [section] 节点下 [area] 对应的各个指标数据,并以dict格式返回
        参数:
        section: str
            节的名称
        area: str
            区域的名称
        返回值:
        keys: list
            包含指标名称的列表
        values: dict
            包含指标值的字典
        """
        return json.loads(self.cf[section][area].replace("'", '"')).keys(), json.loads(
            self.cf[section][area].replace("'", '"'))

    def get_content(self, area, section):
        """
        以字典格式返回特定区域和节的内容
        参数:
        area: str
            区域的名称
        section: str
            节的名称
        返回值:
        result: dict
            包含特定区域和节的内容的字典
        """
        result = {}
        key, data = self.get_options(section, area)
        return data

2:数据连接池基类

class BaseCxOraclePool(object):
    """
        数据池基类(oracle)
    """
    def __init__(self, user, password, sid, database, area=None):
        self.area = area
        self.user = user
        self.password = password
        self.sid = sid
        self.database = database
        self.conn = None
        self.cursor = None

3:数据库连接池主体

class CxOraclesqlPool(BaseCxOraclePool):
    """
    MYSQL数据库对象,负责产生数据库连接 , 此类中的连接采用连接池实现
        获取连接对象:conn = Mysql.getConn()
        释放连接对象;conn.close()或del conn
    """
    _instances = {}
    _lock = threading.Lock()

    def __new__(cls, area):
        if area not in cls._instances:
            with cls._lock:
                if area not in cls._instances:
                    cls._instances[area] = super().__new__(cls)
                    cls._instances[area]._resources = []  # 初始化资源列表
        return cls._instances[area]

    def __init__(self, area, conf_name='oracle'):
        self.conf_name = conf_name
        self.conf = Config().get_content(area, conf_name)
        super(CxOraclesqlPool, self).__init__(**self.conf)
        self.__pool = PooledDB(creator=cx_Oracle,
                               mincached=2,
                               maxcached=5,
                               user=self.user,
                               password=self.password,
                               dsn=self.sid,
                               )
        # 数据库构造函数,从连接池中取出连接,并生成操作游标
        # conn = self.__getConn()
        # cursor = conn.cursor()

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_value, traceback):
        # self.dispose()
        return self

    def __getConn(self):
        """
        @summary: 静态方法,从连接池中取出连接
        @return MySQLdb.connection
        """
        conn = self.__pool.connection()  # 从连接池获取一个链接
        cursor = conn.cursor()
        return conn, cursor

    def getAll(self, sql, param=None):
        """
        @summary: 执行查询,并取出所有结果集
        @param sql:查询SQL,如果有查询条件,请只指定条件列表,并将条件值使用参数[param]传递进来
        @param param: 可选参数,条件列表值(元组/列表)
        @return: result pd.DataFrame 查询到的结果集
        """
        try:
            conn, cursor = self.__getConn()

            result = pd.DataFrame()
            if param is None:
                count = cursor.execute(sql)
            else:
                count = cursor.execute(sql, param)

            result = cursor.fetchall()
            column_names = [row[0].lower() for row in cursor.description]
            result = pd.DataFrame(result, columns=column_names)
        except Exception as e:
            raise e
        finally:
            self.dispose(cursor, conn)
        return result

    def getOne(self, sql, param=None):
        """
        @summary: 执行查询,并取出第一条
        @param sql:查询SQL,如果有查询条件,请只指定条件列表,并将条件值使用参数[param]传递进来
        @param param: 可选参数,条件列表值(元组/列表)
        @return: result list/boolean 查询到的结果集
        """
        try:
            conn, cursor = self.__getConn()

            if param is None:
                count = cursor.execute(sql)
            else:
                count = cursor.execute(sql, param)

            result = cursor.fetchone()
        except Exception as e:
            raise e
        finally:
            self.dispose(cursor, conn)

        return result

    def getMany(self, sql, num, param=None):
        """
        @summary: 执行查询,并取出num条结果
        @param sql:查询SQL,如果有查询条件,请只指定条件列表,并将条件值使用参数[param]传递进来
        @param num:取得的结果条数
        @param param: 可选参数,条件列表值(元组/列表)
        @return: result pd.DataFrame/boolean 查询到的结果集
        """
        try:
            conn, cursor = self.__getConn()

            result = pd.DataFrame()
            if param is None:
                count = cursor.execute(sql)
            else:
                count = cursor.execute(sql, param)
            if count > 0:
                result = cursor.fetchmany(num)
            else:
                result = False
            column_names = [row[0].lower() for row in cursor.description]
            result = pd.DataFrame(result, columns=column_names)
            if not isinstance(result, pd.DataFrame):
                return pd.DataFrame()
        except Exception as e:
            raise e
        finally:
            self.dispose(cursor, conn)

        return result

    def insertMany(self, sql, values):
        """
        @summary: 向数据表插入多条记录
        @param sql:要插入的SQL格式
        @param values:要插入的记录数据tuple(tuple)/list[list]
        @return: count 受影响的行数
        """
        try:
            conn, cursor = self.__getConn()

            count = cursor.executemany(sql, values)
        except Exception as e:
            raise e
        finally:
            self.dispose(cursor, conn)

        return count

    def __query(self, sql, param=None):
        conn, cursor = self.__getConn()

        if param is None:
            count = cursor.execute(sql)
            conn.commit()
        else:
            count = cursor.execute(sql, param)
            conn.commit()

        self.dispose(cursor, conn)

        return count

    def update(self, sql, param=None):
        """
        @summary: 更新数据表记录
        @param sql: SQL格式及条件,使用(%s,%s)
        @param param: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql, param)

    def insert(self, sql, param=None):
        """
        @summary: 更新数据表记录
        @param sql: SQL格式及条件,使用(%s,%s)
        @param param: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """

        return self.__query(sql, param)

    def insert_data(self, table, data):
        column_names_str = ','.join(data.columns)
        values_str = self._make_values_str(data)
        sql = '''insert into {0} ({1}) values {2}'''.format(table, column_names_str, values_str)
        return self.__query(sql)

    def insert_update_data(self, table, data):
        """ 批量插入或更新数据
            :param table: 表格
            :param data: 數據集
        """
        # 製作批量插入SQL語句
        column_names_str = ','.join(data.columns)
        update_column_names_str = ','.join(data.columns + '=VALUES(' + data.columns + ')')
        values_str = self._make_values_str(data)
        sql = '''insert into {0} ({1}) values {2} ON DUPLICATE KEY UPDATE {3}'''.format(table,
                                                                                        column_names_str,
                                                                                        values_str,
                                                                                        update_column_names_str)
        return self.__query(sql)

    @staticmethod
    def _make_values_str(frame):
        temp_frame = frame.copy()

        # 將時間類型的列轉為字符串類型
        for i, j in zip(temp_frame.columns, temp_frame.dtypes):
            if "datetime" in str(j):
                temp_frame[i] = temp_frame[i].apply(lambda x: x if pd.isnull(x) else x.strftime('%Y-%m-%d %H:%M:%S'))

        values_list = [str(tuple(x)) for x in temp_frame.values]

        # 將None, NAN, NaT, inf替換 為null
        values_str = ','.join(values_list). \
            replace("nan", "null").replace("NaT", "null").replace("inf", "null").replace("None", "null")

        return values_str

    def delete(self, sql, param=None):
        """
        @summary: 删除数据表记录
        @param sql: SQL格式及条件,使用(%s,%s)
        @param param: 要删除的条件 值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql, param)

    def begin(self, conn):
        """
        @summary: 开启事务
        """
        conn.autocommit(0)

    def dispose(self, conn, cursor):
        """
        @summary: 释放连接池资源
        """
        cursor.close()
        conn.close()

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值