• notice
  • Congratulations on the launch of the Sought Tech site

Insert data into server database using pymysql

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()


Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+