移动校园（3）：处理全校课程数据excel文档，实现空闲教室查询与课程表查询

首先打开教学平台

然后导出为excel文档

 

import mathimport pandas as pd
import pymssql
serverName = '127.0.0.1'
userName = 'sa'
passWord = '123456'
database='uniSchool'
conn = pymssql.connect(server=serverName,user=userName,password=passWord,database=database)
cursor = conn.cursor()
cursor.execute('CREATE TABLE courses ( id varchar(50), name varchar(100),No varchar(100),department varchar(100),studyhours varchar(20),credit varchar(20),teachername varchar(100),classname varchar(800),timeandlocation varchar(200),campus varchar(30))')
conn.commit()
df = pd.read_excel('全校课程表.xlsx')for r_index in df.index:print(f"第{r_index}次")each=df.iloc[r_index].to_dict()insert = "insert into courses (id,name,No,department,studyhours,credit,teachername,classname,timeandlocation,campus) values (%s, %s, %s, %s, %s,%s,%s,%s,%s,%s)"if math.isnan(each["学时"]):each["学时"]=""for col in [ "上课班级", "已排时间地点","上课教师"]:value = each[col]if not isinstance(value, str):each[col]=""da = (each["课程号"], each["课程名"],  each["课序号"],  each["开课单位"],  each["学时"],each["学分"],each["上课教师"],each["上课班级"],each["已排时间地点"],each["学校校区"])cursor.execute(insert, da)conn.commit()
cursor.close()
conn.close()

至于筛选条件为什么是这些，你试试就知道了，我刚开始各种报错，然后一个个改好的，对应的长度也是，报错不断，

最后结果如图