mysql数据表结构如下:
利用pandas处理后的数据格式如下:
方法一:
with self.tikuengine1.connect() as conn: result.to_sql('t_exam', con=conn, if_exists='replace', chunksize=3000)
速度较快,但是每次时追加或者替换,且不能设置主键(待验证)
方法二:
result.apply(apply_to_db, axis=1) def apply_to_db(df): user_id=df['user_id'] pre_score=df['pre_score'] status=df['status'] item_id=df['item_id'] with self.tikuengine1.connect() as conn: sql=f''' replace into `t_exam_user` (user_id,pre_score,status,item_id) values ('{user_id}','{pre_score}','{status}','{item_id}') ''' res=conn.execute(sql)
有数据则替换没有则添加,需要设置唯一索引,速度慢的一笔
方法三:
with self.tikuengine1.connect() as conn:
res=zip(df['user_id'],df['pre_score'],df['status'],df['item_id'])
sql='''
replace into `t_exam_user` (user_id,pre_score,status,item_id) values
'''
for i in res:
# print(i)
sql=sql+f'''{i}''' +','
# print(sql)
# print(sql)
sql=sql.strip(',')
print(sql)
res = conn.execute(sql)
瞬间插入,写完都觉得自己棒棒哒、
方法四:
利用on duplicated key 具体待更新