使用python进行自动化打分

数据挖掘研讨课结束了&＃xff0c;这门课的考核方法是每个同学根据班里面同学的课堂表现打分&＃xff0c;然后老师再取截断平均值作为最后的分数。

于是我就想&＃xff0c;能否用python进行自动化打分呢&＃xff0c;答案是肯定的。

老师发给我们的课堂发言记录是pdf格式&＃xff0c;首先我在网上下载了迅捷pdf转word工具&＃xff0c;由于这个不付费只能一次转5页&＃xff0c;所以我又下载了迅捷pdf编辑器&＃xff0c;一次删除5页图片&＃xff0c;最后将数据汇总到txt中。

通过观察可以很容易发现&＃xff0c;记录中记录同学信息的格式是&＃xff08;xxx&＃xff0c; xxx&＃xff0c;xxx&＃xff09;&＃xff0c;于是可以使用python将所有同学信息提取出来&＃xff0c;并且统计每个同学的回答频率&＃xff0c;最后根据回答频率给出分数。

打分表是一张xlsx格式的表格&＃xff0c;那里面详细记录了同学学号&＃xff0c;姓名和班级信息&＃xff0c;将上述过程计算得到的分数和信息录入数据库&＃xff0c;然后再根据表格的学号在数据库中查询成绩&＃xff0c;再录入表格中&＃xff0c;这样&＃xff0c;就大功告成啦。

python使用到的库&＃xff1a;

xlrd xls读取库

xlwt xls写入库

pymysql python操作mysql库

xlutils 表格复制库

具体代码如下&＃xff1a;

#coding:utf-8import xlrd
import xlwt
from xlutils.copy import copy
import pymysql.cursors#提取出&＃xff08;&＃xff09;的内容
def extract():with open("学生记录.txt","w") as data:with open("数据挖掘课程记录.txt", "r") as file:lines &＃61; file.readlines() #读取每一行for line in lines: #从每一行中读取出&＃xff08;&＃xff09;内容if(line.find("&＃xff08;") !&＃61; -1):tuple &＃61; line.split("&＃xff08;")[1].split("&＃xff09;")[0]data.write(tuple) #些许数据文件data.write("\n")#对提取的数据进行数据清洗
#返回回答频率字典name
def cleanout():with open("学生记录.txt","r") as data:#统计每个同学出现次数name &＃61; {}lines &＃61; data.readlines()for line in lines:print(line)if(line.find("&＃xff0c;") !&＃61; -1):st &＃61; line.split("&＃xff0c;")[1]print(st)if st in name: #如果已经记录了这个名字就将频率加一print("在")name[st] &＃61; name[st] &＃43; 1else: #否则就加入字典print("不在")name[st] &＃61; 1print(name)#接着写入成绩文件return name#将数据录入数据库中
def entering(name &＃61; {}):#连接数据库connection &＃61; pymysql.connect(host &＃61; "localhost",user &＃61; "root",password &＃61; "root",db &＃61; "mark")data &＃61; []try:with connection.cursor() as cur:with open("学生记录.txt","r") as file:lines &＃61; file.readlines()for line in lines:if(line.find("&＃xff0c;") !&＃61; -1):data &＃61; []data.append(line.split("&＃xff0c;")[0])data.append(line.split("&＃xff0c;")[1])data.append(line.split("&＃xff0c;")[2])num &＃61; name[data[1]]print(name[data[1]])if(num &＃61;&＃61; 1):grad &＃61; 94elif(num &＃61;&＃61; 2):grad &＃61; 96elif(num &＃61;&＃61; 3):grad &＃61; 98elif(num &＃61;&＃61; 4):grad &＃61; 100print(grad)print(data[0])print(data[1])print(data[2])id &＃61; int(data[0])sel &＃61; "SELECT * FROM grade WHERE id &＃61; %s"sul &＃61; cur.execute(sel, id)if(sul &＃61;&＃61; 0):sql &＃61; "INSERT INTO grade values(%s,%s,%s,%s)"cur.execute(sql, (id,data[1],data[2],grad))connection.commit() #提交事务finally:connection.close()#接着按照数据库的成绩写入excel表格中
def wtexcel():connection &＃61; pymysql.connect(host &＃61; "localhost",user &＃61; "root",password &＃61; "root",db &＃61; "mark")try:with connection.cursor() as cur:file &＃61; xlrd.open_workbook("2018秋打分表weiweihit&＃64;163.com.xlsx")table &＃61; file.sheets()[0]nrows &＃61; table.nrowswb &＃61; copy(file)sheet &＃61; wb.get_sheet(0)for row in range(1, nrows):#print(row)id &＃61; int(table.cell(row, 1).value)sel &＃61; "SELECT grade FROM grade WHERE id &＃61; %s"sul &＃61; cur.execute(sel, id)grad &＃61; cur.fetchone()if(sul &＃61;&＃61; 0): #没有记录就是75分sheet.write(row, 4, 75)else:sheet.write(row, 4, grad[0])wb.save("打分表.xls")finally:connection.close()if __name__&＃61;&＃61;"__main__":#extract()#cleanout()#name &＃61; {}#name &＃61; cleanout()#entering(name)wtexcel()