自写Python操作MySQL模块（二次封装）

前置基础知识

1. Python 操作 MySQL 基础
2. Python任意字典写入MySQL
3. pandas读MySQL数据并存为Excel

封装成类（Python3版本）

from pymysql import Connection
from my_utils import Timer  # https://blog.youkuaiyun.com/Yellow_python/article/details/80723272


class Mysql(Timer):
    def __init__(self, **kwargs):
        super().__init__()
        self.database = kwargs.pop('database')
        self.db = Connection(
            user=kwargs.pop('user', 'root'),
            password=kwargs.pop('password'),
            host=kwargs.pop('host', 'localhost'),
            database=self.database,
            port=kwargs.pop('port', 3306),
            charset=kwargs.pop('charset', 'UTF8'),
        )
        self.cursor = self.db.cursor()

    def __del__(self):
        self.cursor.close()
        self.db.close()

    def commit(self, sql):
        self.debug(sql)
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except Exception as e:
            print(e)

    def fetchall(self, query):
        self.cursor.execute(query)
        return self.cursor.fetchall()  # 有数据：tuple of tuple；无数据：()

    def fetchone(self, query, n=9999999):
        print(query)
        self.cursor.execute(query)
        for _ in range(n):
            one = self.cursor.fetchone()  # tuple
            if one:
                yield one

    def fetchone_dt(self, query, n=9999999):
        print(query)
        rowcount = self.cursor.execute(query)
        columns = [i[0] for i in self.cursor.description]
        length = len(columns)
        for _ in range(min(n, rowcount)):
            one = self.cursor.fetchone()  # tuple
            yield {
   
   columns[i]: one[i] for i in range(length)}

    def insert(self, dt, tb):
        ls = [(k, v) for k, v in dt.items() if v is not None]
        sql = 'INSERT %s (' % tb + ','.join(i[0] for i in ls) + \
              ') VALUES (' + ','.join('%r' % i[1] for i in ls