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

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

 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:UsersadminDownloads修复后的数据.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:UsersadminDownloads修复后的数据.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