import pymysql
#链接数据库
conn=pymysql.connect(host='127.0.0.1',
user='***',
passwd='***',
db='***',
port=3306,charset='utf8')
#批量update函数
def Item_Clean_Spu(item_combine_spu_re):
item_clean_spu_id=item_combine_spu_re[['id','spu_id']]
item_clean_spu_id=item_clean_spu_id[item_clean_spu_id['spu_id'].notnull()]
item_clean_spu_id=item_clean_spu_id.reset_index(drop=True)
for i in np.arange(len(item_clean_spu_id)+2)[1:-1]:
sql="""
update item_clean ic set ic.spu_id = {0}
where ic.item_id={1} and ic.spu_id IS NULL
""".format(item_clean_spu_id[:i]['spu_id'].astype(str).tolist()[(i-1)],item_clean_spu_id[:i]['id'].astype(str).tolist()[(i-1)])
conn.ping(reconnect=True)
sqled= conn.query(sql)
conn.commit()
采取了低效的for循环,有更好的方式欢迎告知,感谢~~
还发现一个小问题,一个报错:1205 - Lock wait timeout exceeded; try restarting transaction,结果是同事在改动表hiahiahia,我还傻乎乎找了半天搞了半天数据库,kill一堆线程,还是要多多学习鸭~