字典是另一种可变容器模型,且可存储任意类型对象,主要是工具类,
接下来使用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: """ if not self.table_exists(table_name): self.create_table(data, table_name) sql_key = '' # 数据库行字段 sql_value = '' # 数据库值 for key in data.keys(): # 生成insert插入语句 sql_value = (sql_value + '"' + pymysql.escape_string(data[key]) + '"' + ',') sql_key = sql_key + ' ' + key + ',' self.cursor.execute( "INSERT INTO %s (%s) VALUES (%s)" % (table_name, sql_key[:-1], sql_value[:-1])) 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")