批量更新mysql数据（万条数据秒完成）

2023-08-10 11:04| 来源: 网络整理| 查看: 265

1.常规方法，利用executemany批量更新数据（数据量小用这个就够了）

import pymysql import pandas as pd conn = pymysql.connect( host = '127.0.0.1', user = 'root', passwd = '123456', db = 'company_new', port=3306, charset = 'utf8' ) cursor = conn.cursor() io = r'C:\Users\admin\Downloads\修复后的数据.xlsx' arr=pd.read_excel(io, sheet_name = 0,usecols = 'A,AA,AI,AJ,AK,AL', nrows = 100,converters={'approved_time':pd.to_datetime,'operation_start_time':pd.to_datetime,'operation_end_time':pd.to_datetime}) order = ['approved_time', 'registry_address', 'business_scope', 'operation_start_time', 'operation_end_time','id'] arr = arr[order] update_data=np.array(arr).tolist() print('开始更新') sql = 'UPDATE x_credit_enterprise_to_fixed SET approved_time = (%s),registry_address=(%s),business_scope = (%s),operation_start_time=(%s),operation_end_time = (%s) WHERE id = (%s)' try: res = cursor.executemany(sql, update_data) print('res',res) conn.commit() except Exception as e: print(e) conn.rollback() finally: conn.close()

2.高效方法，通过python拼接sql语句，只需要执行一次sql。（一万条数据一两秒就能更新完）

sql语句

UPDATE my_table SET name = CASE id WHEN 1 THEN 'luck' WHEN 2 THEN 'zhang3' WHEN 3 THEN 'li4' END, age = CASE id WHEN 1 THEN 18 WHEN 2 THEN 28 WHEN 3 THEN 38 END WHERE id IN (1,2,3)

通过python拼接sql语句

import pymysql import pandas as pd conn = pymysql.connect( host = '127.0.0.1', user = 'root', passwd = '123456', db = 'company_new', port=3306, charset = 'utf8' ) cursor = conn.cursor() io = r'C:\Users\admin\Downloads\修复后的数据.xlsx' arr=pd.read_excel(io, sheet_name = 0,usecols = 'A,AA,AI,AJ,AK,AL', nrows = 1000,converters={'approved_time':pd.to_datetime,'operation_start_time':pd.to_datetime,'operation_end_time':pd.to_datetime}) sql='UPDATE x_credit_enterprise_to_fixed SET' idList=tuple(arr['id']) for l,item in enumerate(arr): if(not item=='id'): sql+=' '+item+'= CASE id' for index,el in enumerate(arr[item]): sql+=' WHEN '+str(arr["id"].loc[index].tolist())+' THEN \'%s\''%(str(el)) if(l

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章