pymysql 工具类

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
pip install pymsql
"""
import pymysql
import logging


class SQLException(BaseException):
    pass


class MySQLOperation(object):
    def __init__(self, host, username, password, database):
        self.host = host
        self.username = username
        self.password = password
        self.database = database
        self.connection = self.__connection()
        self.cursor = self.connection.cursor()

    def __connection(self):
        return pymysql.connect(host=self.host, user=self.username, password=self.password,
                               database=self.database, cursorclass=pymysql.cursors.DictCursor)

    def close(self):
        if self.connection:
            self.connection.close()

    def fetchall(self, sql: str):
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def fetchone(self, sql: str):
        self.cursor.execute(sql)
        return self.cursor.fetchone()

    def __insert(self, table, data: dict):
        cols = ", ".join('`{}`'.format(k) for k in data.keys())
        val_cols = ', '.join('%({})s'.format(k) for k in data.keys())
        sql = f"insert into {table}(%s) values(%s)"
        res_sql = sql % (cols, val_cols)
        # print(res_sql)  # 'insert into users(`name`, `age`) values(%(name)s, %(age)s)'
        self.cursor.execute(res_sql, data)

    def insert(self, table, data, commit: bool):
        if data is None or (isinstance(data, list) and len(data) == 0):
            raise SQLException("数据为空")
        try:
            if isinstance(data, dict):
                self.__insert(table, data)
            if isinstance(data, list):
                for elm in data:
                    self.__insert(table, elm)
            if commit:
                self.connection.commit()
        except Exception as e:
            logging.error("发生异常: %s", e, exc_info=True)  # 记录异常信息
            if commit:
                self.connection.rollback()
            else:
                raise SQLException("sql 插入异常")

    def update_delete(self, sql):
        try:
            self.cursor.execute(sql)
            self.connection.commit()
        except Exception as e:
            logging.error("发生异常: %s", e, exc_info=True)  # 记录异常信息
            self.connection.rollback()


if __name__ == '__main__':
    utils = MySQLOperation("192.168.148.172", "test", "test",
                           "test_db")
    # #
    # # limit_ = "SELECT * FROM sys_user LIMIT 1"
    # # print(utils.fetchall(sql=limit_))
    # #
    # # utils.close()
    # # data_1 = [{"name": 'happy224122', 'age': 12, "create_date": '2023-11-15 11:00:00'},
    # #           {"name": 'happy422', 'age': 13, "create_date": '2023-11-15 12:00:00'}]
    # #
    # # utils.insert("stu_test", data_1)
    # utils.update_delete("update stu_test set name ='happy1232' where age =13")
    # utils.close()