Python 数据库连接池

连接池可以有效减少与数据库建立连接和关闭的一个开销。

以下代码记录：

import pymysql
from dbutils.pooled_db import PooledDB
import pandas as pd

class SqlFunct():
    __instance = None

    def __new__(cls):
        if cls.__instance is None:
            cls.__instance = super().__new__(cls)
            return cls.__instance
        else:
            return cls.__instance

    def __init__(self):
        config = {
            'creator': pymysql,
            'host': "",
            'port': "",
            'user': "",
            'password': "",
            'db': "",
            'charset': "utf8mb4",
            'maxconnections': 10,  # 连接池最大连接数量
            'cursorclass': pymysql.cursors.DictCursor
        }
        self.pool = PooledDB(**config)

    def get_connection(self):
        conn = self.pool.connection()
        cursor = conn.cursor()
        return conn,cursor

    def insert_db(self,sql,value):
        """
        插入数据库
        """
        try:
            conn,cursor = self.get_connection()
            cursor.execute(sql, value)
            conn.commit()
            # cursor.close()
            conn.close()
            print('完成插库', value[2])
            return 1

        except Exception as e:
            # print(e)
            if 'Duplicate' in str(e):
                return 0
            else:
                print(f"插入数据时出现了错误：{e} | {value[2]}")
            if 'TaskConfig_UN' in str(e) or 'ErrorTask_UN' in str(e):
                print(f'TaskConfig_UN or ErrorTask_UN | {value}')
                return 2
            else:
                return 0

    def update_db(self,sql):
        """
        更新数据库
        """
        try:
            conn,cursor = self.get_connection()
            cursor.execute(sql)
            conn.commit()
            # cursor.close()
            conn.close()
        except Exception as e:
            print(f"更新信息时出现了错误:{e}")

    def update_db2(self,sql, value):
        """
        更新数据库
        """
        try:
            conn,cursor = self.get_connection()
            cursor = conn.cursor()
            cursor.execute(sql, value)
            conn.commit()
            # cursor.close()
            conn.close()
        except Exception as e:
            print(f"更新信息时出现了错误:{e} | {value}")

    def select_db(self,sql):
        """
        查询数据库
        """
        try:
            conn,cursor = self.get_connection()
            cursor.execute(sql)
            info = cursor.fetchall()
            # cursor.close()
            conn.close()
            return info
        except Exception as e:
            print(e)