本文链接:https://blog.csdn.net/Mr__lqy/article/details/85719603
1. 连接mysql
importpymysql
db= pymysql.connect(host=‘localhost‘, user=‘root‘, password=‘123456‘, port=3306, db=‘spiders‘)
cursor=db.cursor()
sql= ‘select * from students;‘cursor.execute(sql)
cursor.close()
db.close()
2. 多字段动态插入mysql数据库中
importpymysql
db= pymysql.connect(host=‘localhost‘,user=‘root‘, password=‘123456‘, port=3306, db=‘spiders‘)
data={‘id‘: ‘20180606‘,‘name‘: ‘Lily‘,‘age‘: 20}
table= ‘students‘keys= ‘,‘.join(data.keys())
values= ‘,‘.join([‘%s‘] *len(data))
sql= ‘INSERT INTO {table}({keys}) VALUES ({values})‘.format(table=table, keys=keys, values=values)try:
cursor.execute(sql, tuple(data.values()))print(‘Successful‘)
db.commit()except:print(‘Failed‘)
db.rollback()
cursor.close()
db.close()
3. 数据更新插入mysql数据库中
importpymysql
db= pymysql.connect(host=‘localhost‘,user=‘root‘, password=‘123456‘, port=3306, db=‘spiders‘)
data={‘id‘: ‘20180606‘,‘name‘: ‘Lily‘,‘age‘: 25}
table= ‘students‘keys= ‘,‘.join(data.keys())
values= ‘,‘.join([‘%s‘] *len(data))
sql= ‘INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE‘.format(table=table, keys=keys, values=values)
update= ‘,‘.join(["{key} = %s".format(key=key) for key indata])
sql+=updatetry:
cursor.execute(sql, tuple(data.values())*2)print(‘Successful‘)
db.commit()except:print(‘Failed‘)
db.rollback()
cursor.close()
db.close()
原文:https://www.cnblogs.com/hzjdpawn/p/11618443.html