python批量将excel维表导入mysql数据库

1、首先从本地读取excel维表，存到内存

number	name	score
1	Mary	98
2	Kem	95

import pandas as pd
import pymysql
import traceback

def get_excel():
	df = pd_excel(mytable.xlsx)
	df.column = ['name','score']
	# 主键 
	df['number] = [x for x in range(1,len(df) + 1) 
	# create表时主键为varchar型
	df['number'] = df['nmber'].apply(str) 
	
	return df  

2、连接数据库，导入维表

def insert_data(df,table_name)
# 创建连接
conn = pymysql.connect(host = db_host,user = db_user,password=db_password,db= db_name,port = db_port,use_unicode=True,charset = 'usf-8')
cursor = conn.cursor()

# 先删除数据
delete_sql = """delete from %s where number <= '10000000' """ % (table_name)
cursor.execute(delete_sql)
conn.commit()

#获取列名和值
keys = df.keys()
values = df.values.tolist()
key_sql = ','.join(keys)
value_sql = ','.join(['%s']*df.shape(1))

for t in values:
	insert_sql = """insert into %s (%s) values %s""" %(table_name,key_sql,value_sql)
	try:
		cursor.execute(insert_sql,[t[0],t[1],t[2]])
		print(str(table_name)+"导入成功")
	except:
		status_msg = traceback.format_exc()
		conn.rollback()
		print("报错信息如下："+str(status_msg))
# 统一提交
conn.commit()
# 断开连接
cursor.close()
conn.close()

if __name__ == "__main__":
	data_df = get_excel()
	insert_data(data_df,"score_table")