详解PyMySQL插入字典类型的数据

student_list = [

     { "name" : "Tony" , "age" : 19, "sex" : "male" },

     { "name" : "Lisa" , "age" : 18, "sex" : "female" },

     { "name" : "Jack" , "age" : 20, "sex" : "male" }

]

cursor .executemany(sql, data_list)

def generate_sql(data_list):

     data = data_list[0]  # 取出第一条

     cols = ", " . join ( '`{}`' .format(k) for k in data.keys())  # 字段名拼接

     val_cols = ', ' . join ( '%({})s' .format(k) for k in data.keys())  # 对应值的占位符拼接

     sql = "" "

     INSERT INTO student(%s) VALUES(%s)

     " "" % (cols, val_cols)

     return sql

INSERT INTO student(` name `, `age`, `sex`) VALUES (%( name )s, %(age)s, %(sex)s)

import pymysql

MYSQL_CONFIG = {

     'host' : 'localhost' ,  # IP地址

     'port' : 3306,  # 端口

     'user' : 'myuser' ,  # 用户名

     'passwd' : '111111' ,  # 密码

     'db' : 'myclass' ,  # 数据库

     'charset' : 'utf8' ,  # 编码

}

def generate_sql(data_list):

     data = data_list[0]

     cols = ", " . join ( '`{}`' .format(k) for k in data.keys())

     val_cols = ', ' . join ( '%({})s' .format(k) for k in data.keys())

     sql = "" "

     INSERT INTO student(%s) VALUES(%s)

     " "" % (cols, val_cols)

     return sql

def main():

     student_list = [

         { 'name' : 'Tony' , 'age' : 19, 'sex' : 'male' },

         { 'name' : 'Lisa' , 'age' : 18, 'sex' : 'female' },

         { 'name' : 'Jack' , 'age' : 20, 'sex' : 'male' }

     ]

     conn = pymysql. connect (**MYSQL_CONFIG)  # 数据库连接

     cur = conn. cursor ()  # 游标对象

     sql = generate_sql(student_list)

     cur.executemany(sql, student_list)

     conn. commit ()

     cur. close ()

     conn. close ()

if __name__ == '__main__' :

     main()