Mysql工具类：根据python dict字典类型数据到Mysql，并自动创建表与列

主要是工具类，使用pymysql来创建表与SQL

示例代码

import pymysql


class UseMysql(object):
    def __init__(self, user, passwd, db, host="127.0.0.1", port=3306):
        self.db = db
        self.conn = pymysql.connect(
            host=host, user=user, passwd=passwd, db=db, port=port, charset='utf8')  # 链接数据库
        self.cursor = self.conn.cursor()

    def table_exists(self, table_name) -> bool:
        """判断表是否存在
        :param table_name: 表名
        :return: 存在返回True，不存在返回False
        """
        sql = "show tables;"
        self.cursor.execute(sql)
        tables = self.cursor.fetchall()
        for _t in tables:
            if table_name == _t[0]:
                return True
        return False

    def create_table(self, data: dict, table_name):
        """创建表"""
        # 构造数据库
        sql_key_str = ''
        columnStyle = ' text'  # 数据库字段类型
        for key in data.keys():
            sql_key_str = sql_key_str + ' ' + key + columnStyle + ','
        self.cursor.execute("CREATE TABLE %s (%s)" % (table_name, sql_key_str[:-1]))
        # 添加自增ID
        self.cursor.execute("""ALTER TABLE `{}` \
                    ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST, \
                    ADD PRIMARY KEY (`id`);"""
                            .format(table_name))
        # 添加创建时间
        self.cursor.execute(
            """ALTER TABLE {} ADD join_time timestamp NULL DEFAULT current_timestamp();""".format(table_name))

    def write_dict(self, data: dict, table_name):
        """
        写入mysql，如果没有表，创建表
        :param data: 字典类型
        :param table_name: 表名
        :return:
        """
        columns = ', '.join("`" + str(x).replace('/', '_') + "`" for x in data.keys())
        values = ', '.join("'" + str(x).replace('/', '_') + "'" for x in data.values())
        sql = "INSERT INTO %s ( %s ) VALUES ( %s );" % (table_name, columns, values)
        self.cursor.execute(sql)
        self.conn.commit()  # 提交当前事务


if __name__ == '__main__':
    mysql = UseMysql('用户名', '密码', '数据库名')
    my_data1 = {"col1": "a", "col2": "b", "col3": "c", }
    mysql.write_dict(my_data1, table_name="mytable")
    my_data2 = {"col1": "a2", "col2": "b2"}
    mysql.write_dict(my_data2, table_name="mytable")