PyMySQL插入字典类型的数据-526互联

import pymysql

MYSQL_CONFIG = {
    'host': 'localhost',  # IP地址
    'port': 3306,  # 端口
    'user': 'root',  # 用户名
    'passwd': '123456',  # 密码
    'db': 'layui',  # 数据库
    '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 myapp_user(%s) VALUES(%s)
    """ % (cols, val_cols)
    return sql


def main():
    student_list = []
    # 模拟数据 正常通过模型类ORM获取
    for id in range(1,100):
        import random
        firstname = random.sample(['刘', '陈', '张', '李', '王', '赵', '孙', '周', '吴', '郑'], 1)
        lastname = random.sample(['一', '二', '三', '四', '五', '六', '七', '八', '九', '十'], 1)
        name = str(firstname[0]) + str(lastname[0])  #用户名
        sex = random.sample(['男','女'], 1)  #随机选一个内容生成新的列表
        sex = sex[0]
        age = random.randint(1,120)
        email = "test" + str(id) + "@email.com"
        popularity = random.randint(1,1000)
        row = {'id': id, 'username': name, 'sex': sex, 'age': age, 'email': email, 'popularity': popularity}  
        student_list.append(row)  #student_list格式[{},{},{}]


    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()