Слияние кода завершено, страница обновится автоматически
import sys
import random as r
import xlwt
import datetime
from PyQt5.QtCore import Qt
from PyQt5 import QtCore, QtSql
from PyQt5.QtSql import QSqlTableModel, QSqlRelationalTableModel, QSqlQueryModel
from PyQt5.QtSql import QSqlDatabase, QSqlQuery
from PyQt5.QtWidgets import QMessageBox, QMainWindow, QApplication
from 登录 import Ui_loginForm
from 主界面 import Ui_MainWindow
from 显示 import Ui_Form
from 注册 import Ui_addno
from 修改密码 import Ui_modifyForm
from 查询 import Ui_queryForm
from 教师端 import Ui_teacherForm
Tab = QSqlRelationalTableModel()
Sno = "0"
S_id = "0"
class addnoWindow(QMainWindow, Ui_addno):
def __init__(self):
super(addnoWindow, self).__init__()
self.setupUi(self)
self.pushButton.clicked.connect(Win.add)
self.pushButton_2.clicked.connect(self.addclose)
def addclose(self):
self.close()
Win.show()
class teacherWindow(QMainWindow, Ui_teacherForm):
def __init__(self):
super(teacherWindow, self).__init__()
self.setupUi(self)
Win.pushButton.clicked.connect(self.S_sctView)
self.pushButton_2.clicked.connect(self.teacherWinClose)
self.pushButton.clicked.connect(self.T_modifysct)
self.pushButton_3.clicked.connect(lambda: modify.show())
self.pushButton_4.clicked.connect(self.T_sctxls)
def teacherWinClose(self):
self.close()
Win.show()
def T_sctxls(self):
rows = self.tableView.model().rowCount()
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('学生信息表')
str = ['学号','姓名','性别','系别','教师','课程号','成绩']
for a in range(7):
sheet.write(0, a, str[a])
for i in range(rows):
for j in range(7):
sheet.write(i+1, j, TQ_model.data(TQ_model.index(i,j)))
workbook.save('{}教师学生选课信息.xls'.format(self.w_teach()))
QMessageBox.about(self, "恭喜您", "报表生成成功！")
def w_teach(self):
query = QSqlQuery()
if query.exec('select tno, tname from teacher'):
no = query.record().indexOf('tno')
name = query.record().indexOf('tname')
while query.next():
tno = query.value(no)
tname = query.value(name)
if Win.name_2.text() == tno:
return tname
def T_modifysct(self):
query = QSqlQuery()
n = self.tableView_2.currentIndex().row()
str1 = TQ_model.data(TQ_model.index(n, 0))
str2 = TQ_model.data(TQ_model.index(n, 5))
num = int(self.lineEdit_3.text())
if num > 0 and num < 100:
if query.exec('update sct set grade = "{}" where sno = "{}" and cno = "{}"'.format(self.lineEdit_3.text(), str1, str2)):
QMessageBox.about(self, "恭喜您", "修改成功！")
self.S_sctView()
else:
QMessageBox.warning(self, "警告", "请输入正确的分数！", QMessageBox.Yes)
def S_sctView(self):
self.S_sctModel()
self.tableView.setModel(TQ_model)
self.tableView_2.setModel(TQ_model)
num = self.tableView.model().rowCount()
self.label_6.setText("欢迎您： 教师{}".format(self.w_teach()))
self.label_8.setText("{}".format(self.MAX_sct()))
self.label_7.setText("{}".format(num))
self.label_9.setText("{}".format(self.MIN_sct()))
def S_sctModel(self):
self.a = Win.name_2.text()
TQ_model.setQuery('select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and c.tno = "{}"'.format(self.a))
TQ_model.setHeaderData(0, Qt.Horizontal, "学号")
TQ_model.setHeaderData(1, Qt.Horizontal, "姓名")
TQ_model.setHeaderData(2, Qt.Horizontal, "性别")
TQ_model.setHeaderData(3, Qt.Horizontal, "系别")
TQ_model.setHeaderData(4, Qt.Horizontal, "教师")
TQ_model.setHeaderData(5, Qt.Horizontal, "课程号")
TQ_model.setHeaderData(6, Qt.Horizontal, "成绩")
def MAX_sct(self):
rows = self.tableView.model().rowCount()
max = 0
for i in range(rows):
if TQ_model.data(TQ_model.index(i, 6)) != '':
if max < TQ_model.data(TQ_model.index(i, 6)):
max = TQ_model.data(TQ_model.index(i, 6))
return max
def MIN_sct(self):
rows = self.tableView.model().rowCount()
min = 101
for i in range(rows):
if TQ_model.data(TQ_model.index(i, 6)) != '':
if min > TQ_model.data(TQ_model.index(i, 6)):
min = TQ_model.data(TQ_model.index(i, 6))
return min
class modifyWindow(QMainWindow, Ui_modifyForm):
def __init__(self):
super(modifyWindow,self).__init__()
self.setupUi(self)
self.pushButton.clicked.connect(self.modify)
self.pushButton_2.clicked.connect(lambda: modify.close())
def T_modify(self):
query = QSqlQuery()
a = self.lineEdit_2.text()
a = ''.join(a.split())
if query.exec('select password from teacherlog where name = "{}" and password = "{}"'.format(Win.name_2.text(),
self.lineEdit_2)):
if len(a) >= 4 and len(a) < 8:
if self.lineEdit_3.text() == self.lineEdit_2.text():
query.exec('update teacherlog set password = "{}" where name = "{}"'.format(self.lineEdit_2.text(),
Win.name_2.text()))
QMessageBox.about(self, "恭喜您", "修改成功！")
self.close()
else:
QMessageBox.warning(self, "警告", "前后密码不一致！", QMessageBox.Yes)
else:
QMessageBox.warning(self, "警告", "新密码长度不足或过长！", QMessageBox.Yes)
else:
QMessageBox.warning(self, "警告", "密码错误！", QMessageBox.Yes)
self.lineEdit.setText("")
self.lineEdit_2.setText("")
self.lineEdit_3.setText("")
def S_modify(self):
query = QSqlQuery()
a = self.lineEdit_2.text()
a = ''.join(a.split())
if query.exec('select password from stu where name = "{}" and password = "{}"'.format(Win.name_2.text(),
self.lineEdit_2)):
if len(a) >= 4 and len(a) < 8:
if self.lineEdit_3.text() == self.lineEdit_2.text():
query.exec('update stu set password = "{}" where name = "{}"'.format(self.lineEdit_2.text(),
Win.name_2.text()))
QMessageBox.about(self, "恭喜您", "修改成功！")
self.close()
else:
QMessageBox.warning(self, "警告", "前后密码不一致！", QMessageBox.Yes)
else:
QMessageBox.warning(self, "警告", "新密码长度不足或过长！", QMessageBox.Yes)
else:
QMessageBox.warning(self, "警告", "密码错误！", QMessageBox.Yes)
self.lineEdit.setText("")
self.lineEdit_2.setText("")
self.lineEdit_3.setText("")
def G_modify(self):
query = QSqlQuery()
a = self.lineEdit_2.text()
a = ''.join(a.split())
if query.exec(
'select password from Administrator where name = "{}" and password = "{}"'.format(Win.name_2.text(),
self.lineEdit_2)):
if len(a) >= 4 and len(a) < 8:
if self.lineEdit_3.text() == self.lineEdit_2.text():
query.exec('update Administrator set password = "{}" where name = "{}"'.format(self.lineEdit_2.text(), Win.name_2.text()))
QMessageBox.about(self, "恭喜您", "修改成功！")
self.close()
else:
QMessageBox.warning(self, "警告", "前后密码不一致！", QMessageBox.Yes)
else:
QMessageBox.warning(self, "警告", "新密码长度不足或过长！", QMessageBox.Yes)
else:
QMessageBox.warning(self, "警告", "密码错误！", QMessageBox.Yes)
self.lineEdit.setText("")
self.lineEdit_2.setText("")
self.lineEdit_3.setText("")
def modify(self):
if Win.comboBox.currentIndex() == 0:
self.T_modify()
if Win.comboBox.currentIndex() == 1:
self.S_modify()
if Win.comboBox.currentIndex() == 2:
self.G_modify()
class queryWindow(QMainWindow, Ui_queryForm):
def __init__(self):
super(queryWindow, self).__init__()
self.setupUi(self)
self.a = ''
self.b = ''
self.query = ''
self.query_choose()
self.pushButton.clicked.connect(self.query_1)
self.pushButton_3.clicked.connect(self.query_choose)
self.pushButton_2.clicked.connect(self.query_2)
self.pushButton_5.clicked.connect(self.query_xls)
def S_query_model(self):
S_query.setQuery('select sno, sname, ssex, sage, sdept from student where sno = "{}"'.format(self.lineEdit_5.text()))
S_query.setHeaderData(0, Qt.Horizontal, "学号")
S_query.setHeaderData(1, Qt.Horizontal, "姓名")
S_query.setHeaderData(2, Qt.Horizontal, "性别")
S_query.setHeaderData(3, Qt.Horizontal, "年龄")
S_query.setHeaderData(4, Qt.Horizontal, "系别")
def T_query_model(self):
T_query.setQuery('select tno, tname, tsex, tage, teb, tpt, cno1, cno2, cno3 from teacher where tno = "{}"'.format(self.lineEdit_5.text()))
T_query.setHeaderData(0, Qt.Horizontal, "教工号")
T_query.setHeaderData(1, Qt.Horizontal, "姓名")
T_query.setHeaderData(2, Qt.Horizontal, "性别")
T_query.setHeaderData(3, Qt.Horizontal, "年龄")
T_query.setHeaderData(4, Qt.Horizontal, "学历")
T_query.setHeaderData(5, Qt.Horizontal, "职称")
T_query.setHeaderData(6, Qt.Horizontal, "主讲课程一")
T_query.setHeaderData(7, Qt.Horizontal, "主讲课程二")
T_query.setHeaderData(8, Qt.Horizontal, "主讲课程三")
def C_query_model(self):
C_query.setQuery('select cno, cname, cpno, ccredit from course where cno = "{}"'.format(self.lineEdit_5.text()))
C_query.setHeaderData(0, Qt.Horizontal, "课程号")
C_query.setHeaderData(1, Qt.Horizontal, "课程名")
C_query.setHeaderData(2, Qt.Horizontal, "先行课编号")
C_query.setHeaderData(3, Qt.Horizontal, "学分")
def D_query_model(self):
D_query.setQuery('select dno, dname, dnamager from department where dno = "{}"'.format(self.lineEdit_5.text()))
D_query.setHeaderData(0, Qt.Horizontal, "系编号")
D_query.setHeaderData(1, Qt.Horizontal, "系名")
D_query.setHeaderData(2, Qt.Horizontal, "系主任")
def query_1(self):
if self.comboBox_6.currentIndex() == 0:
QMessageBox.warning(self, "警告", "请选择需要查询的信息！", QMessageBox.Yes)
if self.comboBox_6.currentIndex() == 1:
self.S_query_model()
self.tableView.setModel(S_query)
if self.comboBox_6.currentIndex() == 2:
self.T_query_model()
self.tableView.setModel(T_query)
if self.comboBox_6.currentIndex() == 3:
self.D_query_model()
self.tableView.setModel(D_query)
if self.comboBox_6.currentIndex() == 4:
self.C_query_model()
self.tableView.setModel(sct_query)
def query_choose(self):
if self.comboBox.currentIndex() == 0:
self.comboBox_2.setEnabled(False)
else:
self.comboBox_2.setEnabled(True)
def query_2(self):
self.Q_query_model()
self.tableView_2.setModel(Q_query)
def Q_query_model(self):
self.query_model()
a = self.query
Q_query.setQuery(a)
Q_query.setHeaderData(0, Qt.Horizontal, "学号")
Q_query.setHeaderData(1, Qt.Horizontal, "姓名")
Q_query.setHeaderData(2, Qt.Horizontal, "性别")
Q_query.setHeaderData(3, Qt.Horizontal, "系别")
Q_query.setHeaderData(4, Qt.Horizontal, "教师")
Q_query.setHeaderData(5, Qt.Horizontal, "课程号")
Q_query.setHeaderData(6, Qt.Horizontal, "成绩")
def query_model(self):
self.a = self.lineEdit.text()
self.b = self.lineEdit_2.text()
a = self.comboBox_2.currentIndex()
b = self.comboBox.currentIndex()
c = ''
if b == 0:
self.comboBox_2.setCurrentIndex(0)
self.comboBox_2.setEnabled(False)
QMessageBox.warning(self, "警告", "请选择需要查询的信息！", QMessageBox.Yes)
if a == b:
a = 0
if a == 0:
if b == 1:
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sno = "{}"'.format(self.a)
if b == 2:
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sname = "{}"'.format(self.a)
if b == 3:
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.ssex = "{}"'.format(self.a)
if b == 4:
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and c.tno = "{}"'.format(self.a)
if b == 5:
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sdept = "{}"'.format(self.a)
else:
if b == 1 and a == 2 or b == 2 and a == 1:
if b == 2:
c = self.a
self.a = self.b
self.b = c
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sno = "{}" and a.sname = "{}"'.format(self.a, self.b)
if b == 1 and a == 3 or b == 3 and a == 1:
if b == 3:
c = self.a
self.a = self.b
self.b = c
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sno = "{}" and a.ssex = "{}"'.format(self.a, self.b)
if b == 1 and a == 4 or b == 4 and a == 1:
if b == 4:
c = self.a
self.a = self.b
self.b = c
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sno = "{}" and c.tno = "{}"'.format(self.a, self.b)
if b == 1 and a == 5 or b == 5 and a == 1:
if b == 5:
c = self.a
self.a = self.b
self.b = c
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sno = "{}" and a.sdept = "{}"'.format(self.a, self.b)
if b == 2 and a == 3 or b == 3 and a == 2:
if b == 3:
c = self.a
self.a = self.b
self.b = c
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sname = "{}" and a.ssex = "{}"'.format(self.a, self.b)
if b == 2 and a == 4 or b == 4 and a == 2:
if b == 4:
c = self.a
self.a = self.b
self.b = c
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sname = "{}" and c.tno = "{}"'.format(self.a, self.b)
if b == 2 and a == 5 or b == 5 and a == 2:
if b == 5:
c = self.a
self.a = self.b
self.b = c
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sname = "{}" and a.sdept = "{}"'.format(self.a, self.b)
if b == 3 and a == 4 or b == 4 and a == 3:
if b == 4:
c = self.a
self.a = self.b
self.b = c
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.ssex = "{}" and c.tno = "{}"'.format(self.a, self.b)
if b == 3 and a == 5 or b == 5 and a == 3:
if b == 5:
c = self.a
self.a = self.b
self.b = c
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.ssex = "{}" and a.sdept = "{}"'.format(self.a, self.b)
if b == 4 and a == 5 or b == 5 and a == 4:
if b == 5:
c = self.a
self.a = self.b
self.b = c
self.query = 'select a.sno, a.sname, a.ssex, a.sdept, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and c.tno = "{}" and a.sdept = "{}"'.format(self.a, self.b)
def query_xls(self):
rows = self.tableView_2.model().rowCount()
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('选课信息查询')
str = ['学号','姓名','性别','系别','教师','课程号','成绩']
for a in range(7):
sheet.write(0, a, str[a])
for i in range(rows):
for j in range(7):
sheet.write(i+1, j, Q_query.data(Q_query.index(i,j)))
now_time = datetime.datetime.now()
time = now_time.strftime("%y%m%d%H%M%S")
workbook.save('{}查询结果.xls'.format(time))
QMessageBox.about(self, "恭喜您", "报表生成成功！")
class LoginWindow(QMainWindow, Ui_loginForm):
def __init__(self):
super(LoginWindow, self).__init__()
self.setupUi(self)
self.pushButton.clicked.connect(self.login)
self.pushButton_2.clicked.connect(self.addshow)
def G_login(self):
query = QSqlQuery()
if query.exec('select name,password from Administrator'):
id_index = query.record().indexOf('name')
name_index = query.record().indexOf('password')
p = 1
while query.next():
id = query.value(id_index)
name = query.value(name_index)
if self.name_2.text() == id and self.mima_2.text() == name:
p = 0
test.show()
self.close()
if p == 1:
QMessageBox.warning(self, "警告", "用户名或密码错误！", QMessageBox.Yes)
self.mima_2.setText("")
def T_login(self):
query = QSqlQuery()
if query.exec('select name,password from teacherlog'):
id_index = query.record().indexOf('name')
name_index = query.record().indexOf('password')
p = 1
while query.next():
id = query.value(id_index)
name = query.value(name_index)
if self.name_2.text() == id and self.mima_2.text() == name:
p = 0
teacherWin.show()
self.close()
if p == 1:
QMessageBox.warning(self,"警告","用户名或密码错误！",QMessageBox.Yes)
self.mima_2.setText("")
def T_add(self):
query = QSqlQuery()
if query.exec('select tno from teacher except select name from teacherlog'):
id_index = query.record().indexOf('tno')
p = 1
no = add.lineEdit.text()
pas = add.lineEdit_2.text()
pas_2 = add.lineEdit_3.text()
while query.next():
id = query.value(id_index)
if no == id:
p = 0
if pas == pas_2:
query.exec("insert into teacherlog values('{}','{}')".format(no, pas))
QMessageBox.about(self, "恭喜您", "注册成功！")
add.close()
self.show()
else:
QMessageBox.warning(self, "警告", "前后密码不一致！", QMessageBox.Yes)
if p == 1:
QMessageBox.warning(self,"警告","没有该教师号或该教师号已被注册！",QMessageBox.Yes)
add.lineEdit_2.setText("")
add.lineEdit_3.setText("")
def S_add(self):
query = QSqlQuery()
if query.exec('select sno from student except select name from stu'):
id_index = query.record().indexOf('sno')
p = 1
no = add.lineEdit.text()
pas = add.lineEdit_2.text()
pas_2 = add.lineEdit_3.text()
while query.next():
id = query.value(id_index)
if no == id:
p = 0
if pas == pas_2:
query.exec("insert into stu values('{}','{}')".format(no, pas))
QMessageBox.about(self, "恭喜您", "注册成功！")
add.close()
self.show()
else:
QMessageBox.warning(self, "警告", "前后密码不一致！", QMessageBox.Yes)
if p == 1:
QMessageBox.warning(self, "警告", "没有该学生号或该学生号已被注册！", QMessageBox.Yes)
add.lineEdit_2.setText("")
add.lineEdit_3.setText("")
def add(self):
if self.comboBox.currentIndex() == 0:
self.T_add()
if self.comboBox.currentIndex() == 1:
self.S_add()
def addshow(self):
if self.comboBox.currentIndex() == 2:
QMessageBox.warning(self, "警告", "管理员无法进行注册！", QMessageBox.Yes)
else:
self.close()
add.show()
def S_login(self):
global S_id
query = QSqlQuery()
if query.exec('select name,password from stu'):
id_index = query.record().indexOf('name')
name_index = query.record().indexOf('password')
p = 1
while query.next():
S_id = query.value(id_index)
name = query.value(name_index)
if self.name_2.text() == S_id and self.mima_2.text() == name:
p = 0
break
if p == 0:
Win2.show()
self.close()
else:
QMessageBox.warning(self,"警告","用户名或密码错误！",QMessageBox.Yes)
self.mima_2.setText("")
def login(self):
if self.comboBox.currentIndex() == 0:
self.T_login()
if self.comboBox.currentIndex() == 1:
self.S_login()
if self.comboBox.currentIndex() == 2:
self.G_login()
class S_MainWindow(QMainWindow, Ui_Form):
def __init__(self):
super(S_MainWindow, self).__init__()
self.setupUi(self)
global S_id
self.pushButton_3.setEnabled(False)
Win.pushButton.clicked.connect(self.S_sqldata)
self.pushButton_8.clicked.connect(self.S_sctxlw)
self.pushButton.clicked.connect(lambda: modify.show())
self.pushButton_5.clicked.connect(lambda: self.tableView_3.setModel(T_model))
self.tableView_2.setModel(C_model)
self.pushButton_7.clicked.connect(self.S_exit)
self.pushButton_4.clicked.connect(self.ST_view)
self.pushButton_2.clicked.connect(self.SC_view)
self.pushButton_3.clicked.connect(self.S_choose)
self.pushButton_6.clicked.connect(self.S_exitcourse)
def S_sctxlw(self):
rows = self.tableView.model().rowCount()
S_workbook = xlwt.Workbook()
sheet1 = S_workbook.add_sheet('成绩')
str = ['学号','姓名','性别','任课教师','课程代码','成绩']
for a in range(6):
sheet1.write(0, a, str[a])
for i in range(rows):
for j in range(6):
sheet1.write(i+1,j,Q_model.data(Q_model.index(i,j)))
S_workbook.save('{}选课信息表.xls'.format(S_id))
QMessageBox.about(self, "恭喜您", "报表生成成功！")
def S_course(self):
str = self.tableView_2.currentIndex().row()
self.course = C_model.data(C_model.index(str, 0))
def S_sqldata(self):
query = QSqlQuery()
p = 1
self.num = 0
QueryModel(Q_model)
if query.exec('select sno, sname, ssex, sage, sdept from student'):
sno = query.record().indexOf('sno')
sname = query.record().indexOf('sname')
ssex = query.record().indexOf('ssex')
sage = query.record().indexOf('sage')
sdept = query.record().indexOf('sdept')
while query.next():
Sno = query.value(sno)
self.Sname = query.value(sname)
Ssex = query.value(ssex)
Sage = query.value(sage)
Sdept = query.value(sdept)
if Sno == S_id:
p = 0
break
if p == 0:
self.lineEdit.setText(self.Sname)
self.lineEdit.setReadOnly(True)
self.lineEdit_2.setText(S_id)
self.lineEdit_2.setReadOnly(True)
self.lineEdit_3.setText(Ssex)
self.lineEdit_3.setReadOnly(True)
self.lineEdit_4.setText('%d' %Sage)
self.lineEdit_4.setReadOnly(True)
self.lineEdit_5.setText(Sdept)
self.lineEdit_5.setReadOnly(True)
self.tableView.setModel(Q_model)
def ST_model(self):
SQ_model.setQuery('select tno, tname, tsex, tage, teb, tpt, cno1, cno2, cno3 from teacher where tno = "{}"'.format(self.lineEdit_6.text()))
SQ_model.setHeaderData(0, Qt.Horizontal, "教工号")
SQ_model.setHeaderData(1, Qt.Horizontal, "姓名")
SQ_model.setHeaderData(2, Qt.Horizontal, "性别")
SQ_model.setHeaderData(3, Qt.Horizontal, "年龄")
SQ_model.setHeaderData(4, Qt.Horizontal, "学历")
SQ_model.setHeaderData(5, Qt.Horizontal, "职称")
SQ_model.setHeaderData(6, Qt.Horizontal, "主讲课程一")
SQ_model.setHeaderData(7, Qt.Horizontal, "主讲课程二")
SQ_model.setHeaderData(8, Qt.Horizontal, "主讲课程三")
def SC_model(self):
SQ_query.setQuery('select cno, cname, cpno, ccredit from course where cno = "{}"'.format(self.course))
SQ_query.setHeaderData(0, Qt.Horizontal, "课程号")
SQ_query.setHeaderData(1, Qt.Horizontal, "课程名")
SQ_query.setHeaderData(2, Qt.Horizontal, "先行课编号")
SQ_query.setHeaderData(3, Qt.Horizontal, "学分")
def SC_query(self):
query = QSqlQuery()
if query.exec('select cpno from course where cno = "{}"'.format(self.course)):
cpno = query.record().indexOf('cpno')
while query.next():
Cpno = query.value(cpno)
if Cpno == '':
self.num2 = 0
self.label_7.setText("无")
else:
self.num2 = 1
self.label_7.setText("有")
def SC_Query_2(self):
query = QSqlQuery()
if query.exec('select d.cno from student a, sct b, teacher c, course d where b.tno = c.tno and a.sno = b.sno and b.cno = d.cpno and d.cno = "{}" and a.sno = "{}"'.format(self.course,S_id)):
cno = query.record().indexOf('cno')
self.num = 0
while query.next():
Cno = query.value(cno)
if Cno != '':
self.num = 1
self.label_9.setText("是")
if self.num == 0:
self.label_9.setText("否")
def ST_view(self):
self.ST_model()
self.tableView_3.setModel(SQ_model)
def S_chooseT(self):
query = QSqlQuery()
List = []
if query.exec('select cno1, cno2, cno3, tno from teacher'):
cno1 = query.record().indexOf('cno1')
cno2 = query.record().indexOf('cno2')
cno3 = query.record().indexOf('cno3')
tno = query.record().indexOf('tno')
while query.next():
Cno1 = query.value(cno1)
Cno2 = query.value(cno2)
Cno3 = query.value(cno3)
Tno = query.value(tno)
if Cno1 == self.course or Cno2 == self.course or Cno3 == self.course:
List.append(Tno)
self.teacher = r.choice(List)
self.label_10.setText("任课教师为：{}".format(self.teacher))
def S_choose_course(self):
query = QSqlQuery()
if query.exec('select C_course from choose'):
number = query.record().indexOf('C_course')
while query.next():
num = query.value(number)
if num == "K":
return 1
else:
return 0
def S_choose(self):
a = self.tableView.model().rowCount()
p = 0
for i in range(a):
str = Q_model.data(Q_model.index(i, 4))
if self.course == str:
p = 1
break
query = QSqlQuery()
if self.S_choose_course() == 1:
if p == 0:
if self.num == 1 or self.num2 == 0:
if query.exec("insert into sct values('{}','{}','{}','')".format(S_id, self.course, self.teacher)):
QMessageBox.about(self, "恭喜您", "选课成功！")
QueryModel(Q_model)
self.tableView.setModel(Q_model)
else:
QMessageBox.warning(self, "警告", "未选修前置课程！", QMessageBox.Yes)
else:
QMessageBox.warning(self, "警告", "已经选修过该课程了！", QMessageBox.Yes)
self.pushButton_3.setEnabled(False)
else:
QMessageBox.warning(self, "警告", "还未到选课时间！", QMessageBox.Yes)
def SC_Query_3(self):
query = QSqlQuery()
str = self.tableView.currentIndex().row()
rows = self.tableView.model().rowCount()
Course = Q_model.data(Q_model.index(str, 4))
if query.exec('select cno from course where cpno = "{}"'.format(Course)):
cno = query.record().indexOf('cno')
while query.next():
Cno = query.value(cno)
for i in range(rows):
if Cno == Q_model.data(Q_model.index(i, 4)):
return 1
return 0
def S_exitcourse(self):
str = self.tableView.currentIndex().row()
course = Q_model.data(Q_model.index(str, 5))
sno = Q_model.data(Q_model.index(str, 0))
cno = Q_model.data(Q_model.index(str, 4))
query = QSqlQuery()
if self.SC_Query_3() == 0:
if course == '':
if query.exec('delete from sct where sno = "{}" and cno = "{}"'.format(sno, cno)):
QueryModel(Q_model)
self.tableView.setModel(Q_model)
else:
QMessageBox.warning(self, "警告", "已有成绩的课程无法退课！", QMessageBox.Yes)
else:
QMessageBox.warning(self, "警告", "该课程有后置课程在学习中，请先退选后置课程！", QMessageBox.Yes)
def SC_view(self):
self.S_course()
self.SC_model()
self.SC_query()
self.SC_Query_2()
self.S_chooseT()
self.pushButton_3.setEnabled(True)
self.tableView_4.setModel(SQ_query)
def S_exit(self):
self.close()
Win.show()
class MyMainWindow(QMainWindow , Ui_MainWindow):
def __init__(self):
super(MyMainWindow, self).__init__()
self.setupUi(self)
self.action_3.triggered.connect(self.close)
self.action_2.triggered.connect(self.add)
self.action2_D.triggered.connect(self.Del)
self.action_4.triggered.connect(self.sure)
self.actionxiugai.triggered.connect(lambda: modify.show())
self.action3_C.triggered.connect(lambda: queryWin.show())
self.actiony.triggered.connect(self.quit_login)
self.actiont.triggered.connect(self.A_xls)
self.actionK.triggered.connect(self.K)
self.actionJ.triggered.connect(self.J)
self.tableView.setModel(S_model)
self.tableView_2.setModel(T_model)
self.tableView_5.setModel(sct_model)
self.tableView_4.setModel(D_model)
self.tableView_3.setModel(C_model)
def add(self):
global Tab
if self.tabWidget.currentIndex()==0:
Tab = S_model
if self.tabWidget.currentIndex()==1:
Tab = T_model
if self.tabWidget.currentIndex()==2:
Tab = C_model
if self.tabWidget.currentIndex()==3:
Tab = D_model
if self.tabWidget.currentIndex()==4:
Tab = sct_model
ret = Tab.insertRows(Tab.rowCount(), 1)
print('insertRows=%s' % str(ret))
def Del(self):
if self.tabWidget.currentIndex()==0:
S_model.removeRow(self.tableView.currentIndex().row())
S_model.select()
if self.tabWidget.currentIndex()==1:
T_model.removeRow(self.tableView_2.currentIndex().row())
T_model.select()
if self.tabWidget.currentIndex()==2:
C_model.removeRow(self.tableView_3.currentIndex().row())
C_model.select()
if self.tabWidget.currentIndex()==3:
D_model.removeRow(self.tableView_4.currentIndex().row())
D_model.select()
if self.tabWidget.currentIndex()==4:
sct_model.removeRow(self.tableView_5.currentIndex().row())
sct_model.select()
def G_course(self):
query = QSqlQuery()
if query.exec('select C_course from choose'):
number = query.record().indexOf('C_course')
while query.next():
num = query.value(number)
if num == "K":
return 1
else:
return 0
def K(self):
query = QSqlQuery()
if self.G_course() == 0:
if query.exec('update choose set C_course = "K" where C_course = "J"'):
QMessageBox.about(self, "恭喜您", "开启选课成功！")
else:
QMessageBox.warning(self, "警告", "已经开启过选课了", QMessageBox.Yes)
def J(self):
query = QSqlQuery()
if self.G_course() == 1:
if query.exec('update choose set C_course = "J" where C_course = "K"'):
QMessageBox.about(self, "恭喜您", "关闭选课成功！")
else:
QMessageBox.warning(self, "警告", "还未开启过选课", QMessageBox.Yes)
def sure(self):
if self.tabWidget.currentIndex()==0:
S_model.select()
if self.tabWidget.currentIndex()==1:
T_model.select()
if self.tabWidget.currentIndex()==2:
C_model.select()
if self.tabWidget.currentIndex()==3:
D_model.select()
if self.tabWidget.currentIndex()==4:
sct_model.select()
def A_xls(self):
S_rows = self.tableView.model().rowCount()
T_rows = self.tableView_2.model().rowCount()
C_rows = self.tableView_3.model().rowCount()
D_rows = self.tableView_4.model().rowCount()
sct_rows = self.tableView_5.model().rowCount()
workbook = xlwt.Workbook()
if self.tabWidget.currentIndex()==0:
sheet = workbook.add_sheet('学生')
str = ['学号','姓名','性别','年龄','系别']
for a in range(5):
sheet.write(0, a, str[a])
for i in range(S_rows):
for j in range(5):
sheet.write(i + 1, j, S_model.data(S_model.index(i, j)))
workbook.save('student信息表.xls')
if self.tabWidget.currentIndex()==1:
sheet = workbook.add_sheet('教师')
str = ['教工号', '姓名', '性别', '年龄', '学历','职称','主讲课程一','主讲课程二','主讲课程三']
for a in range(9):
sheet.write(0, a, str[a])
for i in range(T_rows):
for j in range(9):
sheet.write(i + 1, j, T_model.data(T_model.index(i, j)))
workbook.save('teacher信息表.xls')
if self.tabWidget.currentIndex()==2:
sheet = workbook.add_sheet('课程')
str = ['课程号', '课程名', '先行课程号', '学分']
for a in range(4):
sheet.write(0, a, str[a])
for i in range(C_rows):
for j in range(4):
sheet.write(i + 1, j, C_model.data(C_model.index(i, j)))
workbook.save('course信息表.xls')
if self.tabWidget.currentIndex()==3:
sheet = workbook.add_sheet('系别')
str = ['系编号', '系名', '系主任']
for a in range(3):
sheet.write(0, a, str[a])
for i in range(D_rows):
for j in range(3):
sheet.write(i + 1, j, D_model.data(D_model.index(i, j)))
workbook.save('department信息表.xls')
if self.tabWidget.currentIndex()==4:
sheet = workbook.add_sheet('选课信息')
str = ['学号', '课程号', '教工号', '成绩']
for a in range(4):
sheet.write(0, a, str[a])
for i in range(sct_rows):
for j in range(4):
sheet.write(i + 1, j, sct_model.data(sct_model.index(i, j)))
workbook.save('sct信息表.xls')
QMessageBox.about(self, "恭喜您", "报表生成成功！")
def quit_login(self):
self.close()
Win.show()
def studentModel(S_model):
S_model.setTable('student')
S_model.setRelation(4, QtSql.QSqlRelation('department', 'dname', 'dname'))
S_model.setEditStrategy(QSqlTableModel.OnFieldChange)
S_model.setSort(0, QtCore.Qt.AscendingOrder)
S_model.select()
S_model.setHeaderData(0, Qt.Horizontal, "学号")
S_model.setHeaderData(1, Qt.Horizontal, "姓名")
S_model.setHeaderData(2, Qt.Horizontal, "性别")
S_model.setHeaderData(3, Qt.Horizontal, "年龄")
S_model.setHeaderData(4, Qt.Horizontal, "系别")
def teacherModel(T_model):
T_model.setTable('teacher')
T_model.setRelation(6, QtSql.QSqlRelation('course', 'cno', 'cno'))
T_model.setRelation(7, QtSql.QSqlRelation('course', 'cno', 'cno'))
T_model.setRelation(8, QtSql.QSqlRelation('course', 'cno', 'cno'))
T_model.setEditStrategy(QSqlTableModel.OnFieldChange)
T_model.select()
T_model.setHeaderData(0, Qt.Horizontal, "教工号")
T_model.setHeaderData(1, Qt.Horizontal, "姓名")
T_model.setHeaderData(2, Qt.Horizontal, "性别")
T_model.setHeaderData(3, Qt.Horizontal, "年龄")
T_model.setHeaderData(4, Qt.Horizontal, "学历")
T_model.setHeaderData(5, Qt.Horizontal, "职称")
T_model.setHeaderData(6, Qt.Horizontal, "主讲课程一")
T_model.setHeaderData(7, Qt.Horizontal, "主讲课程二")
T_model.setHeaderData(8, Qt.Horizontal, "主讲课程三")
def sctModel(sct_model):
sct_model.setTable('sct')
sct_model.setRelation(0, QtSql.QSqlRelation('student', 'sno', 'sno'))
sct_model.setRelation(1, QtSql.QSqlRelation('course', 'cno', 'cno'))
sct_model.setRelation(2, QtSql.QSqlRelation('teacher', 'tno', 'tno'))
sct_model.setEditStrategy(QSqlTableModel.OnFieldChange)
sct_model.select()
sct_model.setHeaderData(0, Qt.Horizontal, "学号")
sct_model.setHeaderData(1, Qt.Horizontal, "课程号")
sct_model.setHeaderData(2, Qt.Horizontal, "教工号")
sct_model.setHeaderData(3, Qt.Horizontal, "成绩")
def departmentModel(D_model):
D_model.setTable('department')
D_model.setEditStrategy(QSqlTableModel.OnFieldChange)
D_model.select()
D_model.setHeaderData(0, Qt.Horizontal, "系编号")
D_model.setHeaderData(1, Qt.Horizontal, "系名")
D_model.setHeaderData(2, Qt.Horizontal, "系主任")
def courseModel(C_model):
C_model.setTable('course')
sct_model.setRelation(2, QtSql.QSqlRelation('course', 'cno', 'cno'))
C_model.setEditStrategy(QSqlTableModel.OnFieldChange)
C_model.select()
C_model.setHeaderData(0, Qt.Horizontal, "课程号")
C_model.setHeaderData(1, Qt.Horizontal, "课程名")
C_model.setHeaderData(2, Qt.Horizontal, "先行课编号")
C_model.setHeaderData(3, Qt.Horizontal, "学分")
def QueryModel(Q_model):
global S_id
Q_model.setQuery('select a.sno, a.sname, a.ssex, c.tname, b.cno, b.grade from student a, sct b, teacher c where b.tno = c.tno and a.sno = b.sno and a.sno = "{}"'.format(S_id))
Q_model.setHeaderData(0, Qt.Horizontal, "学号")
Q_model.setHeaderData(1, Qt.Horizontal, "姓名")
Q_model.setHeaderData(2, Qt.Horizontal, "性别")
Q_model.setHeaderData(3, Qt.Horizontal, "任课教师")
Q_model.setHeaderData(4, Qt.Horizontal, "课程代码")
Q_model.setHeaderData(5, Qt.Horizontal, "成绩")
if __name__ == "__main__":
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('学生选课系统.db')
db.open()
S_model = QSqlRelationalTableModel()
T_model = QSqlRelationalTableModel()
C_model = QSqlRelationalTableModel()
D_model = QSqlRelationalTableModel()
sct_model = QSqlRelationalTableModel()
Q_model = QSqlQueryModel()
TQ_model = QSqlQueryModel()
SQ_model = QSqlQueryModel()
S_query = QSqlQueryModel()
T_query = QSqlQueryModel()
C_query = QSqlQueryModel()
D_query = QSqlQueryModel()
sct_query = QSqlQueryModel()
Q_query = QSqlQueryModel()
SQ_query = QSqlQueryModel()
studentModel(S_model)
teacherModel(T_model)
departmentModel(D_model)
courseModel(C_model)
sctModel(sct_model)
app = QApplication(sys.argv)
Win = LoginWindow()
Win2 = S_MainWindow()
test = MyMainWindow()
add = addnoWindow()
teacherWin = teacherWindow()
queryWin = queryWindow()
modify = modifyWindow()
Win.show()
sys.exit(app.exec_())
Вы можете оставить комментарий после Вход в систему
Неприемлемый контент может быть отображен здесь и не будет показан на странице. Вы можете проверить и изменить его с помощью соответствующей функции редактирования.
Если вы подтверждаете, что содержание не содержит непристойной лексики/перенаправления на рекламу/насилия/вульгарной порнографии/нарушений/пиратства/ложного/незначительного или незаконного контента, связанного с национальными законами и предписаниями, вы можете нажать «Отправить» для подачи апелляции, и мы обработаем ее как можно скорее.
Опубликовать ( 0 )