pyhton学习笔记（4）数据库操作-字典数据插入语句

使用pymysql进行数据库插入数据

import pymysql
import random,string

def connection():
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='root',
        db='dbname',
        charset='utf8',
        # autocommit=True,    # 如果插入数据，， 是否自动提交? 和conn.commit()功能一致。
    )
    cur = conn.cursor()
    sql_str = "select * from dbname.tablename;"
    result = cur.execute(sql_str)  # 默认不返回查询结果集， 返回数据记录数。
    print(result)
    # 随机生成11位字母加数字
    ran_str = ''.join(random.sample(string.ascii_letters + string.digits, 11))
    print(type(ran_str))
    # 待插入数据库数据
    data = {
        'id': ran_str,
        'name': 'Lily',
        'description': '20',
        'status': False
    }

    table = 'table_name'
    keys = ', '.join(data.keys())
    values = ', '.join(['%s'] * len(data))
    try:
        # 原有语句
        # insert_str = 'insert into table_name(id,name,description) values("{my_id}","this a name","tom");'.format(my_id=ran_str)
        # 可动态设置插入数据及数据类型
        insert_str = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
        cur.execute(insert_str, tuple(data.values()))
    except Exception as e:
        print("e:", e)
        conn.rollback()
    else:
        conn.commit()

    conn.close()