Python使用连接池连接MySQL并实现通用增删改查功能

# coding=utf-8
from datetime import datetime
import pymysql
from dbutils.pooled_db import PooledDB

log = lambda x: print(f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} {x}")


class MYSQL_UTILS(object):
    def __new__(cls, *args, **kwargs):
        if not hasattr(cls, 'instance'):
            cls.instance = super(MYSQL_UTILS, cls).__new__(cls)
        return cls.instance

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

    def implement_sql(self, sql):
        '''
        执行sql语句
        :param sql:
        :return: 执行结果
        '''
        connect, cur = self.__connect()
        try:
            cur.execute(sql)
            connect.commit()
            log(f"sql:{sql} 执行成功")
        except Exception as e:
            connect.rollback()
            log(f"sql,执行失败：{sql}")
            return False
        finally:
            cur.close()
            connect.close()
        return True

    def select(self, sql, ls=None):
        '''
        查询语句
        :param sql:
        :param ls:
        :return:
        '''
        connect, cur = self.__connect()
        try:
            cur.execute(sql, ls)
            sel_data = cur.fetchall()
        except Exception as e:
            log(f"数据查询失败：{sql}")
            sel_data = None
        finally:
            cur.close()
            connect.close()
        return sel_data

    def __connect(self):  # user, password, host, db, port=3306):
        connect = self.pool.connection()
        cur = connect.cursor()
        return connect, cur

    def update(self, item: dict, condition_item: dict, table_name: str):
        '''
        :param item:  更新字典
        :param condition_item: 条件字典
        :param table_name: 表名
        '''
        if not isinstance(item, dict) or not isinstance(condition_item, dict):
            log("参数出错")
            return False
        connect, cur = self.__connect()
        key_ls, value_ls = self.item2sql(item)
        condition_keys, condition_value = self.item2sql(condition_item)
        str_keys = [f"`{key}` = %s" for key in key_ls]
        condition_keys = [f" `{key}` =  %s " for key in condition_keys]
        update_sql = f"update {table_name} set {','.join(str_keys)} where {' and '.join(condition_keys)} "
        try:
            cur.execute(update_sql, value_ls + condition_value)
            connect.commit()
            log(f"数据更新成功{item}{condition_item}")
        except Exception as e:
            connect.rollback()
            log(update_sql)
            log(f"数据更新失败:{e}")
        finally:
            cur.close()
            connect.close()

    def insert_batch(self, item_ls, table_name):
        '''
        批量插入数据
        :return:
        '''
        if not item_ls:
            return
        else:
            connect, cur = self.__connect()
            key_ls = list(item_ls[0].keys())
            data_ls = [[data[key] for key in key_ls] for data in item_ls]
            insert_sql = f"insert ignore into {table_name}({','.join(key_ls)}) values ({','.join(['%s'] * len(key_ls))})"
            try:
                cur.executemany(insert_sql, data_ls)
                connect.commit()
                log(f"数据入库成功")
            except Exception as e:
                connect.rollback()
                log(f"数据入库失败:{e}")
            finally:
                cur.close()
                connect.close()

    def item2sql(self, item):
        key_ls, value_ls = [], []
        for key, value in item.items():
            key_ls.append(key)
            value_ls.append(value)
        return key_ls, value_ls

    def __del__(self):
        self.pool.close()