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