pymysql封装工具类

一、工具类：dbutil.py

# 导包
import pymysql

# 创建工具类
class DBUtil():
    # 初始化
    __conn = None
    __cursor = None

    # 创建工具方法
    # 创建连接
    @classmethod
    def __get_conn(cls):
        if cls.__conn is None:
            cls.__conn = pymysql.Connect(host="localhost",
                                       port=3306,
                                       user="root",
                                       password="root",
                                       database="books")
        return cls.__conn

    # 获取游标
    @classmethod
    def __ger_cursor(cls):
        if cls.__cursor is None:
            cls.__cursor = cls.__get_conn().cursor()
        return cls.__cursor

    # 执行sql
    @classmethod
    def exe_sql(cls, sql):
        try:
            print('exe_sql start')
            cursor = cls.__ger_cursor()
            cursor.execute(sql)
            if sql.split()[0].lower() == 'select':
                return cursor.fetchall()
            else:
                print("是非查询类型")
                cls.__conn.commit()
                return cursor.rowcount

        except Exception as e:
            cls.__conn.rollback()
            print(e)
        finally:
            cls.__close_cursor()
            cls.__close_conn()

    # 关闭游标
    @classmethod
    def __close_cursor(cls):
        if cls.__cursor:
            cls.__cursor.close()
            cls.__cursor = None

    # 关闭连接
    @classmethod
    def __close_conn(cls):
        if cls.__conn:
            cls.__conn.close()
            cls.__conn = None

二、调用：dbutil_verify.py

# 导入方法
from dbutil import DBUtil
sql = "select id 图书id, title 图书名称,`read` 阅读量,`comment` 评论量 from t_book;"
# sql = "insert into t_book(id, title, pub_date) values('10','东游记','1986-01-01');"
# sql = "delete from t_book where id = 10;"
result = DBUtil.exe_sql(sql)
print(result)