def query(self): self.tbStudent.setRowCount(0) # 清空表格中的所有行 gname = self.cboxGrade.currentText() # 记录选择的年级 cname = self.cboxClass.currentText() # 记录选择的班级 # 获取所有学生信息 if gname == "所有": result = service.query("select stuID,stuName,CONCAT(gradeName,className),sex,age,address,phone from v_studentinfo") # 获取指定年级学生信息 elif gname != "所有" and cname == "所有": result = service.query( "select stuID,stuName,CONCAT(gradeName,className),sex,age,address,phone from v_studentinfo where gradeName=%s", gname) # 获取指定年级指定班的学生信息 elif gname != "所有" and cname != "所有": result = service.query( "select stuID,stuName,CONCAT(gradeName,className),sex,age,address,phone from v_studentinfo where gradeName=%s and className=%s", gname, cname) row = len(result) # 取得记录个数,用于设置表格的行数 self.tbStudent.setRowCount(row) # 设置表格行数 self.tbStudent.setColumnCount(7) # 设置表格列数 # 设置表格的标题名称 self.tbStudent.setHorizontalHeaderLabels(['学生编号', '学生姓名', '班级', '性别', '年龄', '家庭地址', '联系电话']) for i in range(row): # 遍历行 for j in range(self.tbStudent.columnCount()): # 遍历列 data = QTableWidgetItem(str(result[i][j])) # 转换后可插入表格 self.tbStudent.setItem(i, j, data) # 设置每个单元格的数据
def add(self): stuID = self.editID.text() # 记录学生编号 stuName = self.editName.text() # 记录学生姓名 age = self.editAge.text() # 记录年龄 sex = self.cboxSex.currentText() # 记录性别 phone = self.editPhone.text() # 记录电话 address = self.editAddress.text() # 记录地址 if self.cboxGrade.currentText() != "" and self.cboxGrade.currentText() != "所有": # 如果选择了年级 # 获取年级对应的ID result = service.query("select gradeID from tb_grade where gradeName=%s", self.cboxGrade.currentText()) if len(result) > 0: # 如果结果大于0 gradeID = result[0] # 记录选择的年级对应的ID if self.cboxClass.currentText() != "" and self.cboxClass.currentText() != "所有": # 如果选择了班级 # 获取班级对应的ID result = service.query("select classID from tb_class where gradeID=%s and className=%s", gradeID, self.cboxClass.currentText()) if len(result) > 0: # 如果结果大于0 classID = result[0] # 记录选择的班级对应的ID if stuID != "" and stuName != "": # 判学生编号和学生姓名不为空 if self.getName(stuID) > 0: # 判断已经存在该记录 self.editID.setText("") # 清空学生编号文本框 QMessageBox.information(None, '提示', '您要添加的学生编号已经存在,请重新输入!', QMessageBox.Ok) else: # 执行添加语句 result = service.exec( "insert into tb_student(stuID,stuName,classID,gradeID,age,sex,phone,address) values (%s,%s,%s,%s,%s,%s,%s,%s)", (stuID, stuName, classID, gradeID, age, sex, phone, address)) if result > 0: # 如果结果大于0,说明添加成功 self.query() # 在表格中显示最新数据 QMessageBox.information(None, '提示', '信息添加成功!', QMessageBox.Ok) else: QMessageBox.warning(None, '警告', '请输入数据后,再执行相关操作!', QMessageBox.Ok) else: QMessageBox.warning(None, '警告', '请先添加年级!', QMessageBox.Ok)
def bindCbox(self): self.cboxKinds.addItem("所有") # 设置考试类别的默认首选项 result = service.query( "select kindName from tb_examkinds") # 从考试类别中查询数据 for i in result: # 遍历查询结果 self.cboxKinds.addItem(i[0]) # 在下拉列表中显示考试类别 self.cboxSubject.addItem("所有") # 设置考试科目的默认首选项 result = service.query("select subName from tb_subject") # 从考试科目中查询数据 for i in result: # 遍历查询结果 self.cboxSubject.addItem(i[0]) # 在下拉列表中显示考试科目
def add(self): subname = self.cboxSubject.currentText() # 记录考试科目 kindname = self.cboxKinds.currentText() # 记录考试类别 gradename = self.cboxGrade.currentText() # 记录年级 classname = self.cboxClass.currentText() # 记录班级 stuname = self.cboxStuName.currentText() # 记录学生姓名 score = self.editResult.text() # 记录输入的分数 if kindname != "所有": # 如果选择了考试类别 # 获取选择的考试类别对应ID result = service.query( "select kindID from tb_examkinds where kindName=%s", kindname) if len(result) > 0: kindID = result[0] if subname != "所有": # 如果选择了考试科目 # 获取选择的考试科目对应ID result = service.query( "select subID from tb_subject where subName=%s", subname) if len(result) > 0: subID = result[0] if stuname != "": # 如果选择了学生姓名 # 获取学生对应的ID result = service.query( "select stuID from v_studentinfo where gradeName=%s and className=%s and stuName=%s", gradename, classname, stuname) if len(result) > 0: # 如果结果大于0 stuID = result[0] # 记录选择的学生对应的ID if self.getScore(stuID, kindID, subID) <= 0: # 判断是否已经存在相同记录 if score != "": # 如果输入了分数 # 执行添加语句 result = service.exec( "insert into tb_result(stuID,kindID,subID,result) values (%s,%s,%s,%s)", (stuID, kindID, subID, score)) if result > 0: # 如果结果大于0,说明添加成功 self.query() # 在表格中显示最新数据 QMessageBox.information( None, '提示', '信息添加成功!', QMessageBox.Ok) else: QMessageBox.warning( None, '警告', '请输入分数!', QMessageBox.Ok) else: QMessageBox.warning( None, '警告', '该学生成绩记录已经存在,请核查!', QMessageBox.Ok) else: QMessageBox.warning(None, '警告', '请先选择学生!', QMessageBox.Ok) else: QMessageBox.warning(None, '警告', '请先选择考试科目!', QMessageBox.Ok) else: QMessageBox.warning(None, '警告', '请先选择考试类别!', QMessageBox.Ok)
def query(self): self.tbResult.setRowCount(0) # 清空表格中的所有行 stuname = self.editName.text() # 记录查询的学生姓名 kindname = self.cboxKinds.currentText() # 记录选择的考试类别 subname = self.cboxSubject.currentText() # 记录选择的考试科目 if stuname == "": if kindname == "所有": if subname == "所有": # 查询所有成绩信息 result = service.query( "select stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo" ) else: # 根据考试科目查询成绩信息 result = service.query( "select stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where subName=%s", subname) else: # 根据考试类别查询成绩信息 result = service.query( "select stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where kindName=%s", kindname) else: if kindname == "所有": if subname == "所有": # 根据学生姓名查询成绩信息 result = service.query2( "select stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where stuName like '%" + stuname + "%'") else: # 根据学生姓名和考试科目查询成绩信息 result = service.query2( "select stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where stuName like '%" + stuname + "%' and subName='" + subname + "'") else: # 根据学生姓名、考试科目和考试类别查询成绩信息 result = service.query2( "select stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where stuName like '%" + stuname + "%' and subName='" + subname + "' and kindName='" + kindname + "'") row = len(result) # 取得记录个数,用于设置表格的行数 self.tbResult.setRowCount(row) # 设置表格行数 self.tbResult.setColumnCount(6) # 设置表格列数 # 设置表格的标题名称 self.tbResult.setHorizontalHeaderLabels( ['学生编号', '学生姓名', '班级', '科目', '种类', '成绩']) for i in range(row): # 遍历行 for j in range(self.tbResult.columnCount()): # 遍历列 data = QTableWidgetItem(str(result[i][j])) # 转换后可插入表格 self.tbResult.setItem(i, j, data) # 设置每个单元格的数据
def query(self): self.tbStudent.setRowCount(0) # 清空表格中的所有行 # 获取所有学生信息 if self.editKey.text() == "": result = service.query( "select stuID,stuName,CONCAT(gradeName,className),sex,age,address,phone from v_studentinfo" ) else: key = self.editKey.text() # 记录查询关键字 # 根据学生编号查询信息 if self.cboxCondition.currentText() == "学生编号": sql = "select stuID,stuName,CONCAT(gradeName,className),sex,age,address,phone from v_studentinfo where stuID like '%" + key + "%'" result = service.query2(sql) # 根据学生姓名查询信息 elif self.cboxCondition.currentText() == "学生姓名": sql = "select stuID,stuName,CONCAT(gradeName,className),sex,age,address,phone from v_studentinfo where stuName like '%" + key + "%'" result = service.query2(sql) row = len(result) # 取得记录个数,用于设置表格的行数 self.tbStudent.setRowCount(row) # 设置表格行数 self.tbStudent.setColumnCount(7) # 设置表格列数 # 设置表格的标题名称 self.tbStudent.setHorizontalHeaderLabels( ['学生编号', '学生姓名', '班级', '性别', '年龄', '家庭地址', '联系电话']) for i in range(row): # 遍历行 for j in range(self.tbStudent.columnCount()): # 遍历列 data = QTableWidgetItem(str(result[i][j])) # 转换后可插入表格 self.tbStudent.setItem(i, j, data) # 设置每个单元格的数据
def bindClass(self): self.cboxClass.clear() # 清空列表 self.cboxClass.addItem("所有") # 增加首选项 result = service.query("select className from v_classinfo where gradeName=%s", self.cboxGrade.currentText()) # 从年级视图中查询数据 for i in result: # 遍历查询结果 self.cboxClass.addItem(i[0]) # 在下拉列表中显示班级
def bindStuName(self): self.cboxStuName.clear() # 清空列表 result = service.query( "select stuName from v_studentinfo where gradeName=%s and className=%s", self.cboxGrade.currentText(), self.cboxClass.currentText()) # 从学生信息视图中查询数据 for i in result: # 遍历查询结果 self.cboxStuName.addItem(i[0]) # 在下拉列表中显示指定班的学生姓名
def getItem(self, item): if item.column() == 0: # 如果单击的是第一列 self.select = item.text() # 获取单击的单元格文本 self.editID.setText(self.select) # 显示在学生编号文本框中 # 根据学生编号查询学生信息 result = service.query("select * from v_studentinfo where stuID=%s",item.text()) self.editName.setText(result[0][1]) # 显示学生姓名 self.editAge.setText(str(result[0][2])) # 显示年龄 self.editPhone.setText(result[0][4]) # 显示电话 self.editAddress.setText(result[0][5]) # 显示地址 self.cboxSex.setCurrentText(result[0][3]) # 显示性别
def query(self): self.tbClass.setRowCount(0) # 清空表格中的所有行 result = service.query("select classID,gradeName,className from v_classinfo") # 调用服务类中的公共方法执行查询语句 row = len(result) # 取得记录个数,用于设置表格的行数 self.tbClass.setRowCount(row) # 设置表格行数 self.tbClass.setColumnCount(3) # 设置表格列数 # 设置表格的标题名称 self.tbClass.setHorizontalHeaderLabels(['班级编号', '所属年级','班级名称']) for i in range(row): # 遍历行 for j in range(self.tbClass.columnCount()): # 遍历列 data = QTableWidgetItem(str(result[i][j])) # 转换后可插入表格 self.tbClass.setItem(i, j, data) # 设置每个单元格的数据
def query(self): self.tbSubject.setRowCount(0) # 清空表格中的所有行 result = service.query("select * from tb_subject") # 调用服务类中的公共方法执行查询语句 row = len(result) # 取得记录个数,用于设置表格的行数 self.tbSubject.setRowCount(row) # 设置表格行数 self.tbSubject.setColumnCount(2) # 设置表格列数 # 设置表格的标题名称 self.tbSubject.setHorizontalHeaderLabels(['科目编号', '科目名称']) for i in range(row): # 遍历行 for j in range(self.tbSubject.columnCount()): # 遍历列 data = QTableWidgetItem(str(result[i][j])) # 转换后可插入表格 self.tbSubject.setItem(i, j, data) # 设置每个单元格的数据
def getItem(self, item): if item.column() == 0: # 如果单击的是第一列 self.select = item.text() # 获取单击的单元格文本 # 根据编号查询成绩信息 result = service.query("select * from v_resultinfo where ID=%s", self.select) self.cboxKinds.setCurrentText(result[0][3]) # 显示考试类别 self.cboxGrade.setCurrentText(result[0][6]) # 显示年级 self.cboxClass.setCurrentText(result[0][5]) # 显示班级 self.cboxStuName.setCurrentText(result[0][2]) # 显示学生姓名 self.cboxSubject.setCurrentText(result[0][4]) # 显示考试科目 self.editResult.setText(str(result[0][7])) # 显示学生分数
def openMain(self): service.userName = self.editName.text() # 全局变量,记录用户名 self.userPwd = self.editPwd.text() # 记录用户密码 if service.userName != "" and self.userPwd != "": # 判断用户名和密码不为空 # 根据用户名和密码查询数据 result = service.query( "select * from tb_user where userName = %s and userPwd = %s", service.userName, self.userPwd) if len(result) > 0: # 如果查询结果大于0,说明存在该用户,可以登录 self.m = main.Ui_MainWindow() # 创建主窗体对象 self.m.show() # 显示主窗体 MainWindow.hide() # 隐藏当前的登录窗体 else: self.editName.setText("") # 清空用户名文本 self.editPwd.setText("") # 清空密码文本框 QMessageBox.warning(None, '警告', '请输入正确的用户名和密码!', QMessageBox.Ok) else: QMessageBox.warning(None, '警告', '请输入用户名和密码!', QMessageBox.Ok)
def add(self): classID = self.editID.text() # 记录输入的班级编号 className = self.editName.text() # 记录输入的班级名称 if self.cboxGrade.currentText() !="": # 如果选择了年级 # 获取年级对应的ID result=service.query("select gradeID from tb_grade where gradeName=%s",self.cboxGrade.currentText()) if len(result)>0: # 如果结果大于0 gradeID=result[0] # 记录选择的年级对应的ID if classID != "" and className != "": # 判断班级编号和班级名称不为空 if self.getName(gradeID,className) > 0: # 判断已经存在该记录 self.editName.setText("") # 清空班级文本框 QMessageBox.information(None, '提示', '您要添加的班级已经存在,请重新输入!', QMessageBox.Ok) else: # 执行添加语句 result = service.exec("insert into tb_class(classID,gradeID,className) values (%s,%s,%s)", (classID, gradeID,className)) if result > 0: # 如果结果大于0,说明添加成功 self.query() # 在表格中显示最新数据 QMessageBox.information(None, '提示', '信息添加成功!', QMessageBox.Ok) else: QMessageBox.warning(None, '警告', '请输入数据后,再执行相关操作!', QMessageBox.Ok) else: QMessageBox.warning(None, '警告', '请先添加年级!', QMessageBox.Ok)
def edit(self): try: if self.select != "": # 判断是否选择了要修改的数据 className = self.editName.text() # 记录修改的班级名称 if self.cboxGrade.currentText() != "": # 如果选择了年级 # 获取年级对应的ID result = service.query("select gradeID from tb_grade where gradeName=%s", self.cboxGrade.currentText()) if len(result) > 0: # 如果结果大于0 gradeID = result[0] # 记录选择的年级对应的ID if className != "": # 判断班级名称不为空 if self.getName(gradeID,className) > 0: # 判断已经存在该记录 self.editName.setText("") # 清空班级文本框 QMessageBox.information(None, '提示', '您要修改的班级已经存在,请重新输入!', QMessageBox.Ok) else: # 执行修改操作 result = service.exec("update tb_class set gradeID=%s , className= %s where classID=%s", (gradeID,className, self.select)) if result > 0: # 如果结果大于0,说明修改成功 self.query() # 在表格中显示最新数据 QMessageBox.information(None, '提示', '信息修改成功!', QMessageBox.Ok) except: QMessageBox.warning(None, '警告', '请先选择要修改的数据!', QMessageBox.Ok)
def getName(self, name): result = service.query("select * from tb_subject where subName = %s", name) return len(result)
def query(self): self.tbResult.setRowCount(0) # 清空表格中的所有行 kindname = self.cboxKinds.currentText() # 记录选择的考试类别 gradename = self.cboxGrade.currentText() # 记录选择的年级 classname = self.cboxClass.currentText() # 记录选择的班级 if kindname == "所有": if gradename == "所有": if classname == "所有" or classname == "": # 获取所有学生的成绩信息 result = service.query( "select ID,stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo" ) else: # 获取指定班级的成绩信息 result = service.query( "select ID,stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where className=%s", classname) else: if classname == "所有": # 获取指定年级的成绩信息 result = service.query( "select ID,stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where gradeName=%s", gradename) else: # 获取指定年级指定班的成绩信息 result = service.query( "select ID,stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where gradeName=%s and className=%s", gradename, classname) else: if gradename == "所有": if classname == "所有" or classname == "": # 获取指定考试类别的所有学生成绩信息 result = service.query( "select ID,stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where kindName=%s", kindname) else: # 获取指定考试类别的指定班级的成绩信息 result = service.query( "select ID,stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where kindName=%s and className=%s", kindname, classname) else: if classname == "所有": # 获取指定考试类别的指定年级的成绩信息 result = service.query( "select ID,stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where kindName=%s and gradeName=%s", kindname, gradename) else: # 获取指定考试类别的指定年级指定班的成绩信息 result = service.query( "select ID,stuID,stuName,CONCAT(gradeName,className),subName,kindName,result from v_resultinfo where kindName=%s and gradeName=%s and className=%s", kindname, gradename, classname) row = len(result) # 取得记录个数,用于设置表格的行数 self.tbResult.setRowCount(row) # 设置表格行数 self.tbResult.setColumnCount(7) # 设置表格列数 # 设置表格的标题名称 self.tbResult.setHorizontalHeaderLabels( ['编号', '学生编号', '学生姓名', '班级', '科目', '种类', '成绩']) for i in range(row): # 遍历行 for j in range(self.tbResult.columnCount()): # 遍历列 data = QTableWidgetItem(str(result[i][j])) # 转换后可插入表格 self.tbResult.setItem(i, j, data) # 设置每个单元格的数据
def getScore(self, sid, kindid, subid): # 根据年级编号和班级名查询数据 result = service.query( "select * from tb_result where stuID =%s and kindID=%s and subID=%s", sid, kindid, subid) return len(result) # 返回查询结果的记录
def getName(self, sid): # 根据年级编号和班级名查询数据 result = service.query("select * from tb_student where stuID =%s", sid) return len(result) # 返回查询结果的记录
def getName(self, cid,name): # 根据年级编号和班级名查询数据 result = service.query("select * from tb_class where gradeID =%s and className = %s", cid,name) return len(result) # 返回查询结果的记录
def bindGrade(self): self.cboxGrade.addItem("所有") result = service.query("select gradeName from tb_grade") # 从年级表中查询数据 for i in result: # 遍历查询结果 self.cboxGrade.addItem(i[0]) # 在下拉列表中显示年级
def getName(self, name): result = service.query( "select * from tb_examkinds where kindName = %s", name) return len(result)
def getName(self, name): result = service.query("select * from tb_grade where gradeName = %s", name) return len(result)