Insert data into server database using pymysql
Technical otaku
2022-09-19
from pymysql import connect
conn=connect(host='host',port=3306,database='database',user='user',password='password',charset='utf8')
cur = conn.cursor()
# Inquire
sql_countAll = "select id,name from fa_food_type;"
cur.execute(sql_countAll)
countAll = cur.fetchall()
type_dict={x[1]:x[0] for x in countAll}
# print(type_dict)
###delete data
# del_countAll = "truncate table fa_food_list"
# cur.execute(del_countAll)
# conn.commit()
#### read excel
import pandas as pd
import numpy as np
import re
import time
#keep_default_na The empty character is not nan when read, and the first line of header=None is also read, not as a header
df = pd.read_excel('Food ingredient list full version.xlsx',keep_default_na=False)
#.values Convert DataFrame to numpy .tolist() Convert numpy to list
data=df.values[:,1:].tolist()
classify=df.values[:,0].tolist()
res_classify=[re.sub("['(',')',' ']",'',x) for x in classify]
###Replace special characters in data with 0
for index,value in enumerate(data):
for i,v in enumerate(value[1:]):
judge_v=re.findall('\d',str(v))
if len(judge_v)==0:
# print(data[index][i+1],v)
data[index][i+1]=0
# print(res_classify)
## SQL insert statement
for x in range(len(data[1:])):
name=data[x][0]
type_id=int(type_dict[res_classify[x]])
hot=float(data[x][1])
dietary=float(data[x][2])
protein=float(data[x][3])
fat=float(data[x][4])
water=float(data[x][5])
sql = "INSERT INTO fa_food_list(name,type_id,hot,dietary,protein,fat,water,weigh,is_show) VALUES('{}',{},{},{},{},{},{}, 0,1)".format(name,type_id,hot,dietary,protein,fat,water,0,'1')
try:
print(sql)
cur.execute(sql) # execute sql statement
except:
conn.rollback()# rollback when an error occurs
print(x)
time.sleep(1)
conn.commit()
cur.close()
conn.close()
0 Comments