a.xls文件如下:
import pandas as pd
from sqlalchemy import create_engine
#下面这个链接是我的阿里云服务器上的mysql
engine = create_engine('mysql+mysqlconnector://XXXX:http://XXXX@47.106.xxx.xxx:3306/test?auth_plugin=mysql_native_password', echo=False)
data = pd.read_excel('a.xls')
print(list(data))
#输出['博学1202教室课程表', 'Unnamed: 1', 'Unnamed: 2', 'Unnamed: 3', 'Unnamed: 4', 'Unnamed: 5', 'Unnamed: 6', 'Unnamed: 7', 'Unnamed: 8']
classRoom = list(data)[0][:-5]
targetDf= pd.DataFrame([],columns=["class_room_number","class_time","subject","week"])
subjectDict = {}
columns = data.columns.tolist()
for i in range(2,len(columns)):
for j in range(2,len(data)-1):
subjectDict = {"class_room_number":classRoom,"class_time":data[columns[1]][j],
"subject":data[columns[i]][j],"week":data[columns[j]][1]}
print(subjectDict)
targetDf.loc[len(targetDf)]=subjectDict
targetDf.to_sql(name='class_room', con=engine, if_exists = 'append', index=False,chunksize=10)