from xlrd import open_workbook
import sqlite3
# 测试数据系为某人的高考志愿填报表# 从Excel表格中读入数据, 并写入dataList中进行存储
book = open_workbook(r"D:\系统默认\桌面\Student.xls", encoding_override="utf-8")
sheet_name = book.sheet_names()[0]
sheet = book.sheet_by_name(sheet_name)
dataList =[]for i inrange(1, sheet.nrows):
data =[]for j inrange(0, sheet.ncols):
data.append(str(sheet.cell(i, j).value))# 以字符串的形式写入
dataList.append(data)# for i in range(0, len(dataList)): # 打印datalist中的内容# print(dataList[i])# 新建表格
con = sqlite3.connect(r"D:\CodeLibrary\spider\database\Student.db")
cursor = con.cursor()# 获取游标
sql ='''
create table if not exists Student
(
id integer primary key autoincrement,
序号 integer not null,
专业 text not null,
院校 text not null,
计划数 integer not null,
位次 integer not null,
录取分 real not null,
选科要求 text not null,
代号 text not null
);
'''
cursor.execute(sql)
con.commit()'''
大部分是借鉴网课资料, 网课链接: https://www.bilibili.com/video/BV12E411A7ZQ?spm_id_from=..search-card.all.click&vd_source=d0f880a66be8775389e528d9ca528d05
'''for data in dataList:for index inrange(len(data)):if index ==0or index ==3or index ==4or index ==5:continue
data[index]='"'+ data[index]+'"'
sql2 ='''
insert into Student(
序号, 专业, 院校, 计划数, 位次, 录取分, 选科要求, 代号)
values(%s)'''%",".join(data)
cursor.execute(sql2)
con.commit()
cursor.close()
con.close()