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

最新推荐文章于 2023-04-03 15:42:21 发布

æµ®æµªæ½®

最新推荐文章于 2023-04-03 15:42:21 发布

阅读量5.3k

点赞数 3

文章标签： mysql database sql python pandas

本文链接：https://blog.youkuaiyun.com/weixin_43482007/article/details/121491830

版权

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<arr.shape[1]-1):
            sql+=' END,'
        else:
            sql+=' END'
sql+=' WHERE id IN '+str(idList)

try:
    res = cursor.execute(sql)
    conn.commit()
    print(res)
except Exception as e:
    print(e)
    conn.rollback()
finally:
    conn.close()