import pymysql
from dbutils.pooled_db import PooledDB


class DbPool(object):
    # 类变量
    # __private_attrs 类的私有属性
    # __private_method 类的私有属性
    __pool = None
    def __init__(self, db_config):
        # 构造函数，创建数据库连接、游标
        self.coon = DbPool.get_mysql_conn(db_config)
        self.cur = self.coon.cursor(cursor=pymysql.cursors.DictCursor)

    # 数据库连接池连接
    @staticmethod
    def get_mysql_conn(db_config):
        # 声明为全局变量
        global __pool
        if DbPool.__pool is None:
            __pool = PooledDB(creator=pymysql, mincached=1, maxcached=20, host=db_config['host'],
                              user=db_config['user'], passwd=db_config['passwd'], db=db_config['db'],
                              port=db_config['port'], charset=db_config['charset'])
        return __pool.connection()

    # 插入\更新\删除sql
    def op_update(self, sql, param):
        print('op_insert', sql, param)
        insert_num = self.cur.execute(sql, param)
        # print('mysql sucess ', insert_num)
        self.coon.commit()
        return insert_num

    # 查询
    def op_query(self, sql, parm):
        print('op_select', sql, parm)
        self.cur.execute(sql, parm)  # 执行sql
        select_res = self.cur.fetchall()  # 返回结果为字典
        print('op_select', select_res)
        return select_res

    # 批量更新数据
    def op_update_list(self, list):
        sum = 0
        try:
            for tuple in list:
                sql, parm = tuple
                insert_num = self.cur.execute(sql, parm)
                if (insert_num == 0):
                    self.coon.rollback()
                    break
                sum += insert_num
            self.coon.commit()
        except:
            print('事务回滚')
            self.coon.rollback()
        return sum
        # 释放资源

    # 释放资源
    def dispose(self):
        self.coon.close()
        self.cur.close()


if __name__ == '__main__':
    name = "Alice"
    age = 25
    print(f"Hello, my name is {name} and I am {age} years old")
    age = 25
    print("I am %d years old" % age)

    db_config = {
        "host": 'localhost',
        "user": 'root',
        "passwd": '123456',
        "db": 'account',
        "port": 3306,
        "charset": 'utf8mb4'
    }

    db = DbPool(db_config)
    dt_list = db.op_query("select * from user where userid > %s order by userid desc limit 1", (23))
    for nd in dt_list:
        print(nd)

    res = db.op_update("update user set register_date = %s where userid > %s and userid < %s", ("2024-04-19 16:42:50", 17702, 17705))
    print("res is ", res)


    print("start app ! ")