使用pymysql向服务器数据库里插入数据

from pymysql import connect
conn=connect(host='host',port=3306,database='database',user='user',password='password',charset='utf8')

cur = conn.cursor()

# 查询
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)


###删除数据
# del_countAll = "truncate table fa_food_list" 
# cur.execute(del_countAll)
# conn.commit()


####读取excel
import pandas as pd
import numpy as np
import re
import time

#keep_default_na空字符读取出来就不是nan了，header=None第一行也读取 不作为表头
df = pd.read_excel('食物成分表完整版.xlsx',keep_default_na=False)


#.values将DataFrame转为numpy  .tolist() 将numpy转为list
data=df.values[:,1:].tolist()
classify=df.values[:,0].tolist()


res_classify=[re.sub("['（','）',' ']",'',x) for x in classify]


###将data里的特殊字符换成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 插入语句
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) # 执行sql语句
    except:
       conn.rollback()# 发生错误时回滚
       print(x)

time.sleep(1)
conn.commit()
cur.close()
conn.close()