pymysql工具类封装

pymysql工具类封装

提供的功能：

1.获取数据库连接

2.关闭数据库连接

3.查询一条记录

4.查询多条记录

5.增删改语句

import pymysql

host = "127.0.0.1"
user = "root"
password = "han"
port = 3307
database = "school"


class DBUtils:
    # 添加类属性
    conn = None

    # 获取链接
    @classmethod
    def __getConn(cls):
        if cls.conn is None:
            cls.conn = pymysql.connect(host=host, user=user, password=password, port=port, database=database)

        return cls.conn

    # 关闭链接
    @classmethod
    def __closeConn(cls):
        if cls.conn is not None:
            cls.conn.close()
            cls.conn = None

    # 查询一条
    @classmethod
    def select_one(cls, sql):
        cursor = None
        result = None
        try:
            cls.conn = cls.__getConn()
            cursor = cls.conn.cursor()  # 获取游标
            cursor.execute(sql)  # 执行sql
            result = cursor.fetchone()  # 提取结果集

        except Exception as e:
            print(e)
        finally:
            cursor.close()  # 关闭游标
            cls.__closeConn()  # 关闭连接
            print("最后执行")
            return result

    # 查询多条
    @classmethod
    def select_all(cls, sql):
        cursor = None
        result = None
        try:
            cls.conn = cls.__getConn()
            cursor = cls.conn.cursor()  # 获取游标
            cursor.rownumber = 0  # 将游标位置回退为0
            cursor.execute(sql)  # 执行sql
            result = cursor.fetchall()  # 提取结果集

        except Exception as e:
            print(e)
        finally:
            cursor.close()  # 关闭游标
            cls.__closeConn()  # 关闭连接
            return result

    # 增删改
    @classmethod
    def uid_db(cls, sql):
        cursor = None
        try:
            cls.conn = cls.__getConn()
            cursor = cls.conn.cursor()
            cursor.execute(sql)
            cls.conn.commit()  # 提交事务
            print(f"受影响的行数：{cls.conn.affected_rows()}")
        except Exception as e:
            print(e)
            cls.conn.rollback()  # 回滚事务
        finally:
            cursor.close()  # 关闭游标
            cls.__closeConn()  # 关闭连接


if __name__ == '__main__':
    res = DBUtils.select_one("select * from user")
    res_all = DBUtils.select_all("select * from user")
    DBUtils.uid_db("update user set username='大大怪1' where id =12 ")
    print(res_all)