devbox
代码探险家
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Python+mysql+tkinter+matplotlib的可视化学生成绩管理系统课程设计_图形化实现班级成绩管理

作者：代码探险家 | 2024-07-11 18:42:41

赞

踩

图形化实现班级成绩管理

使用模块要求：

tkinter、numpy、openpyxl、matplotlib、pymysql、re、time；其中matplotlib用于绘制统计图

开发工具：pycharm专业版、python3.7、mysql8.0

运行效果:

主页面：

​​​​​​​

 

 教师端主要功能：

f38336fd4e1a4a01b7798fd09219b608.png

 教师端登录后的效果——个人资料页面：bd9003a086e3430387991eddc3c52a7a.png

  教师端登录后的效果——学生查询页面：a8c4d1ba3f114f2eb8cdb91e24ba8189.png

 教师端登录后的效果——成绩查询页面：5ee8fc3c23df400c8411af0c0bbc0c1a.png

 教师端登录后的效果——成绩分析页面：

d72520d0351c4607999a4304aa7c34dd.png

成绩分析页面——课程成绩分析（统计图）：

619e5830475f4877bcd2e05a664d5647.png

各班级创建分析页面——统计图：

78748ba0041c41a2bbf6fa4576c3d557.png

 班级综合成绩评定页面——扇形统计图：

13780bc7892743ab900f6cf4fe4da0db.png

 修改密码页面：

c93d84ea9f1e4908803333332b50f43d.png

课程管理页面——根据学院、考试方式进行添加或删除课程：

3a703763bee04907a16c6ed6c2d95789.png

 Teacherpage.py页面代码如下：

更多代码：看主页联系、或访问：

腾讯文档腾讯文档-在线文档https://docs.qq.com/doc/p/71239d69a76f56cf1521717ae6b22c27cf876f10

  1. from tkinter import *
  2. from tkinter import messagebox, filedialog
  3. from tkinter.ttk import *
  4.  
  5. import openpyxl as openpyxl
  6.  
  7. import AddCoursePage
  8. import AddScorePage
  9. import ClassGradeAnalysis
  10. import ComprehensivePerformanceEvaluation
  11. import CourseScoreAnalysis
  12. import Dao
  13. import DeleteCoursePage
  14. import DeleteScorePage
  15. import Login
  16. import UpdateCoursePage
  17.  
  18. import AddStudentPage
  19. import DeleteStudentPage
  20. import UpdateScorePage
  21. import UpdateStudentPage
  22.  
  23. import numpy as np
  24. import matplotlib.pyplot as plt
  25.  
  26. class WinGUI(Tk):
  27.     def __init__(self):
  28.         super().__init__()
  29.         self.__win()
  30.         self.tk_label_title = self.__tk_label_title()
  31.         self.tk_label_current_user = self.__tk_label_current_user()
  32.         self.tk_tabs_content = Frame_content(self)
  33.         self.tk_button_logout_user = self.__tk_button_logout_user()
  34.  
  35.     def __win(self):
  36.         self.title("教师端")
  37.         # 设置窗口大小、居中
  38.         width = 1000
  39.         height = 600
  40.         screenwidth = self.winfo_screenwidth()
  41.         screenheight = self.winfo_screenheight()
  42.         geometry = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 2)
  43.         self.geometry(geometry)
  44.         self.resizable(width=False, height=False)
  45.         self.iconbitmap('logo.ico')
  46.  
  47.     def __tk_label_title(self):
  48.         label = Label(self, text="学生成绩管理系统", anchor="center")
  49.         label.place(x=0, y=0, width=800, height=100)
  50.         return label
  51.  
  52.     def __tk_label_current_user(self):
  53.         label = Label(self, text="当前用户：admin", anchor="center")
  54.         label.place(x=800, y=70, width=150, height=30)
  55.         return label
  56.  
  57.     def __tk_button_logout_user(self):
  58.         btn = Button(self, text="退出")
  59.         btn.place(x=950, y=70, width=50, height=30)
  60.         return btn
  61.  
  62.  
  63. class Frame_content(Notebook):
  64.     def __init__(self, parent):
  65.         super().__init__(parent)
  66.         self.__frame()
  67.  
  68.     def __frame(self):
  69.         self.tk_tabs_content_0 = Frame_content_0(self)
  70.         self.add(self.tk_tabs_content_0, text="个人资料")
  71.  
  72.         self.tk_tabs_content_1 = Frame_content_1(self)
  73.         self.add(self.tk_tabs_content_1, text="学生查询")
  74.  
  75.         self.tk_tabs_content_2 = Frame_content_2(self)
  76.         self.add(self.tk_tabs_content_2, text="成绩查询")
  77.  
  78.         self.tk_tabs_content_3 = Frame_content_3(self)
  79.         self.add(self.tk_tabs_content_3, text="成绩分析")
  80.  
  81.         self.tk_tabs_content_4 = Frame_content_4(self)
  82.         self.add(self.tk_tabs_content_4, text="修改密码")
  83.  
  84.         self.tk_tabs_content_5 = Frame_content_5(self)
  85.         self.add(self.tk_tabs_content_5, text="课程管理")
  86.  
  87.         self.place(x=0, y=100, width=1000, height=500)
  88.  
  89.  
  90. class Frame_content_0(Frame):
  91.     def __init__(self, parent):
  92.         super().__init__(parent)
  93.         self.__frame()
  94.         self.tk_label_tea_number = self.__tk_label_tea_number()
  95.         self.tk_input_tea_number = self.__tk_input_tea_number()
  96.         self.tk_label_tea_name = self.__tk_label_tea_name()
  97.         self.tk_input_tea_name = self.__tk_input_tea_name()
  98.         self.tk_label_tea_gender = self.__tk_label_tea_gender()
  99.         self.tk_select_tea_gender = self.__tk_select_tea_gender()
  100.         self.tk_label_tea_identity = self.__tk_label_tea_identity()
  101.         self.tk_input_tea_identity = self.__tk_input_tea_identity()
  102.         self.tk_label_tea_email = self.__tk_label_tea_email()
  103.         self.tk_input_tea_email = self.__tk_input_tea_email()
  104.         self.tk_button_tea_update = self.__tk_button_tea_update()
  105.         self.tk_button_tea_reset = self.__tk_button_tea_reset()
  106.  
  107.     def __frame(self):
  108.         self.place(x=0, y=100, width=1000, height=500)
  109.  
  110.     def __tk_label_tea_number(self):
  111.         label = Label(self, text="工号", anchor="e")
  112.         label.place(x=360, y=40, width=100, height=30)
  113.         return label
  114.  
  115.     def __tk_input_tea_number(self):
  116.         self.tea_number = StringVar(self)
  117.         ipt = Entry(self,  text=self.tea_number)
  118.         ipt.place(x=490, y=40, width=150, height=30)
  119.         ipt.config(stat='disable')
  120.         return ipt
  121.  
  122.     def __tk_label_tea_name(self):
  123.         label = Label(self, text="姓名", anchor="e")
  124.         label.place(x=360, y=110, width=100, height=30)
  125.         return label
  126.  
  127.     def __tk_input_tea_name(self):
  128.         self.tea_name = StringVar(self)
  129.         ipt = Entry(self,  text=self.tea_name)
  130.         ipt.place(x=490, y=110, width=150, height=30)
  131.         return ipt
  132.  
  133.     def __tk_label_tea_gender(self):
  134.         label = Label(self, text="性别", anchor="e")
  135.         label.place(x=360, y=180, width=100, height=30)
  136.         return label
  137.  
  138.     def __tk_select_tea_gender(self):
  139.         cb = Combobox(self, state='readonly')
  140.         cb['values'] = ("男", "女")
  141.         cb.place(x=490, y=180, width=150, height=30)
  142.         return cb
  143.  
  144.     def __tk_label_tea_identity(self):
  145.         label = Label(self, text="身份证号", anchor="e")
  146.         label.place(x=360, y=250, width=100, height=30)
  147.         return label
  148.  
  149.     def __tk_input_tea_identity(self):
  150.         self.tea_identify = StringVar(self)
  151.         ipt = Entry(self,  text=self.tea_identify)
  152.         ipt.place(x=490, y=250, width=150, height=30)
  153.         return ipt
  154.  
  155.     def __tk_label_tea_email(self):
  156.         label = Label(self, text="电子邮箱", anchor="e")
  157.         label.place(x=360, y=320, width=100, height=30)
  158.         return label
  159.  
  160.     def __tk_input_tea_email(self):
  161.         self.tea_email = StringVar(self)
  162.         ipt = Entry(self,  text=self.tea_email)
  163.         ipt.place(x=490, y=320, width=150, height=30)
  164.         return ipt
  165.  
  166.     def __tk_button_tea_update(self):
  167.         btn = Button(self, text="修改")
  168.         btn.place(x=400, y=390, width=80, height=30)
  169.         return btn
  170.  
  171.     def __tk_button_tea_reset(self):
  172.         btn = Button(self, text="重置")
  173.         btn.place(x=520, y=390, width=80, height=30)
  174.         return btn
  175.  
  176.  
  177. class Frame_content_1(Frame):
  178.     def __init__(self, parent):
  179.         super().__init__(parent)
  180.         self.__frame()
  181.         self.tk_table_student_query = self.__tk_table_student_query()
  182.         self.tk_input_stu_name = self.__tk_input_stu_name()
  183.         self.tk_select_box_stu_gender = self.__tk_select_box_stu_gender()
  184.         self.tk_button_stu_search = self.__tk_button_stu_search()
  185.         self.tk_button_addStudent = self.__tk_button_addStudent()
  186.         self.tk_button_delete_student = self.__tk_button_delete_student()
  187.         self.tk_button_stu_refresh = self.__tk_button_stu_refresh()
  188.         self.tk_button_studentinfo_export = self.__tk_button_studentinfo_export()
  189.  
  190.     def __frame(self):
  191.         self.place(x=0, y=100, width=1000, height=500)
  192.  
  193.     def __tk_table_student_query(self):
  194.         # 表头字段 表头宽度
  195.         self.tk_table_student_manage_columns = {"ID": 50, "学号": 100, "姓名": 150, '性别': 100, '身份证号': 300, '班级': 100, '邮箱': 200}
  196.         # 初始化表格 表格是基于Treeview，tkinter本身没有表格。show="headings" 为隐藏首列。
  197.         tk_table = Treeview(self, show="headings", columns=list(self.tk_table_student_manage_columns))
  198.         for text, width in self.tk_table_student_manage_columns.items():  # 批量设置列属性
  199.             tk_table.heading(text, text=text, anchor='center')
  200.             tk_table.column(text, anchor='center', width=width, stretch=False)  # stretch 不自动拉伸
  201.  
  202.         # 插入数据示例
  203.       
  204.         #
  205.         # # 导入初始数据
  206.         self.tk_student_table_dataset = Dao.getAllStudents()
  207.         if self.tk_student_table_dataset.get("code") == 0:
  208.             if self.tk_student_table_dataset.get("data"):
  209.                 print(self.tk_student_table_dataset.get("data"))
  210.                 for values in self.tk_student_table_dataset.get("data"):
  211.                     tk_table.insert('', END, values=list(values.values()))
  212.             else:
  213.                 print("未查询到数据！")
  214.         else:
  215.             print("数据查询异常！")
  216.  
  217.         tk_table.place(x=0, y=60, width=1000, height=415)
  218.         return tk_table
  219.  
  220.     def __tk_input_stu_name(self):
  221.         ipt = Entry(self)
  222.         ipt.place(x=360, y=10, width=150, height=30)
  223.         return ipt
  224.  
  225.     def __tk_select_box_stu_gender(self):
  226.         cb = Combobox(self, state="readonly")
  227.         cb['values'] = ("请选择性别", "男", "女")
  228.         cb.place(x=540, y=10, width=150, height=30)
  229.         cb.current(0)
  230.         return cb
  231.  
  232.     def __tk_button_stu_search(self):
  233.         btn = Button(self, text="搜索")
  234.         btn.place(x=720, y=10, width=70, height=30)
  235.         return btn
  236.  
  237.     def __tk_button_stu_refresh(self):
  238.         btn = Button(self, text="刷新")
  239.         btn.place(x=820, y=10, width=70, height=30)
  240.         return btn
  241.  
  242.     def __tk_button_addStudent(self):
  243.         btn = Button(self, text="添加学生")
  244.         btn.place(x=50, y=10, width=100, height=30)
  245.         return btn
  246.  
  247.     def __tk_button_delete_student(self):
  248.         btn = Button(self, text="删除学生")
  249.         btn.place(x=180, y=10, width=100, height=30)
  250.         return btn
  251.  
  252.     def __tk_button_studentinfo_export(self):
  253.         btn = Button(self, text="导出")
  254.         btn.place(x=920, y=10, width=50, height=30)
  255.         return btn
  256.  
  257.  
  258. class Frame_content_2(Frame):
  259.     def __init__(self, parent):
  260.         super().__init__(parent)
  261.         self.__frame()
  262.         self.tk_table_stu_score = self.__tk_table_stu_score()
  263.         self.tk_button_add_score = self.__tk_button_add_score()
  264.         self.tk_button_delete_score = self.__tk_button_delete_score()
  265.         self.tk_select_box_score_course_name = self.__tk_select_box_score_course_name()
  266.         self.tk_select_box_score_nature = self.__tk_select_box_score_nature()
  267.         self.tk_select_box_score_department = self.__tk_select_box_score_department()
  268.         self.tk_button_stu_score_search = self.__tk_button_stu_score_search()
  269.         self.tk_button_stu_score_export = self.__tk_button_stu_score_export()
  270.  
  271.     def __frame(self):
  272.         self.place(x=0, y=100, width=1000, height=500)
  273.  
  274.     def __tk_table_stu_score(self):
  275.         # 表头字段 表头宽度
  276.         self.tk_table_stu_score_columns = {"#": 50, "学号": 70, "姓名": 80, "课程名称": 200, "课程性质": 100, "开课学院": 270, "考试方式": 80, "学分": 50, "成绩": 100}
  277.         # 初始化表格 表格是基于Treeview，tkinter本身没有表格。show="headings" 为隐藏首列。
  278.         tk_table = Treeview(self, show="headings", columns=list(self.tk_table_stu_score_columns))
  279.         for text, width in self.tk_table_stu_score_columns.items():  # 批量设置列属性
  280.             tk_table.heading(text, text=text, anchor='center')
  281.             tk_table.column(text, anchor='center', width=width, stretch=False)  # stretch 不自动拉伸
  282.  
  283.         # 插入数据示例
  284.         self.tk_score_table_dataset = Dao.searchStudentScore()
  285.         # 导入初始数据
  286.         if self.tk_score_table_dataset.get("code") == 0 and self.tk_score_table_dataset.get("data"):
  287.             for data in self.tk_score_table_dataset.get("data"):
  288.                 tk_table.insert('', END, values=list(data.values()))
  289.  
  290.         tk_table.place(x=0, y=60, width=1000, height=415)
  291.         return tk_table
  292.  
  293.     def __tk_button_add_score(self):
  294.         btn = Button(self, text="添加成绩")
  295.         btn.place(x=50, y=10, width=100, height=30)
  296.         return btn
  297.  
  298.     def __tk_button_delete_score(self):
  299.         btn = Button(self, text="删除成绩")
  300.         btn.place(x=170, y=10, width=100, height=30)
  301.         return btn
  302.  
  303.     def __tk_select_box_score_course_name(self):
  304.         cb = Combobox(self, state="readonly")
  305.         values = ["请选择课程名称"]
  306.         for i in Dao.getAllCourses().get("data"):
  307.             values.append(i.get("cname"))
  308.         cb['values'] = values
  309.         cb.current(0)
  310.         cb.place(x=290, y=10, width=150, height=30)
  311.         return cb
  312.  
  313.     def __tk_select_box_score_nature(self):
  314.         cb = Combobox(self, state="readonly")
  315.         values = ["请选择课程性质"]
  316.         for i in Dao.getDataDictByType("nature").get("data"):
  317.             values.append(i.get("v"))
  318.         cb['values'] = values
  319.         cb.current(0)
  320.         cb.place(x=460, y=10, width=150, height=30)
  321.         return cb
  322.  
  323.     def __tk_select_box_score_department(self):
  324.         cb = Combobox(self, state="readonly")
  325.         values = ["请选择开课学院"]
  326.         for i in Dao.getAllDepartments().get("data"):
  327.             values.append(i.get("v"))
  328.         cb['values'] = values
  329.         cb.current(0)
  330.         cb.place(x=630, y=10, width=150, height=30)
  331.         return cb
  332.  
  333.     def __tk_button_stu_score_search(self):
  334.         btn = Button(self, text="搜索")
  335.         btn.place(x=800, y=10, width=70, height=30)
  336.         return btn
  337.  
  338.     def __tk_button_stu_score_export(self):
  339.         btn = Button(self, text="导出")
  340.         btn.place(x=890, y=10, width=70, height=30)
  341.         return btn
  342.  
  343.  
  344. class Frame_content_3(Frame):
  345.     def __init__(self, parent):
  346.         super().__init__(parent)
  347.         self.__frame()
  348.         self.tk_button_pieChart = self.__tk_button_pieChart()
  349.         self.tk_button_columnChart = self.__tk_button_columnChart()
  350.         self.tk_button_paratacticColumnChart = self.__tk_button_paratacticColumnChart()
  351.  
  352.     def __frame(self):
  353.         self.place(x=0, y=100, width=1000, height=500)
  354.  
  355.     def __tk_button_columnChart(self):
  356.         btn = Button(self, text="课程成绩分析")
  357.         btn.place(x=80, y=50, width=150, height=70)
  358.         return btn
  359.  
  360.     def __tk_button_paratacticColumnChart(self):
  361.         btn = Button(self, text="班级成绩分析")
  362.         btn.place(x=420, y=50, width=150, height=70)
  363.         return btn
  364.  
  365.     def __tk_button_pieChart(self):
  366.         btn = Button(self, text="综合成绩评定")
  367.         btn.place(x=760, y=50, width=150, height=70)
  368.         return btn
  369.  
  370.  
  371. class Frame_content_4(Frame):
  372.     def __init__(self, parent):
  373.         super().__init__(parent)
  374.         self.__frame()
  375.         self.tk_label_original_pwd = self.__tk_label_original_pwd()
  376.         self.tk_input_original_pwd = self.__tk_input_original_pwd()
  377.         self.tk_label_new_pwd = self.__tk_label_new_pwd()
  378.         self.tk_input_new_pwd = self.__tk_input_new_pwd()
  379.         self.tk_label_confirm_pwd = self.__tk_label_confirm_pwd()
  380.         self.tk_input_confirm_pwd = self.__tk_input_confirm_pwd()
  381.         self.tk_button_update_tea_pwd = self.__tk_button_update_tea_pwd()
  382.  
  383.     def __frame(self):
  384.         self.place(x=0, y=100, width=1000, height=500)
  385.  
  386.     def __tk_label_original_pwd(self):
  387.         label = Label(self, text="原密码", anchor="e")
  388.         label.place(x=360, y=40, width=100, height=30)
  389.         return label
  390.  
  391.     def __tk_input_original_pwd(self):
  392.         ipt = Entry(self, show='*')
  393.         ipt.place(x=490, y=40, width=150, height=30)
  394.         return ipt
  395.  
  396.     def __tk_label_new_pwd(self):
  397.         label = Label(self, text="新密码", anchor="e")
  398.         label.place(x=360, y=110, width=100, height=30)
  399.         return label
  400.  
  401.     def __tk_input_new_pwd(self):
  402.         ipt = Entry(self, show='*')
  403.         ipt.place(x=490, y=110, width=150, height=30)
  404.         return ipt
  405.  
  406.     def __tk_label_confirm_pwd(self):
  407.         label = Label(self, text="确认密码", anchor="e")
  408.         label.place(x=360, y=180, width=100, height=30)
  409.         return label
  410.  
  411.     def __tk_input_confirm_pwd(self):
  412.         ipt = Entry(self, show='*')
  413.         ipt.place(x=490, y=180, width=150, height=30)
  414.         return ipt
  415.  
  416.     def __tk_button_update_tea_pwd(self):
  417.         btn = Button(self, text="修改")
  418.         btn.place(x=450, y=260, width=100, height=30)
  419.         return btn
  420.  
  421.  
  422. class Frame_content_5(Frame):
  423.     def __init__(self, parent):
  424.         super().__init__(parent)
  425.         self.__frame()
  426.         self.tk_table_course_manage = self.__tk_table_course_manage()
  427.         self.tk_button_add_course = self.__tk_button_add_course()
  428.         self.tk_button_delete_course = self.__tk_button_delete_course()
  429.         self.tk_select_box_course_department = self.__tk_select_box_course_department()
  430.         self.tk_select_box_course_exam_method = self.__tk_select_box_course_exam_method()
  431.         self.tk_button_course_search = self.__tk_button_course_search()
  432.         self.tk_button_course_export = self.__tk_button_course_export()
  433.  
  434.     def __frame(self):
  435.         self.place(x=0, y=100, width=1000, height=500)
  436.  
  437.     def __tk_table_course_manage(self):
  438.         # 表头字段 表头宽度
  439.         self.tk_table_course_manage_columns = {"课程号": 100, "课程名称": 200, "学分": 100, "课程性质": 200, "开课学院": 300, "考试方式": 100}
  440.         # 初始化表格 表格是基于Treeview，tkinter本身没有表格。show="headings" 为隐藏首列。
  441.         tk_table = Treeview(self, show="headings", columns=list(self.tk_table_course_manage_columns))
  442.         for text, width in self.tk_table_course_manage_columns.items():  # 批量设置列属性
  443.             tk_table.heading(text, text=text, anchor='center')
  444.             tk_table.column(text, anchor='center', width=width, stretch=False)  # stretch 不自动拉伸
  445.  
  446.         # 插入数据示例
  447.         self.tk_course_table_dataset = Dao.getAllCourses()
  448.         # 导入初始数据
  449.         if self.tk_course_table_dataset.get("code") == 0 and self.tk_course_table_dataset.get("data"):
  450.             for data in self.tk_course_table_dataset.get("data"):
  451.                 tk_table.insert('', END, values=list(data.values()))
  452.  
  453.         tk_table.place(x=0, y=60, width=1000, height=415)
  454.         return tk_table
  455.  
  456.     def __tk_button_add_course(self):
  457.         btn = Button(self, text="添加课程")
  458.         btn.place(x=50, y=10, width=100, height=30)
  459.         return btn
  460.  
  461.     def __tk_button_delete_course(self):
  462.         btn = Button(self, text="删除课程")
  463.         btn.place(x=180, y=10, width=100, height=30)
  464.         return btn
  465.  
  466.     def __tk_select_box_course_department(self):
  467.         cb = Combobox(self, state="readonly")
  468.         values = ["请选择开课学院"]
  469.         for i in Dao.getAllDepartments().get("data"):
  470.             values.append(i.get("v"))
  471.         cb['values'] = values
  472.         cb.current(0)
  473.         cb.place(x=310, y=10, width=150, height=30)
  474.         return cb
  475.  
  476.     def __tk_select_box_course_exam_method(self):
  477.         cb = Combobox(self, state="readonly")
  478.         values = ["请选择考试方式"]
  479.         for i in Dao.getDataDictByType("exammethod").get("data"):
  480.             values.append(i.get("v"))
  481.         cb['values'] = values
  482.         cb.current(0)
  483.         cb.place(x=490, y=10, width=150, height=30)
  484.         return cb
  485.  
  486.     def __tk_button_course_search(self):
  487.         btn = Button(self, text="搜索")
  488.         btn.place(x=670, y=10, width=100, height=30)
  489.         return btn
  490.  
  491.     def __tk_button_course_export(self):
  492.         btn = Button(self, text="导出")
  493.         btn.place(x=800, y=10, width=100, height=30)
  494.         return btn
  495.  
  496.  
  497. class Win(WinGUI):
  498.     def __init__(self, current_user):
  499.         super().__init__()
  500.         self.__event_bind()
  501.         self.current_user = current_user
  502.         self.uid = current_user.get("uid")
  503.         self.tk_label_current_user['text'] = "当前用户：" + current_user.get("uname")
  504.         self.tk_tabs_content.tk_tabs_content_0.tea_number.set(current_user.get("uid"))
  505.         self.tk_tabs_content.tk_tabs_content_0.tea_name.set(current_user.get("uname"))
  506.         self.tk_tabs_content.tk_tabs_content_0.tk_select_tea_gender.current(0 if current_user.get("ugender") == '男' else 1)
  507.         self.tk_tabs_content.tk_tabs_content_0.tea_identify.set(current_user.get("uidentify"))
  508.         self.tk_tabs_content.tk_tabs_content_0.tea_email.set(current_user.get("uemail"))
  509.  
  510.     def logout(self):
  511.         try:
  512.             self.updateStudent.destroy()
  513.             self.addInfo.destroy()
  514.             self.delete.destroy()
  515.         except Exception as e:
  516.             print(e)
  517.         messagebox.showwarning('提示', '欢迎下次使用！')
  518.         self.destroy()
  519.         login = Login.Win()
  520.         login.mainloop()
  521.  
  522.     def updateStudentInfo(self, evt):
  523.         current_focus = self.tk_tabs_content.tk_tabs_content_1.tk_table_student_query.focus()
  524.         current_studentinfo = self.tk_tabs_content.tk_tabs_content_1.tk_table_student_query.set(current_focus)
  525.         current_uid = current_studentinfo.get('学号')
  526.         self.updateStudent = UpdateStudentPage.Win(current_uid)
  527.         self.updateStudent.mainloop()
  528.         print("<<TreeviewSelect>>事件未处理", evt)
  529.  
  530.     def updateTeacherInfo(self, evt):
  531.         __tea_name = self.tk_tabs_content.tk_tabs_content_0.tk_input_tea_name.get()
  532.         __tea_gender = self.tk_tabs_content.tk_tabs_content_0.tk_select_tea_gender.get()
  533.         __tea_identify = self.tk_tabs_content.tk_tabs_content_0.tk_input_tea_identity.get()
  534.         __tea_email = self.tk_tabs_content.tk_tabs_content_0.tk_input_tea_email.get()
  535.         if not __tea_name or not __tea_gender or not __tea_identify or not __tea_email:
  536.             messagebox.showinfo("提示", "必填项不能为空！")
  537.             return
  538.         if not re.match(r'^[1-9]\d{5}(18|19|20)\d{2}((0[1-9])|(1[0-2]))(([0-2][1-9])|10|20|30|31)\d{3}[0-9Xx]$',
  539.                         __tea_identify):
  540.             messagebox.showinfo("提示", "身份证格式不合法！")
  541.             return
  542.         if not re.match(r'^[\w-]+(\.[\w-]+)*@[\w-]+(\.[\w-]+)+$', __tea_email):
  543.             messagebox.showinfo("提示", "电子邮箱格式不合法！")
  544.             return
  545.         res = Dao.updateUser(self.uid, __tea_name, __tea_gender, __tea_identify, 0, __tea_email)
  546.         messagebox.showinfo("提示", res.get("msg"))
  547.         self.tk_label_current_user['text'] = "当前用户：" + __tea_name
  548.         print("更新教师信息", evt)
  549.  
  550.     def resetTeacherInfo(self, evt):
  551.         self.tk_tabs_content.tk_tabs_content_0.tea_name.set(self.current_user.get("uname"))
  552.         self.tk_tabs_content.tk_tabs_content_0.tk_select_tea_gender.current(0 if self.current_user.get("ugender") == '男' else 1)
  553.         self.tk_tabs_content.tk_tabs_content_0.tea_identify.set(self.current_user.get("uidentify"))
  554.         self.tk_tabs_content.tk_tabs_content_0.tea_email.set(self.current_user.get("uemail"))
  555.         print("重置教师信息", evt)
  556.  
  557.     def searchStudentInfo(self, evt):
  558.         for _ in map(self.tk_tabs_content.tk_tabs_content_1.tk_table_student_query.delete,
  559.                      self.tk_tabs_content.tk_tabs_content_1.tk_table_student_query.get_children("")):
  560.             pass
  561.         value = self.tk_tabs_content.tk_tabs_content_1.tk_input_stu_name.get()
  562.         num = self.tk_tabs_content.tk_tabs_content_1.tk_select_box_stu_gender.get()
  563.         print(num, value)
  564.         if num == '请选择性别':
  565.             result = Dao.searchStudents(value, '')
  566.         else:
  567.             result = Dao.searchStudents(value, num)
  568.         if result.get("code") == 0:
  569.             if result.get("data"):
  570.                 # print(result.get("data"))
  571.                 for values in result.get("data"):
  572.                     self.tk_tabs_content.tk_tabs_content_1.tk_table_student_query.insert('', END, values=list(values.values()))
  573.             else:
  574.                 print("未查询到数据！")
  575.         else:
  576.             print("数据查询异常！")
  577.         print("搜索学生信息", evt)
  578.  
  579.     def updateStudentScore(self, evt):
  580.         current_item = self.tk_tabs_content.tk_tabs_content_2.tk_table_stu_score.set(self.tk_tabs_content.tk_tabs_content_2.tk_table_stu_score.focus())
  581.         data = {
  582.             "uid": current_item.get("学号"),
  583.             "cname": current_item.get("课程名称"),
  584.             "score": current_item.get("成绩")
  585.         }
  586.         updateScorePage = UpdateScorePage.Win(data)
  587.         updateScorePage.mainloop()
  588.         print("修改学生课程成绩", evt)
  589.  
  590.     def searchStuScore(self, evt):
  591.         __score_manage = self.tk_tabs_content.tk_tabs_content_2
  592.         __course_name = __score_manage.tk_select_box_score_course_name.get()
  593.         __course_nature = __score_manage.tk_select_box_score_nature.get()
  594.         __course_department = __score_manage.tk_select_box_score_department.get()
  595.         if __score_manage.tk_select_box_score_course_name.current() == 0:
  596.             __course_name = ''
  597.         if __score_manage.tk_select_box_score_nature.current() == 0:
  598.             __course_nature = ''
  599.         if __score_manage.tk_select_box_score_department.current() == 0:
  600.             __course_department = ''
  601.         for _ in map(__score_manage.tk_table_stu_score.delete, __score_manage.tk_table_stu_score.get_children("")):
  602.             pass
  603.         __score_manage.tk_score_table_dataset = Dao.searchStudentScore(__course_name, __course_nature, __course_department)
  604.         # 导入初始数据
  605.         if __score_manage.tk_score_table_dataset.get("code") == 0 and __score_manage.tk_score_table_dataset.get("data"):
  606.             for data in __score_manage.tk_score_table_dataset.get("data"):
  607.                 __score_manage.tk_table_stu_score.insert('', END, values=list(data.values()))
  608.         __score_manage.tk_select_box_score_course_name.current(0)
  609.         __score_manage.tk_select_box_score_nature.current(0)
  610.         __score_manage.tk_select_box_score_department.current(0)
  611.         print("搜索学生成绩！")
  612.  
  613.     def exportStuScore(self, evt):
  614.         path = filedialog.askdirectory()
  615.         try:
  616.             book = openpyxl.Workbook()
  617.             sheet = book.active
  618.             fff = list(self.tk_tabs_content.tk_tabs_content_2.tk_table_stu_score_columns.keys())  # 获取表头信息
  619.             sheet.append(fff)
  620.             dataset = [list(data_item.values()) for data_item in
  621.                        self.tk_tabs_content.tk_tabs_content_2.tk_score_table_dataset.get("data")]
  622.             print(dataset)
  623.             for i in dataset:
  624.                 sheet.append(i)
  625.             book.save(f"{path}/student_score.xlsx")
  626.             messagebox.showinfo("提示", "导出成功！")
  627.         except Exception as e:
  628.             messagebox.showinfo("提示", "导出失败！")
  629.             print(e)
  630.         print("导出学生成绩！", evt)
  631.  
  632.     def addStudentInfo(self, evt):
  633.         self.addInfo = AddStudentPage.Win()
  634.         self.addInfo.mainloop()
  635.         print("<Button-1>事件未处理", evt)
  636.  
  637.     def deleteStudentInfo(self, evt):
  638.         self.delete = DeleteStudentPage.Win()
  639.         self.delete.mainloop()
  640.         print("<Button-1>事件未处理", evt)
  641.  
  642.     def addStudentScore(self, evt):
  643.         addScorePage = AddScorePage.Win()
  644.         addScorePage.mainloop()
  645.  
  646.     def deleteStudentScore(self, evt):
  647.         deleteScorePage = DeleteScorePage.Win()
  648.         deleteScorePage.mainloop()
  649.         print("删除学生成绩", evt)
  650.  
  651.     def updateTeacherPassword(self, evt):
  652.         __original_pwd = self.tk_tabs_content.tk_tabs_content_4.tk_input_original_pwd.get()
  653.         __new_pwd = self.tk_tabs_content.tk_tabs_content_4.tk_input_new_pwd.get()
  654.         __confirm_pwd = self.tk_tabs_content.tk_tabs_content_4.tk_input_confirm_pwd.get()
  655.         if __original_pwd == '' or __new_pwd == '' or __confirm_pwd == '':
  656.             messagebox.showwarning("提示", "必填项未填写！")
  657.             return
  658.         if not re.match(r"^[0-9a-zA-Z~!@#$%^&*._?]{6,18}$", __original_pwd) \
  659.                 or not re.match(r"^[0-9a-zA-Z~!@#$%^&*._?]{6,18}$", __new_pwd) \
  660.                 or not re.match(r"^[0-9a-zA-Z~!@#$%^&*._?]{6,18}$", __confirm_pwd):
  661.             messagebox.showwarning("提示", "密码格式应为6-18位数字、字母、特殊字符的组合！")
  662.             return
  663.         if __new_pwd != __confirm_pwd:
  664.             messagebox.showwarning("提示", "两次密码输入不一致")
  665.             return
  666.         res = Dao.updatePassword(self.uid, __original_pwd, __new_pwd)
  667.         messagebox.showinfo("提示", res.get("msg"))
  668.         if res.get("code") == 0:
  669.             self.destroy()
  670.             login = Login.Win()
  671.             login.mainloop()
  672.         print("修改教师密码", evt)
  673.  
  674.     def addCourseInfo(self, evt):
  675.         addCoursePage = AddCoursePage.Win()
  676.         addCoursePage.mainloop()
  677.         print("添加成绩！")
  678.  
  679.     def deleteCourseInfo(self, evt):
  680.         deleteCoursePage = DeleteCoursePage.Win()
  681.         deleteCoursePage.mainloop()
  682.         print("删除课程！")
  683.  
  684.     def searchCourseInfo(self, evt):
  685.         __course_manage = self.tk_tabs_content.tk_tabs_content_5
  686.         __department = __course_manage.tk_select_box_course_department.get()
  687.         __exammethod = __course_manage.tk_select_box_course_exam_method.get()
  688.         if __course_manage.tk_select_box_course_department.current() == 0:
  689.             __department = ''
  690.         if __course_manage.tk_select_box_course_exam_method.current() == 0:
  691.             __exammethod = ''
  692.         for _ in map(__course_manage.tk_table_course_manage.delete, __course_manage.tk_table_course_manage.get_children("")):
  693.             pass
  694.         self.tk_tabs_content.tk_tabs_content_5.tk_course_table_dataset = Dao.searchCourses(__department, __exammethod)
  695.         # 导入初始数据
  696.         if self.tk_tabs_content.tk_tabs_content_5.tk_course_table_dataset.get("code") == 0 and self.tk_tabs_content.tk_tabs_content_5.tk_course_table_dataset.get("data"):
  697.             for data in self.tk_tabs_content.tk_tabs_content_5.tk_course_table_dataset.get("data"):
  698.                 __course_manage.tk_table_course_manage.insert('', END, values=list(data.values()))
  699.         __course_manage.tk_select_box_course_department.current(0)
  700.         __course_manage.tk_select_box_course_exam_method.current(0)
  701.         print("查询课程！")
  702.  
  703.     def updateCourseInfo(self, evt):
  704.         __focus = self.tk_tabs_content.tk_tabs_content_5.tk_table_course_manage.focus()
  705.         current_item = self.tk_tabs_content.tk_tabs_content_5.tk_table_course_manage.set(__focus)
  706.         __cid = current_item.get("课程号")
  707.         self.updateCoursePage = UpdateCoursePage.Win(__cid)
  708.         self.updateCoursePage.mainloop()
  709.         print("更新课程信息！")
  710.  
  711.     def exportCourseInfo(self, evt):
  712.         path = filedialog.askdirectory()
  713.         try:
  714.             book = openpyxl.Workbook()
  715.             sheet = book.active
  716.             fff = list(self.tk_tabs_content.tk_tabs_content_5.tk_table_course_manage_columns.keys())  # 获取表头信息
  717.             sheet.append(fff)
  718.             dataset = [list(data_item.values()) for data_item in self.tk_tabs_content.tk_tabs_content_5.tk_course_table_dataset.get("data")]
  719.             print(dataset)
  720.             for i in dataset:
  721.                 sheet.append(i)
  722.             book.save(path + "/course_info.xlsx")
  723.             messagebox.showinfo("提示", "导出成功！")
  724.         except Exception as e:
  725.             messagebox.showinfo("提示", "导出失败！")
  726.             print(e)
  727.  
  728.     def logout_user(self, evt):
  729.         messagebox.showwarning('提示', '欢迎下次使用！')
  730.         self.destroy()
  731.         login = Login.Win()
  732.         login.mainloop()
  733.  
  734.     def studentinfo_refresh(self, evt):
  735.         # 删除原结点，加入新结点
  736.         for _ in map(self.tk_tabs_content.tk_tabs_content_1.tk_table_student_query.delete,
  737.                      self.tk_tabs_content.tk_tabs_content_1.tk_table_student_query.get_children("")):
  738.             pass
  739.         result = Dao.getAllStudents()
  740.         if result.get("code") == 0:
  741.             if result.get("data"):
  742.                 # print(result.get("data"))
  743.                 for values in result.get("data"):
  744.                     self.tk_tabs_content.tk_tabs_content_1.tk_table_student_query.insert('', END,
  745.                                                                                          values=list(values.values()))
  746.             else:
  747.                 print("未查询到数据！")
  748.         else:
  749.             print("数据查询异常！")
  750.  
  751.     def studentinfo_export(self, evt):
  752.         path = filedialog.askdirectory()
  753.         try:
  754.             book = openpyxl.Workbook()
  755.             sheet = book.active
  756.             fff = list(self.tk_tabs_content.tk_tabs_content_1.tk_table_student_manage_columns.keys())  # 获取表头信息
  757.             sheet.append(fff)
  758.             dataset = [list(data_item.values()) for data_item in
  759.                        self.tk_tabs_content.tk_tabs_content_1.tk_student_table_dataset.get("data")]
  760.             print(dataset)
  761.             for i in dataset:
  762.                 sheet.append(i)
  763.             book.save(path + "/student_info.xlsx")
  764.             messagebox.showinfo("提示", "导出成功！")
  765.         except Exception as e:
  766.             messagebox.showinfo("提示", "导出失败！")
  767.             print(e)
  768.  
  769.     def columnChart(self, evt):
  770.         courseScoreAnalysis = CourseScoreAnalysis.Win()
  771.         courseScoreAnalysis.mainloop()
  772.  
  773.     def paratacticColumnChart(self, evt):
  774.         classGradeAnalysis = ClassGradeAnalysis.Win()
  775.         classGradeAnalysis.mainloop()
  776.  
  777.     def pieChart(self, evt):
  778.         comprehensivePerformanceEvaluation = ComprehensivePerformanceEvaluation.Win()
  779.         comprehensivePerformanceEvaluation.mainloop()
  780.  
  781.  
  782.     def __event_bind(self):
  783.         self.protocol('WM_DELETE_WINDOW', self.logout)
  784.         self.tk_tabs_content.tk_tabs_content_1.tk_table_student_query.bind('<<TreeviewSelect>>', self.updateStudentInfo)
  785.         self.tk_tabs_content.tk_tabs_content_0.tk_button_tea_update.bind('<Button-1>', self.updateTeacherInfo)
  786.         self.tk_tabs_content.tk_tabs_content_0.tk_button_tea_reset.bind('<Button-1>', self.resetTeacherInfo)
  787.         self.tk_tabs_content.tk_tabs_content_1.tk_button_stu_search.bind('<Button-1>', self.searchStudentInfo)
  788.         self.tk_tabs_content.tk_tabs_content_1.tk_button_addStudent.bind('<Button-1>', self.addStudentInfo)
  789.         self.tk_tabs_content.tk_tabs_content_1.tk_button_delete_student.bind('<Button-1>', self.deleteStudentInfo)
  790.         self.tk_tabs_content.tk_tabs_content_1.tk_button_stu_refresh.bind('<Button-1>', self.studentinfo_refresh)
  791.         self.tk_tabs_content.tk_tabs_content_1.tk_button_studentinfo_export.bind('<Button-1>', self.studentinfo_export)
  792.         self.tk_tabs_content.tk_tabs_content_2.tk_button_add_score.bind('<Button-1>', self.addStudentScore)
  793.         self.tk_tabs_content.tk_tabs_content_2.tk_button_delete_score.bind('<Button-1>', self.deleteStudentScore)
  794.         self.tk_tabs_content.tk_tabs_content_2.tk_table_stu_score.bind('<<TreeviewSelect>>', self.updateStudentScore)
  795.         self.tk_tabs_content.tk_tabs_content_2.tk_button_stu_score_search.bind('<Button-1>', self.searchStuScore)
  796.         self.tk_tabs_content.tk_tabs_content_2.tk_button_stu_score_export.bind('<Button-1>', self.exportStuScore)
  797.         self.tk_tabs_content.tk_tabs_content_3.tk_button_columnChart.bind('<Button-1>', self.columnChart)
  798.         self.tk_tabs_content.tk_tabs_content_3.tk_button_paratacticColumnChart.bind('<Button-1>', self.paratacticColumnChart)
  799.         self.tk_tabs_content.tk_tabs_content_3.tk_button_pieChart.bind('<Button-1>', self.pieChart)
  800.  
  801.         self.tk_tabs_content.tk_tabs_content_4.tk_button_update_tea_pwd.bind('<Button-1>', self.updateTeacherPassword)
  802.         self.tk_tabs_content.tk_tabs_content_5.tk_button_add_course.bind('<Button-1>', self.addCourseInfo)
  803.         self.tk_tabs_content.tk_tabs_content_5.tk_button_delete_course.bind('<Button-1>', self.deleteCourseInfo)
  804.         self.tk_tabs_content.tk_tabs_content_5.tk_button_course_search.bind('<Button-1>', self.searchCourseInfo)
  805.         self.tk_tabs_content.tk_tabs_content_5.tk_button_course_export.bind('<Button-1>', self.exportCourseInfo)
  806.         self.tk_tabs_content.tk_tabs_content_5.tk_table_course_manage.bind('<<TreeviewSelect>>', self.updateCourseInfo)
  807.         self.tk_button_logout_user.bind('<Button-1>', self.logout_user)

学生端登录页面效果：

4372cd6b99704ec4bc6aa215ec45f9da.png

学生端——成绩分析：

学生端——修改密码：

bc4900e878ee4e9c83d00a8bcb6cfc95.png

 学生端代码如下：

student.py:

  1. from tkinter import *
  2. from tkinter import messagebox, filedialog
  3. from tkinter.ttk import *
  4.  
  5. import numpy as np
  6. import openpyxl
  7. from matplotlib import pyplot as plt
  8.  
  9. import Dao
  10. import Login
  11.  
  12. global current_uid
  13.  
  14. class WinGUI(Tk):
  15.     def __init__(self):
  16.         super().__init__()
  17.         self.__win()
  18.         self.tk_label_title = self.__tk_label_title()
  19.         self.tk_label_current_user = self.__tk_label_current_user()
  20.         self.tk_tabs_content = Frame_content(self)
  21.         self.tk_button_logout = self.__tk_button_logout()
  22.  
  23.     def __win(self):
  24.         self.title("成绩查询")
  25.         # 设置窗口大小、居中
  26.         width = 1000
  27.         height = 600
  28.         screenwidth = self.winfo_screenwidth()
  29.         screenheight = self.winfo_screenheight()
  30.         geometry = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 2)
  31.         self.geometry(geometry)
  32.         self.resizable(width=False, height=False)
  33.         self.iconbitmap('logo.ico')
  34.  
  35.     def __tk_label_title(self):
  36.         label = Label(self, text="学生成绩管理系统", anchor="center")
  37.         label.place(x=0, y=0, width=800, height=100)
  38.         return label
  39.  
  40.     def __tk_label_current_user(self):
  41.         label = Label(self, text="当前用户：admin", anchor="center")
  42.         label.place(x=800, y=70, width=150, height=30)
  43.         return label
  44.  
  45.     def __tk_button_logout(self):
  46.         btn = Button(self, text="退出")
  47.         btn.place(x=950, y=70, width=50, height=30)
  48.         return btn
  49.  
  50.  
  51. class Frame_content(Notebook):
  52.     def __init__(self, parent):
  53.         super().__init__(parent)
  54.         self.__frame()
  55.  
  56.     def __frame(self):
  57.         self.tk_tabs_content_0 = Frame_content_0(self)
  58.         self.add(self.tk_tabs_content_0, text="个人资料")
  59.  
  60.         self.tk_tabs_content_1 = Frame_content_1(self)
  61.         self.add(self.tk_tabs_content_1, text="成绩查询")
  62.  
  63.         self.tk_tabs_content_3 = Frame_content_3(self)
  64.         self.add(self.tk_tabs_content_3, text="修改密码")
  65.  
  66.         self.place(x=0, y=100, width=1000, height=500)
  67.  
  68.  
  69. class Frame_content_0(Frame):
  70.     def __init__(self, parent):
  71.         super().__init__(parent)
  72.         self.__frame()
  73.         self.tk_label_stu_number = self.__tk_label_stu_number()
  74.         self.tk_input_stu_number = self.__tk_input_stu_number()
  75.         self.tk_label_stu_name = self.__tk_label_stu_name()
  76.         self.tk_input_stu_name = self.__tk_input_stu_name()
  77.         self.tk_label_stu_gender = self.__tk_label_stu_gender()
  78.         self.tk_tk_select_stu_gender = self.__tk_select_stu_gender()
  79.         self.tk_label_stu_identity = self.__tk_label_stu_identity()
  80.         self.tk_input_stu_identity = self.__tk_input_stu_identity()
  81.         self.tk_label_stu_email = self.__tk_label_stu_email()
  82.         self.tk_input_stu_email = self.__tk_input_stu_email()
  83.         self.tk_button_stu_update = self.__tk_button_stu_update()
  84.         self.tk_button_stu_reset = self.__tk_button_stu_reset()
  85.  
  86.     def __frame(self):
  87.         self.place(x=0, y=100, width=1000, height=500)
  88.  
  89.     def __tk_label_stu_number(self):
  90.         label = Label(self, text="学号", anchor="e")
  91.         label.place(x=360, y=40, width=100, height=30)
  92.         return label
  93.  
  94.     def __tk_input_stu_number(self):
  95.         self.student_number = StringVar(self)
  96.         ipt = Entry(self, text=self.student_number)
  97.         ipt.place(x=490, y=40, width=150, height=30)
  98.         ipt.config(stat='disable')
  99.         return ipt
  100.  
  101.     def __tk_label_stu_name(self):
  102.         label = Label(self, text="姓名", anchor="e")
  103.         label.place(x=360, y=110, width=100, height=30)
  104.         return label
  105.  
  106.     def __tk_input_stu_name(self):
  107.         self.student_name = StringVar(self)
  108.         ipt = Entry(self, text=self.student_name)
  109.         ipt.place(x=490, y=110, width=150, height=30)
  110.         return ipt
  111.  
  112.     def __tk_label_stu_gender(self):
  113.         label = Label(self, text="性别", anchor="e")
  114.         label.place(x=360, y=180, width=100, height=30)
  115.         return label
  116.  
  117.     def __tk_select_stu_gender(self):
  118.         cb = Combobox(self, state='readonly')
  119.         cb['values'] = ("男", "女")
  120.         cb.place(x=490, y=180, width=150, height=30)
  121.         return cb
  122.  
  123.     def __tk_label_stu_identity(self):
  124.         label = Label(self, text="身份证号", anchor="e")
  125.         label.place(x=360, y=250, width=100, height=30)
  126.         return label
  127.  
  128.     def __tk_input_stu_identity(self):
  129.         self.student_identify = StringVar(self)
  130.         ipt = Entry(self, text=self.student_identify)
  131.         ipt.place(x=490, y=250, width=150, height=30)
  132.         return ipt
  133.  
  134.     def __tk_label_stu_email(self):
  135.         label = Label(self, text="电子邮箱", anchor="e")
  136.         label.place(x=360, y=320, width=100, height=30)
  137.         return label
  138.  
  139.     def __tk_input_stu_email(self):
  140.         self.student_email = StringVar(self)
  141.         ipt = Entry(self, text=self.student_email)
  142.         ipt.place(x=490, y=320, width=150, height=30)
  143.         return ipt
  144.  
  145.     def __tk_button_stu_update(self):
  146.         btn = Button(self, text="修改")
  147.         btn.place(x=400, y=390, width=80, height=30)
  148.         return btn
  149.  
  150.     def __tk_button_stu_reset(self):
  151.         btn = Button(self, text="重置")
  152.         btn.place(x=520, y=390, width=80, height=30)
  153.         return btn
  154.  
  155.  
  156. class Frame_content_1(Frame):
  157.     def __init__(self, parent):
  158.         super().__init__(parent)
  159.         self.__frame()
  160.         self.tk_button_analysis = self.__tk_button_analysis()
  161.         self.tk_table_stu_score = self.__tk_table_stu_score()
  162.         self.tk_select_box_course_nature = self.__tk_select_box_course_nature()
  163.         self.tk_select_box_course_department = self.__tk_select_box_course_department()
  164.         self.tk_select_box_exam_method = self.__tk_select_box_exam_method()
  165.         self.tk_button_search = self.__tk_button_search()
  166.         self.tk_button_export = self.__tk_button_export()
  167.  
  168.     def __frame(self):
  169.         self.place(x=0, y=100, width=1000, height=500)
  170.  
  171.     def __tk_table_stu_score(self):
  172.         # 表头字段 表头宽度
  173.         self.tk_table_stu_score_columns = {"#": 50, "课程名称": 200, "课程性质": 150, "开课学院": 300, "考试方式": 100, "学分": 100, "成绩": 100}
  174.         # 初始化表格 表格是基于Treeview，tkinter本身没有表格。show="headings" 为隐藏首列。
  175.         tk_table = Treeview(self, show="headings", columns=list(self.tk_table_stu_score_columns))
  176.         for text, width in self.tk_table_stu_score_columns.items():  # 批量设置列属性
  177.             tk_table.heading(text, text=text, anchor='center')
  178.             tk_table.column(text, anchor='center', width=width, stretch=False)  # stretch 不自动拉伸
  179.  
  180.         # 插入数据示例
  181.         # self.tk_score_table_dataset = Dao.getScoreByUid(8888)
  182.         # # 导入初始数据
  183.         # if self.tk_score_table_dataset.get("code") == 0 and self.tk_score_table_dataset.get("data"):
  184.         #     for data in self.tk_score_table_dataset.get("data"):
  185.         #         tk_table.insert('', END, values=list(data.values()))
  186.  
  187.         tk_table.place(x=0, y=55, width=1000, height=420)
  188.         return tk_table
  189.  
  190.     def __tk_button_analysis(self):
  191.         btn = Button(self, text="分析")
  192.         btn.place(x=65, y=10, width=75, height=30)
  193.         return btn
  194.  
  195.     def __tk_select_box_course_nature(self):
  196.         cb = Combobox(self, state="readonly")
  197.         values = ["请选择课程性质"]
  198.         for i in Dao.getDataDictByType("nature").get("data"):
  199.             values.append(i.get("v"))
  200.         cb['values'] = values
  201.         cb.current(0)
  202.         cb.place(x=180, y=10, width=150, height=30)
  203.         return cb
  204.  
  205.     def __tk_select_box_course_department(self):
  206.         cb = Combobox(self, state="readonly")
  207.         values = ["请选择开课学院"]
  208.         for i in Dao.getAllDepartments().get("data"):
  209.             values.append(i.get("v"))
  210.         cb['values'] = values
  211.         cb.current(0)
  212.         cb.place(x=370, y=10, width=150, height=30)
  213.         return cb
  214.  
  215.     def __tk_select_box_exam_method(self):
  216.         cb = Combobox(self, state="readonly")
  217.         values = ["请选择考试方式"]
  218.         for i in Dao.getDataDictByType("exammethod").get("data"):
  219.             values.append(i.get("v"))
  220.         cb['values'] = values
  221.         cb.current(0)
  222.         cb.place(x=560, y=10, width=150, height=30)
  223.         return cb
  224.  
  225.     def __tk_button_search(self):
  226.         btn = Button(self, text="搜索")
  227.         btn.place(x=750, y=10, width=75, height=30)
  228.         return btn
  229.  
  230.     def __tk_button_export(self):
  231.         btn = Button(self, text="导出")
  232.         btn.place(x=840, y=10, width=75, height=30)
  233.         return btn
  234.  
  235.  
  236. class Frame_content_3(Frame):
  237.     def __init__(self, parent):
  238.         super().__init__(parent)
  239.         self.__frame()
  240.         self.tk_label_original_password = self.__tk_label_original_password()
  241.         self.tk_input_original_password = self.__tk_input_original_password()
  242.         self.tk_label_new_password = self.__tk_label_new_password()
  243.         self.tk_input_new_password = self.__tk_input_new_password()
  244.         self.tk_label_confirm_password = self.__tk_label_confirm_password()
  245.         self.tk_input_confirm_password = self.__tk_input_confirm_password()
  246.         self.tk_button_update_stu_password = self.__tk_button_update_stu_password()
  247.  
  248.     def __frame(self):
  249.         self.place(x=0, y=100, width=1000, height=500)
  250.  
  251.     def __tk_label_original_password(self):
  252.         label = Label(self, text="原密码", anchor="e")
  253.         label.place(x=360, y=40, width=100, height=30)
  254.         return label
  255.  
  256.     def __tk_input_original_password(self):
  257.         ipt = Entry(self, show='*')
  258.         ipt.place(x=490, y=40, width=150, height=30)
  259.         return ipt
  260.  
  261.     def __tk_label_new_password(self):
  262.         label = Label(self, text="新密码", anchor="e")
  263.         label.place(x=360, y=110, width=100, height=30)
  264.         return label
  265.  
  266.     def __tk_input_new_password(self):
  267.         ipt = Entry(self, show='*')
  268.         ipt.place(x=490, y=110, width=150, height=30)
  269.         return ipt
  270.  
  271.     def __tk_label_confirm_password(self):
  272.         label = Label(self, text="确认密码", anchor="e")
  273.         label.place(x=360, y=180, width=100, height=30)
  274.         return label
  275.  
  276.     def __tk_input_confirm_password(self):
  277.         ipt = Entry(self, show='*')
  278.         ipt.place(x=490, y=180, width=150, height=30)
  279.         return ipt
  280.  
  281.     def __tk_button_update_stu_password(self):
  282.         btn = Button(self, text="修改")
  283.         btn.place(x=460, y=250, width=100, height=30)
  284.         return btn
  285.  
  286.  
  287. class Win(WinGUI):
  288.     def __init__(self, current_user):
  289.         super().__init__()
  290.         self.__event_bind()
  291.         self.uid = current_user.get("uid")
  292.         self.uclid = current_user.get("uclid")
  293.         self.tk_label_current_user['text'] = "当前用户：" + current_user.get("uname")
  294.         self.tk_tabs_content.tk_tabs_content_0.student_number.set(current_user.get("uid"))
  295.         self.tk_tabs_content.tk_tabs_content_0.student_name.set(current_user.get("uname"))
  296.         self.tk_tabs_content.tk_tabs_content_0.tk_tk_select_stu_gender.current(0 if current_user.get("ugender") == '男' else 1)
  297.         self.tk_tabs_content.tk_tabs_content_0.student_identify.set(current_user.get("uidentify"))
  298.         self.tk_tabs_content.tk_tabs_content_0.student_email.set(current_user.get("uemail"))
  299.  
  300.         # 插入数据示例
  301.         self.score_table_dataset = Dao.getScoreByUid(self.uid)
  302.         # 导入初始数据
  303.         if self.score_table_dataset.get("code") == 0 and self.score_table_dataset.get("data"):
  304.             for data in self.score_table_dataset.get("data"):
  305.                 self.tk_tabs_content.tk_tabs_content_1.tk_table_stu_score.insert('', END, values=list(data.values()))
  306.  
  307.     def logout(self):
  308.         messagebox.showwarning('提示', '欢迎下次使用！')
  309.         self.destroy()
  310.         login = Login.Win()
  311.         login.mainloop()
  312.  
  313.     def updateStudentInfo(self, evt):
  314.         __stu_name = self.tk_tabs_content.tk_tabs_content_0.tk_input_stu_name.get()
  315.         __stu_gender = self.tk_tabs_content.tk_tabs_content_0.tk_tk_select_stu_gender.get()
  316.         __stu_identify = self.tk_tabs_content.tk_tabs_content_0.tk_input_stu_identity.get()
  317.         __stu_email = self.tk_tabs_content.tk_tabs_content_0.tk_input_stu_email.get()
  318.         if not __stu_name or not __stu_gender or not __stu_identify or not __stu_email:
  319.             messagebox.showinfo("提示", "必填项不能为空！")
  320.             return
  321.         if not re.match(r'^[1-9]\d{5}(18|19|20)\d{2}((0[1-9])|(1[0-2]))(([0-2][1-9])|10|20|30|31)\d{3}[0-9Xx]$', __stu_identify):
  322.             messagebox.showinfo("提示", "身份证格式不合法！")
  323.             return
  324.         if not re.match(r'^[\w-]+(\.[\w-]+)*@[\w-]+(\.[\w-]+)+$', __stu_email):
  325.             messagebox.showinfo("提示", "电子邮箱格式不合法！")
  326.             return
  327.         res = Dao.updateUser(self.uid, __stu_name, __stu_gender, __stu_identify, self.uclid, __stu_email)
  328.         messagebox.showinfo("提示", res.get("msg"))
  329.         self.tk_label_current_user['text'] = "当前用户：" + __stu_name
  330.         print("更新学生信息", evt)
  331.  
  332.     def stu_reset(self, evt):
  333.         self.tk_tabs_content.tk_tabs_content_0.student_number.set(self.userInfo[0])
  334.         self.tk_tabs_content.tk_tabs_content_0.student_name.set(self.userInfo[1])
  335.         self.tk_tabs_content.tk_tabs_content_0.tk_tk_select_stu_gender.current(0 if self.userInfo[2] else 1)
  336.         self.tk_tabs_content.tk_tabs_content_0.student_identify.set(self.userInfo[3])
  337.         self.tk_tabs_content.tk_tabs_content_0.student_email.set(self.userInfo[5])
  338.  
  339.     def analysisStudentScore(self, evt):
  340.         result = Dao.getScoreByUid(self.uid).get("data")
  341.         plt.title('成绩统计图')
  342.         # 设置x轴数据
  343.         x = [i.get("cname") for i in result]
  344.         # 每组数据n有3个类型
  345.         total_width, n = 0.6, 3
  346.         width = total_width / n
  347.         y1 = [i.get("score") for i in result]
  348.         y2 = [i.get("avg_score") for i in Dao.getAllCourseAvgScore(self.uid).get("data")]
  349.         plt.bar(x, y1, color="b", width=width, label='我的成绩')
  350.         plt.plot(x, y2, color="g", label='科目平均成绩')
  351.         # x和y轴标题
  352.         plt.xlabel("课程")
  353.         plt.ylabel("分数")
  354.         plt.legend(loc="best")
  355.         plt.ylim((0, 100))
  356.         # 设置纵轴起始,终止和间距
  357.         my_y_ticks = np.arange(0, 100, 10)
  358.         plt.yticks(my_y_ticks)
  359.         plt.rcParams['font.sans-serif'] = ['SimHei']  # 支持中文显示
  360.         plt.show()
  361.         print("成绩分析图表绘制")
  362.  
  363.     def searchStudentScore(self, evt):
  364.         __score_query = self.tk_tabs_content.tk_tabs_content_1
  365.         __nature = __score_query.tk_select_box_course_nature.get()
  366.         __department = __score_query.tk_select_box_course_department.get()
  367.         __exammethod = __score_query.tk_select_box_exam_method.get()
  368.         if __score_query.tk_select_box_course_nature.current() == 0:
  369.             __nature = ''
  370.         if __score_query.tk_select_box_course_department.current() == 0:
  371.             __department = ''
  372.         if __score_query.tk_select_box_exam_method.current() == 0:
  373.             __exammethod = ''
  374.         for _ in map(__score_query.tk_table_stu_score.delete, __score_query.tk_table_stu_score.get_children("")):
  375.             pass
  376.         self.score_table_dataset = Dao.getScoreByUid(self.uid, __nature, __department, __exammethod)
  377.         # 导入初始数据
  378.         if self.score_table_dataset.get("code") == 0 and self.score_table_dataset.get("data"):
  379.             for data in self.score_table_dataset.get("data"):
  380.                 __score_query.tk_table_stu_score.insert('', END, values=list(data.values()))
  381.         __score_query.tk_select_box_course_nature.current(0)
  382.         __score_query.tk_select_box_course_department.current(0)
  383.         __score_query.tk_select_box_exam_method.current(0)
  384.         print(f"查询学生{self.uid}的成绩！")
  385.  
  386.     def exportStudentScore(self, evt):
  387.         path = filedialog.askdirectory()
  388.         try:
  389.             book = openpyxl.Workbook()
  390.             sheet = book.active
  391.             fff = list(self.tk_tabs_content.tk_tabs_content_1.tk_table_stu_score_columns.keys())  # 获取表头信息
  392.             sheet.append(fff)
  393.             dataset = [list(data_item.values()) for data_item in
  394.                        self.score_table_dataset.get("data")]
  395.             print(dataset)
  396.             for i in dataset:
  397.                 sheet.append(i)
  398.             book.save(f"{path}/{self.uid}.xlsx")
  399.             messagebox.showinfo("提示", "导出成功！")
  400.         except Exception as e:
  401.             messagebox.showinfo("提示", "导出失败！")
  402.             print(e)
  403.         print("<Button-1>事件未处理", evt)
  404.  
  405.     def updateStudentPassword(self, evt):
  406.         __original_pwd = self.tk_tabs_content.tk_tabs_content_3.tk_input_original_password.get()
  407.         __new_pwd = self.tk_tabs_content.tk_tabs_content_3.tk_input_new_password.get()
  408.         __confirm_pwd = self.tk_tabs_content.tk_tabs_content_3.tk_input_confirm_password.get()
  409.         if __original_pwd == '' or __new_pwd == '' or __confirm_pwd == '':
  410.             messagebox.showwarning("提示", "必填项未填写！")
  411.             return
  412.         if not re.match(r"^[0-9a-zA-Z~!@#$%^&*._?]{6,18}$", __original_pwd) \
  413.                 or not re.match(r"^[0-9a-zA-Z~!@#$%^&*._?]{6,18}$", __new_pwd) \
  414.                 or not re.match(r"^[0-9a-zA-Z~!@#$%^&*._?]{6,18}$", __confirm_pwd):
  415.             messagebox.showwarning("提示", "密码格式应为6-18位数字、字母、特殊字符的组合！")
  416.             return
  417.         if __new_pwd != __confirm_pwd:
  418.             messagebox.showwarning("提示", "两次密码输入不一致")
  419.             return
  420.         res = Dao.updatePassword(self.uid, __original_pwd, __new_pwd)
  421.         messagebox.showinfo("提示", res.get("msg"))
  422.         if res.get("code") == 0:
  423.             self.destroy()
  424.             login = Login.Win()
  425.             login.mainloop()
  426.         print("修改密码", evt)
  427.  
  428.     def logout_user(self, evt):
  429.         messagebox.showwarning('提示', '欢迎下次使用！')
  430.         self.destroy()
  431.         login = Login.Win()
  432.         login.mainloop()
  433.  
  434.     def __event_bind(self):
  435.         self.protocol('WM_DELETE_WINDOW', self.logout)
  436.         self.tk_tabs_content.tk_tabs_content_0.tk_button_stu_update.bind('<Button-1>', self.updateStudentInfo)
  437.         self.tk_tabs_content.tk_tabs_content_0.tk_button_stu_reset.bind('<Button-1>', self.stu_reset)
  438.         self.tk_tabs_content.tk_tabs_content_1.tk_button_analysis.bind('<Button-1>', self.analysisStudentScore)
  439.         self.tk_tabs_content.tk_tabs_content_1.tk_button_search.bind('<Button-1>', self.searchStudentScore)
  440.         self.tk_tabs_content.tk_tabs_content_1.tk_button_export.bind('<Button-1>', self.exportStudentScore)
  441.         self.tk_tabs_content.tk_tabs_content_3.tk_button_update_stu_password.bind('<Button-1>',
  442.                                                                                   self.updateStudentPassword)
  443.         self.tk_button_logout.bind('<Button-1>', self.logout_user)
  444.

数据库采用mysql 8.0，库名和表如下：

d05ca003ad1c475297b73b3e0afbcf3a.png

 Dao层代码如下：用于访问数据库,向数据库发送sql语句,完成成绩与科目等增删改查任务

Dao.py:

  1. import pymysql
  2.  
  3.  
  4. def getConnect():
  5.     """
  6.     获取数据库连接
  7.     :return:
  8.     """
  9.     conn = pymysql.Connect(
  10.         host='localhost',
  11.         port=3306,
  12.         user='#你的数据库用户名',
  13.         password='#你的数据库密码',
  14.         db='stu_sc_sys',
  15.         charset='utf8',
  16.         cursorclass=pymysql.cursors.DictCursor
  17.     )
  18.     cursor = conn.cursor()
  19.     return conn, cursor
  20.  
  21.  
  22. def getUserByIdAndPwd(username, password):
  23.     conn, cursor = getConnect()
  24.     sql = f"select uid, uname, ugender, uidentify, uclid, uemail, urole from user where uid='{username}' " \
  25.           f"and upwd='{password}'"
  26.     cursor.execute(sql)
  27.     res = {
  28.         "code": 0,
  29.         "msg": "success",
  30.         "data": cursor.fetchone()
  31.     }
  32.     cursor.close()
  33.     conn.close()
  34.     return res
  35.  
  36.  
  37. def getUserInfoById(uid):
  38.     conn, cursor = getConnect()
  39.     sql = f"select uid, uname, ugender, uidentify, uclid, uemail, urole from user where uid='{uid}'"
  40.     cursor.execute(sql)
  41.     res = cursor.fetchone()
  42.     cursor.close()
  43.     conn.close()
  44.     return res
  45.  
  46.  
  47. def updatePassword(uid, origin_pwd, new_pwd):
  48.     conn, cursor = getConnect()
  49.     res = {
  50.         "code": 1,
  51.         "msg": "修改密码失败！"
  52.     }
  53.     if not getUserByIdAndPwd(uid, origin_pwd).get("data"):
  54.         res = {
  55.             "code": 1,
  56.             "msg": "原密码不正确！"
  57.         }
  58.     else:
  59.         sql = f"update user set upwd = '{new_pwd}' where uid = '{uid}'"
  60.         try:
  61.             cursor.execute(sql)
  62.             conn.commit()
  63.             res = {
  64.                 "code": 0,
  65.                 "msg": "修改成功！"
  66.             }
  67.         except Exception as e:
  68.             conn.rollback()
  69.             print(e)
  70.     return res
  71.  
  72.  
  73. def getScoreByUid(uid, nature='', department='', exam_method=''):
  74.     """
  75.     通过学号获取成绩
  76.     :param uid:
  77.     :param nature:
  78.     :param department:
  79.     :param exam_method:
  80.     :return:
  81.     """
  82.     conn, cursor = getConnect()
  83.     sql = f"select ROW_NUMBER() over () as id, uc.cname, c.cnature, c.cdepartment, c.cexammethod, c.ccredit, score " \
  84.           f"from user_course uc inner join user u on uc.uid = u.uid inner join course c on uc.cname = c.cname " \
  85.           f"where u.uid = '{uid}' and c.cnature like '%{nature}%' and " \
  86.           f"c.cdepartment like '%{department}%' and c.cexammethod like '%{exam_method}%'"
  87.     print(sql)
  88.     cursor.execute(sql)
  89.     res = {
  90.         "code": 0,
  91.         "msg": "success",
  92.         "data": cursor.fetchall()
  93.     }
  94.     cursor.close()
  95.     conn.close()
  96.     return res
  97.  
  98.  
  99. def getAllUsers():
  100.     connection, cursor = getConnect()
  101.     sql = 'select uid, uname, ugender, uidentify, uclid, uemail, upwd, urole from user'
  102.     cursor.execute(sql)
  103.     res = {
  104.         "code": 0,
  105.         "msg": "success",
  106.         "data": cursor.fetchall()
  107.     }
  108.     cursor.close()
  109.     connection.close()
  110.     return res
  111.  
  112.  
  113. def getAllStudents():
  114.     connection, cursor = getConnect()
  115.     sql = "select row_number() over () as id, uid, uname, ugender, uidentify, uclid, uemail from user where urole = 1"
  116.     cursor.execute(sql)
  117.     res = {
  118.         "code": 0,
  119.         "msg": "success",
  120.         "data": cursor.fetchall()
  121.     }
  122.     cursor.close()
  123.     connection.close()
  124.     return res
  125.  
  126.  
  127. def searchStudents(uname, ugender):
  128.     """
  129.     学生信息搜索
  130.     :param uname:
  131.     :param ugender:
  132.     :return:
  133.     """
  134.     connection, cursor = getConnect()
  135.     sql = f"select row_number() over () as id, uid, uname, ugender, uidentify, uclid, uemail, upwd, urole from user " \
  136.           f"where urole = 1 and uname like '%{uname}%' and ugender like '%{ugender}%'"
  137.     cursor.execute(sql)
  138.     res = {
  139.         "code": 0,
  140.         "msg": "success",
  141.         "data": cursor.fetchall()
  142.     }
  143.     cursor.close()
  144.     connection.close()
  145.     return res
  146.  
  147.  
  148. def addStudent(uid, uname, ugender, uidentify, uclid, uemail):
  149.     """
  150.     添加一个学生信息，密码默认为123456
  151.     :param uid:
  152.     :param uname:
  153.     :param ugender:
  154.     :param uidentify:
  155.     :param uclid:
  156.     :param uemail:
  157.     :return:
  158.     """
  159.     connection, cursor = getConnect()
  160.     sql = f"insert into user(uid, uname, ugender, uidentify, uclid, uemail, upwd) values ('{uid}', '{uname}'" \
  161.           f", '{ugender}', '{uidentify}', '{uclid}', '{uemail}', '123456') "
  162.     res = {
  163.         "code": 0,
  164.         "msg": "添加成功！"
  165.     }
  166.     try:
  167.         cursor.execute(sql)
  168.         connection.commit()
  169.     except Exception as e:
  170.         res = {
  171.             "code": 1,
  172.             "msg": "添加失败！"
  173.         }
  174.         connection.rollback()
  175.         print(e)
  176.     cursor.close()
  177.     connection.close()
  178.     return res
  179.  
  180.  
  181. def deleteUser(uid):
  182.     """
  183.     通过uid删除用户
  184.     :param uid:
  185.     :return:
  186.     """
  187.     connection, cursor = getConnect()
  188.     sql = f"delete from user where uid = '{uid}'"
  189.     res = {
  190.         "code": 0,
  191.         "msg": "删除成功！"
  192.     }
  193.     try:
  194.         cursor.execute(sql)
  195.         connection.commit()
  196.     except Exception as e:
  197.         res = {
  198.             "code": 1,
  199.             "msg": "删除失败！"
  200.         }
  201.         connection.rollback()
  202.         print(e)
  203.     cursor.close()
  204.     connection.close()
  205.     return res
  206.  
  207.  
  208. def updateUser(uid, uname, ugender, uidentify, uclid, uemail):
  209.     """
  210.     通过uid更新用户信息
  211.     :param uid:
  212.     :param uname:
  213.     :param ugender:
  214.     :param uidentify:
  215.     :param uclid:
  216.     :param uemail:
  217.     :return:
  218.     """
  219.     connection, cursor = getConnect()
  220.     sql = f"update user set uname = '{uname}', ugender = '{ugender}', uidentify = '{uidentify}', uclid = '{uclid}'" \
  221.           f", uemail ='{uemail}' where uid = '{uid}'"
  222.     print(sql)
  223.     res = {
  224.         "code": 0,
  225.         "msg": "修改成功！"
  226.     }
  227.     try:
  228.         cursor.execute(sql)
  229.         connection.commit()
  230.     except Exception as e:
  231.         res = {
  232.             "code": 1,
  233.             "msg": "修改失败！"
  234.         }
  235.         connection.rollback()
  236.         print(e)
  237.     cursor.close()
  238.     connection.close()
  239.     return res
  240.  
  241.  
  242. def getAllCourses():
  243.     """
  244.     获取所有的课程信息
  245.     :return:
  246.     """
  247.     connection, cursor = getConnect()
  248.     sql = "select cid, cname, ccredit, cnature, cdepartment, cexammethod from course"
  249.     cursor.execute(sql)
  250.     res = {
  251.         "code": 0,
  252.         "msg": "success",
  253.         "data": cursor.fetchall()
  254.     }
  255.     cursor.close()
  256.     connection.close()
  257.     return res
  258.  
  259.  
  260. def getCourseByCid(cid):
  261.     """
  262.     获取cid的课程
  263.     :return:
  264.     """
  265.     connection, cursor = getConnect()
  266.     sql = f"select cid, cname, cnature, ccredit, cdepartment, cexammethod from course where cid = '{cid}'"
  267.     cursor.execute(sql)
  268.     res = {
  269.         "code": 0,
  270.         "msg": "success",
  271.         "data": cursor.fetchall()
  272.     }
  273.     cursor.close()
  274.     connection.close()
  275.     return res
  276.  
  277.  
  278. def getDataDictByType(dtype):
  279.     """
  280.     获取dtype类型的数据字典
  281.     :param:
  282.     :return:
  283.     """
  284.     connection, cursor = getConnect()
  285.     sql = f"select d.ddtkey as k, d.ddtvalue as v from dictionary d where d.ddtype = '{dtype}'"
  286.     cursor.execute(sql)
  287.     res = {
  288.         "code": 0,
  289.         "msg": "success",
  290.         "data": cursor.fetchall()
  291.     }
  292.     cursor.close()
  293.     connection.close()
  294.     return res
  295.  
  296.  
  297. def getAllDepartments():
  298.     """
  299.     获取所有的学院
  300.     :return:
  301.     """
  302.     connection, cursor = getConnect()
  303.     sql = "select did as k, dname as v from department"
  304.     cursor.execute(sql)
  305.     res = {
  306.         "code": 0,
  307.         "msg": "success",
  308.         "data": cursor.fetchall()
  309.     }
  310.     cursor.close()
  311.     connection.close()
  312.     return res
  313.  
  314.  
  315. def getMaxStuNumber(s_num_prefix):
  316.     """
  317.     获取以s_num_prefix开头的最大学号
  318.     :return:
  319.     """
  320.     connection, cursor = getConnect()
  321.     sql = f"select MAX(uid) as max_id from user where uid like '{s_num_prefix}%'"
  322.     cursor.execute(sql)
  323.     res = {
  324.         "code": 0,
  325.         "msg": "success",
  326.         "data": cursor.fetchall()
  327.     }
  328.     cursor.close()
  329.     connection.close()
  330.     return res
  331.  
  332.  
  333. def addCourse(cname, nature, credit, department, exam_method):
  334.     """
  335.     添加课程
  336.     :return:
  337.     """
  338.     connection, cursor = getConnect()
  339.     sql = f"insert into course(cname, cnature, ccredit, cdepartment, cexammethod) VALUES ('{cname}', '{nature}'" \
  340.           f", '{credit}', '{department}', '{exam_method}')"
  341.     res = {
  342.         "code": 0,
  343.         "msg": "添加课程成功！"
  344.     }
  345.     try:
  346.         cursor.execute(sql)
  347.         connection.commit()
  348.     except Exception as e:
  349.         res = {
  350.             "code": 1,
  351.             "msg": "添加课程失败！"
  352.         }
  353.         connection.rollback()
  354.         print(e)
  355.     cursor.close()
  356.     connection.close()
  357.     return res
  358.  
  359.  
  360. def deleteCourse(cid):
  361.     """
  362.     删除课程
  363.     :return:
  364.     """
  365.     connection, cursor = getConnect()
  366.     sql = f"delete from course where cid = {cid}"
  367.     res = {
  368.         "code": 0,
  369.         "msg": "删除课程成功！"
  370.     }
  371.     try:
  372.         cursor.execute(sql)
  373.         connection.commit()
  374.     except Exception as e:
  375.         res = {
  376.             "code": 1,
  377.             "msg": "删除课程失败！"
  378.         }
  379.         connection.rollback()
  380.         print(e)
  381.     cursor.close()
  382.     connection.close()
  383.     return res
  384.  
  385.  
  386. def searchCourses(department, exammethod):
  387.     """
  388.     搜索课程信息
  389.     :param department:
  390.     :param exammethod:
  391.     :return:
  392.     """
  393.     connection, cursor = getConnect()
  394.     sql = f"select cid, cname, ccredit, cnature, cdepartment, cexammethod from course " \
  395.           f"where cdepartment like '%{department}%' and cexammethod like '%{exammethod}%'"
  396.     cursor.execute(sql)
  397.     res = {
  398.         "code": 0,
  399.         "msg": "success",
  400.         "data": cursor.fetchall()
  401.     }
  402.     cursor.close()
  403.     connection.close()
  404.     return res
  405.  
  406.  
  407. def updateCourseInfo(cid, cname, nature, credit, department, exammethod):
  408.     """
  409.     修改课程信息
  410.     :param cid:
  411.     :param cname:
  412.     :param nature:
  413.     :param credit:
  414.     :param department:
  415.     :param exammethod:
  416.     :return:
  417.     """
  418.     connection, cursor = getConnect()
  419.     sql = f"update course set cname = '{cname}', cnature = '{nature}', ccredit = '{credit}'" \
  420.           f", cdepartment = '{department}', cexammethod = '{exammethod}' where cid = '{cid}'"
  421.     res = {
  422.         "code": 0,
  423.         "msg": "修改课程成功！"
  424.     }
  425.     try:
  426.         cursor.execute(sql)
  427.         connection.commit()
  428.     except Exception as e:
  429.         res = {
  430.             "code": 1,
  431.             "msg": "更新课程失败！"
  432.         }
  433.         connection.rollback()
  434.         print(e)
  435.     cursor.close()
  436.     connection.close()
  437.     return res
  438.  
  439.  
  440. def getScoreBandByCName(course_name):
  441.     """
  442.     获取课程名为course_name的各个分段的学生人数
  443.     :param course_name:
  444.     :return:
  445.     """
  446.     connection, cursor = getConnect()
  447.     sql = f"select SUM(IF(score <= 100 and score >= 90, 1, 0)) as A, SUM(IF(score < 90 and score >= 80, 1, 0)) as B, " \
  448.           f"SUM(IF(score < 80 and score >= 70, 1, 0)) as C, SUM(IF(score < 70 and score >= 60, 1, 0)) as D, " \
  449.           f"SUM(IF(score < 60, 1, 0)) as E from user_course where cname = '{course_name}'"
  450.     cursor.execute(sql)
  451.     res = {
  452.         "code": 0,
  453.         "msg": "success",
  454.         "data": cursor.fetchall()
  455.     }
  456.     cursor.close()
  457.     connection.close()
  458.     return res
  459.  
  460.  
  461. def getMaxAndMinAndAvgScoreByCLid(class_id):
  462.     """
  463.     获取班级号为class_id的班级的各科最高分、最低分以及平均分
  464.     :param class_id:
  465.     :return:
  466.     """
  467.     connection, cursor = getConnect()
  468.     sql = f"select uc.cname, MAX(score) as max_score, MIN(score) as min_score, AVG(score) as avg_score " \
  469.           f"from user_course uc inner join user u on uc.uid = u.uid where u.uclid = '{class_id}' group by uc.cname"
  470.     cursor.execute(sql)
  471.     res = {
  472.         "code": 0,
  473.         "msg": "success",
  474.         "data": cursor.fetchall()
  475.     }
  476.     cursor.close()
  477.     connection.close()
  478.     return res
  479.  
  480.  
  481. def getOverallGradeLevelByCLid(class_id):
  482.     """
  483.     获取班级号为class_id的班级综合成绩等级各分段人数
  484.     :param class_id:
  485.     :return:
  486.     """
  487.     connection, cursor = getConnect()
  488.     sql = f"select SUM(IF(avg_score >= 85 and avg_score <= 100, 1, 0)) as A, " \
  489.           f"SUM(IF(avg_score >= 70 and avg_score < 85, 1, 0)) as B, " \
  490.           f"SUM(IF(avg_score >= 60 and avg_score < 70, 1, 0)) as C, " \
  491.           f"SUM(IF(avg_score < 60, 1, 0)) as D from (" \
  492.           f"select AVG(score) as avg_score from user_course uc " \
  493.           f"inner join user u on uc.uid = u.uid where u.uclid = '{class_id}' group by u.uid" \
  494.           f") s"
  495.     cursor.execute(sql)
  496.     res = {
  497.         "code": 0,
  498.         "msg": "success",
  499.         "data": cursor.fetchall()
  500.     }
  501.     cursor.close()
  502.     connection.close()
  503.     return res
  504.  
  505.  
  506. def getAllClasses():
  507.     """
  508.     获取用户表中已存在的班级号
  509.     :return:
  510.     """
  511.     connection, cursor = getConnect()
  512.     sql = f"select distinct uclid from user where urole = 1"
  513.     cursor.execute(sql)
  514.     res = {
  515.         "code": 0,
  516.         "msg": "success",
  517.         "data": cursor.fetchall()
  518.     }
  519.     cursor.close()
  520.     connection.close()
  521.     return res
  522.  
  523.  
  524. def getAllCourseAvgScore(uid):
  525.     """
  526.     获取所有课程的平均分
  527.     :return:
  528.     """
  529.     connection, cursor = getConnect()
  530.     sql = f"select cname, AVG(score) as avg_score from user_course where cname in " \
  531.           f"(select cname from user_course where uid = '{uid}') group by cname"
  532.     cursor.execute(sql)
  533.     res = {
  534.         "code": 0,
  535.         "msg": "success",
  536.         "data": cursor.fetchall()
  537.     }
  538.     cursor.close()
  539.     connection.close()
  540.     return res
  541.  
  542.  
  543. def searchStudentScore(course_name='', course_nature='', course_department=''):
  544.     """
  545.     搜索学生成绩
  546.     :param course_name:
  547.     :param course_nature:
  548.     :param course_department:
  549.     :return:
  550.     """
  551.     connection, cursor = getConnect()
  552.     sql = f"select ROW_NUMBER() over () as id, u.uid, u.uname, uc.cname, c.cnature, c.cdepartment, c.cexammethod, " \
  553.           f"c.ccredit, score from user_course uc inner join user u on uc.uid = u.uid inner join course c " \
  554.           f"on uc.cname = c.cname where c.cname like '%{course_name}%' and " \
  555.           f"c.cnature like '%{course_nature}%' and c.cdepartment like '%{course_department}%'"
  556.     cursor.execute(sql)
  557.     res = {
  558.         "code": 0,
  559.         "msg": "success",
  560.         "data": cursor.fetchall()
  561.     }
  562.     cursor.close()
  563.     connection.close()
  564.     return res
  565.  
  566.  
  567. def getScoreByUidAndCName(uid, course_name):
  568.     """
  569.     查找学号为uid课程为course_name的学生成绩
  570.     :param uid:
  571.     :param course_name:
  572.     :return:
  573.     """
  574.     connection, cursor = getConnect()
  575.     sql = f"select uid, cname, score from user_course where uid = '{uid}' and cname = '{course_name}'"
  576.     cursor.execute(sql)
  577.     res = {
  578.         "code": 0,
  579.         "msg": "success",
  580.         "data": cursor.fetchall()
  581.     }
  582.     cursor.close()
  583.     connection.close()
  584.     return res
  585.  
  586.  
  587. def addStudentScore(uid, course_name, score):
  588.     connection, cursor = getConnect()
  589.     if not cursor.execute(f"select uid from user where uid = '{uid}'"):
  590.         return {
  591.             "code": 1,
  592.             "msg": "学号不存在！"
  593.         }
  594.     if cursor.execute(f"select 1 from user_course where uid = '{uid}' and cname = '{course_name}' limit 1"):
  595.         return {
  596.             "code": 1,
  597.             "msg": f"该学生的{course_name}课程成绩已经存在！"
  598.         }
  599.     sql = f"insert into user_course(uid, cname, score) VALUES ('{uid}', '{course_name}', '{score}')"
  600.     res = {
  601.         "code": 0,
  602.         "msg": "添加成绩成功！"
  603.     }
  604.     try:
  605.         cursor.execute(sql)
  606.         connection.commit()
  607.     except Exception as e:
  608.         res = {
  609.             "code": 1,
  610.             "msg": "添加成绩失败！"
  611.         }
  612.         connection.rollback()
  613.         print(e)
  614.     cursor.close()
  615.     connection.close()
  616.     return res
  617.  
  618.  
  619. def deleteStudentScore(uid, course_name):
  620.     connection, cursor = getConnect()
  621.     if not cursor.execute(f"select uid from user where uid = '{uid}'"):
  622.         return {
  623.             "code": 1,
  624.             "msg": "学号不存在！"
  625.         }
  626.     sql = f"delete from user_course where uid = '{uid}' and cname = '{course_name}'"
  627.     res = {
  628.         "code": 0,
  629.         "msg": "删除成绩成功！"
  630.     }
  631.     try:
  632.         code = cursor.execute(sql)
  633.         connection.commit()
  634.         if code == 0:
  635.             res = {
  636.                 "code": 0,
  637.                 "msg": f"该学生的{course_name}课程成绩不存在！"
  638.             }
  639.     except Exception as e:
  640.         res = {
  641.             "code": 1,
  642.             "msg": "删除成绩失败！"
  643.         }
  644.         connection.rollback()
  645.         print(e)
  646.     cursor.close()
  647.     connection.close()
  648.     return res
  649.  
  650.  
  651. def updateStudentScore(uid, course_name, score):
  652.     connection, cursor = getConnect()
  653.     sql = f"update user_course set score = '{score}' where uid = '{uid}' and cname = '{course_name}'"
  654.     res = {
  655.         "code": 0,
  656.         "msg": "修改成绩成功！"
  657.     }
  658.     try:
  659.         cursor.execute(sql)
  660.         connection.commit()
  661.     except Exception as e:
  662.         res = {
  663.             "code": 1,
  664.             "msg": "修改成绩失败！"
  665.         }
  666.         connection.rollback()
  667.         print(e)
  668.     cursor.close()
  669.     connection.close()
  670.     return res

本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号