import pymysql
from dbutils.pooled_db import PooledDB

class MysqlPool:
    def __init__(self) -> None:
        self.pool = PooledDB(
            creator=pymysql,
            maxconnections=6,   # 允许最大连接数
            mincached=2,        # 初始化时，最少空闲连接数
            maxcached=5,        # 最多的连接
            maxshared=3,        # 最多共享的连接
            blocking=True,      # 没有连接时， 十分阻塞True等待
            maxusage=None,      # 一个连接最多被重复使用的次数， None表示无限制
            setsession=[],      # 开始会话前执行的命令列表
            ping=0,             # Ping通mysql服务器十分连接
            host='127.0.0.1',
            port=3306,
            user='root',
            password='chenhaifeng',
            database='pymysql',
            charset='utf8'
        )

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

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

    def fetchall(self, sql, *args):
        conn, cursor = self.open()
        cursor.execute(sql, args)
        res = cursor.fetchall()
        self.close(cursor=cursor, conn=conn)
        return res

    def fetchone(self, sql, *args):
        conn, cursor = self.open()
        cursor.execute(sql, args)
        res = cursor.fetchone()
        self.close(cursor=cursor, conn=conn)
        return res

    def insert(self, sql, *args):
        conn, cursor = self.open()
        res = cursor.execute(sql, args)
        conn.commit()
        self.close(cursor=cursor, conn=conn)
        return res

    def update(self, sql, *args):
        conn, cursor = self.open()
        res = cursor.execute(sql, args)
        conn.commit()
        self.close(cursor=cursor, conn=conn)
        return res

    def delete(self, sql, *args):
        conn, cursor = self.open()
        res = cursor.execute(sql, args)
        conn.commit()
        self.close(cursor=cursor, conn=conn)
        return res

db = MysqlPool()

# res = db.insert('insert into users values(%s, %s)', ("1743", "123456"))
# res = db.fetchone("show tables;")
# res = db.insert('insert into users(email, password) values (%s, %s)', '1233@qq.com', '123456')
# res = db.update('update users set email=%s, password=%s where id = %s', '999@qq.com', '999', 1)
# res = db.delete('delete from users where id = %s', 1)
res = db.fetchall('select email, password from users;')
print(res)