数据库连接池

 

 

 

类的写法

import json
import pymysql
from dbutils.persistent_db import PersistentDB
from threading import RLock
from abc import ABCMeta, abstractmethod
LOCK = RLock()
class Base(metaclass=ABCMeta):
    @abstractmethod
    def __init__(self, *args, **kwargs):
        pass

    def connect(self):
        self.conn = self.POOL.connection() # 创建链接
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    def connect_close(self):
        self.cursor.close()
        self.conn.close()

    def fetch_one(self, sql, args=None):
        with LOCK:
            self.cursor.execute(sql, args)
            result = self.cursor.fetchone()
        return result

    def fetch_all(self, sql, args=None):
        with LOCK:
            if args is None:
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql, args)
            record_list = self.cursor.fetchall()
        return record_list

    def fetch_all_to_json(self, sql, args, k, v):
        """
        :param sql:
        :param args:
        :param k: 数据库某个字段用于当字典的key
        :param v: 数据库某个字段用于当字典的value
        :return: json序列化的字典，字典格式 {数据库k字段的值：数据库v字段的value}
        """
        with LOCK:
            if args is None:
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql, args)
            record_list = self.cursor.fetchall()
        record_dict = {}
        for elem in record_list:
            record_dict[elem[k]] = elem[v]
        record_dict = json.dumps(record_dict)
        return record_dict

    def fetch_all_to_list(self, sql, args=None):
        with LOCK:
            if args is None:
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql, args)
            record_list = self.cursor.fetchall()
            if not record_list:
                record_list = []
        return record_list

    def fetch_list(self, sql, args=None):
        with LOCK:
            recordList = []
            if args is None:
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql, args)
            record_list = self.cursor.fetchall()
            if not record_list:
                return recordList
            for elem in record_list:
                for v in elem.values():
                    recordList.append(v)
        return recordList

    def fetch_all_to_dict(self, sql, args, k, v):
        """
        :param sql:
        :param args:
        :param k: 数据库某个字段用于当字典的key
        :param v: 数据库某个字段用于当字典的value
        :return: 返回一个字典，字典格式 {数据库k字段的值：数据库v字段的value}
        """
        with LOCK:
            if args is None:
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql, args)
            record_list = self.cursor.fetchall()
        record_dict = {}
        for elem in record_list:
            record_dict[elem[k]] = elem[v]
        return record_dict

    def fetch_APOE_to_dict(self, sql, args, k, v):
        """
        :param sql:
        :param args:
        :param k: 数据库某个字段用于当字典的key
        :param v: 数据库某个字段用于当字典的value
        :return: 返回一个字典，字典格式 {数据库k字段的值：数据库v字段的value}
        """
        with LOCK:
            if args is None:
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql, args)
            record_list = self.cursor.fetchall()
        record_dict = {}
        for elem in record_list:
            if not record_dict.get(elem[k]):
                record_dict[elem[k]] = elem[v]
            else:
                record_dict[elem[k]] += elem[v]
        return record_dict

    def fetch_all_to_itemdict(self, sql, args):
        """
        区别于fetch_all_to_dict，fetch_all_to_dict是利用求得值做key 而fetch_all_to_itemdict 是用字段做key
        :param sql:
        :param args:
        :return:
        """
        with LOCK:
            if args is None:
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql, args)
            record_list = self.cursor.fetchall()
        record_dict = {}
        if record_list:
            for elem in record_list:
                record_dict["riskNum"] = elem["riskNum"]
                record_dict["rsInfo"] = json.loads(elem["rsInfo"])
        return json.dumps(record_dict)

class ReportPool(Base):

    def __init__(self, dbdict):
        self.POOL = PersistentDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
            setsession=[],  # 开始会话前执行的命令列表
            ping=0,
            autocommit=True,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            closeable=False,
            # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接
            threadlocal=None,  # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
            # creator=pymysql,
            # maxconnections=dbdict.get("maxconnections"),  # 连接池的最大连接数
            # maxcached=dbdict.get("maxcached"),
            # maxshared=dbdict.get("maxshared"),
            # blocking=True,
            # setsession=[],
            host="",
            port=3306,
            user="",
            password="",
            database="",
            charset='utf8',
        )
        self.conn = None
        self.cursor = None
        self.connect()

    def __new__(cls, *args, **kw):
        if not hasattr(cls, '_instance'):
            cls._instance = object.__new__(cls)
        return cls._instance

class BuildDbPool(metaclass=ABCMeta):
    # 继承BuildDbPool 必须实现creat_pool的方法
    @abstractmethod
    def creat_pool(self, env):
        pass

class BuildReportPool(BuildDbPool):
    def creat_pool(self, env):
        return ReportPool(env)

report = BuildReportPool()
reportdb = report.creat_pool("beta")