python批量更新mysql_Python操作MySQL批量修改数据：cursor.executemany()

使用pymysql的 course.executemany(sql, update_list) 进行批量更新

sql：更新一条的sql语句模板；

update_list：一个列表套元组的结构；

示例：

db = pymysql.connect(user='root', password='mysql', database='test', host='127.0.0.1', port=3306, charset='utf8mb4')

name_list = ["re", "gh", "ds", "D"] # 存储name的值

age_list = ["10", "20", "30", "40"] # 存储age的值

id_list = ["1", "2", "3", "4"] # 存储id的值

val_list = [[name_list[i], age_list[i], id_list[i]] for i in range(len(id_list))]

print(val_list)

# [['re', '10', '1'], ['gh', '20', '2'], ['ds', '30', '3'], ['D', '40', '4']]

with db.cursor() as cursor:

try:

sql = "UPDATE test SET name=(%s), age=(%s) WHERE id=(%s)"

cursor.executemany(sql, val_list)

db.commit()

except:

db.rollback()

db.close()

相关文章