基于pymysql应用系统开发-学生成绩管理系统_pymysql学生管理系统

学生成绩管理系统

1需求分析

学生成绩管理系统是一种基于计算机技术的教育管理工具，旨在帮助学校、教师和家长更好地管理和跟踪学生的学习成绩，如学生成绩的录入、查看、删除、查找、更改。

2概念结构设计

E-R 图如下图所示：

3逻辑结构设计

ER 图转换后的关系模式 

转换后的关系模式如下：

学生信息（学号， 班级， 姓名 ，学部）

学生选课（学号， 课程号， 教师工号）

学生成绩（学号， 课程号， 成绩

4数据库 SQL 源代码

create table students(

sno char(10) not null,

sname char(10),

sclass char(20),  

depart char(10),

primary key (sno)

)default charset=utf8;

create table course(

sno char(10) not null,

cno char(20) not null,

teacher char(10) not null,

primary key (sno, cno)

)default charset=utf8;

create table grades(

sno char(10) not null,

cno char(20) not null,

grade char(5) not null,

primary key (sno,cno)

)default charset=utf8;

INSERT INTO students (sno, sname, sclass, depart) VALUES

(1, '言琳', 2, '计算机系'),

(2, '黄茵', 2, '计算机系'),

(3, '谢红', 2, '计算机系'),

(4, '谢村明', 2, '计算机系'),

(5, '郑云昊', 3, '经管系'),

(6, '谢佳琪', 2, '电子系'),

(7, '罗文心', 1, '电子系'),

(8, '谢宇航', 4, '电子系'),

(9, '谢刚', 4, '计算机系'),

(10, '朱莎', 3, '经管系');

insert into course(sno,cno, teacher) values

('1', '55501', 'J234567801'),

('2', '55502', 'J234567802'),

('3', '55501', 'J234567801'),

('5', '55502', 'J234567802'),

('7', '55501', 'J234567801'),

('8', '55502', 'J234567802'),

('4', '55501', 'J234567801'),

('6', '55502', 'J234567802'),

('9', '55502', 'J234567802'),

('10', '55502', 'J234567802');

insert into grades(sno,cno, grade) values

('1', '55501', '67'),

('2', '55502', '82'),

('3', '55501', '56'),

('5', '55502', '78'),

('7', '55501', '45'),

('8', '55502', '52'),

('4', '55501', '78'),

('6', '55502', '72'),

('9', '55502', '82'),

('10', '55502', '67');

5 Python源代码

import pymysql
import tkinter as tk
from tkinter import messagebox
db_config = {
    'host': 
    'port': 
    'user': 
    'passwd': 
    'db': 
}#这里链接自己得本地数据库

class Student:

    def __init__(self):
        self.window = tk.Tk()
        self.window.title("学生成绩管理系统")

        self.label1 = tk.Label(self.window, text='学号：')  
        self.label2 = tk.Label(self.window, text='姓名：')  
        self.entry1 = tk.Entry(self.window)  
        self.entry2 = tk.Entry(self.window)
        self.label3 = tk.Label(self.window, text='成绩：')  
        self.label4 = tk.Label(self.window, text='课程号：')  
        self.entry3 = tk.Entry(self.window)  
        self.entry4 = tk.Entry(self.window)
        self.label5 = tk.Label(self.window, text='学部：')  
        self.label6 = tk.Label(self.window, text='班级：')
        self.entry5 = tk.Entry(self.window)  
        self.entry6 = tk.Entry(self.window)
        self.label7 = tk.Label(self.window, text='教师职工号：')  
        self.entry7 = tk.Entry(self.window)  

        self.text = tk.Text(self.window)  

        self.label1.grid(row=0, column=0)
        self.entry1.grid(row=0, column=1)
        self.label2.grid(row=1, column=0)
        self.entry2.grid(row=1, column=1)
        self.label3.grid(row=2, column=0)
        self.entry3.grid(row=2, column=1)
        self.label4.grid(row=3, column=0)
        self.entry4.grid(row=3, column=1)
        self.label5.grid(row=4, column=0)
        self.entry5.grid(row=4, column=1)
        self.label6.grid(row=5, column=0)
        self.entry6.grid(row=5, column=1)
        self.label7.grid(row=6, column=0)
        self.entry7.grid(row=6, column=1)

          self.text.grid(row=7, column=1)

        self.btn_update = tk.Button(self.window, text="更新", command=self.update_student)
        self.button1 = tk.Button(self.window, text='查询', command=self.button)
        self.btn_add = tk.Button(self.window, text="添加", command=self.add_student)
        self.btn_delete = tk.Button(self.window, text="删除", command=self.delete_student)

        self.button1.grid(row=0, column=2)
        self.btn_add.grid(row=1, column=2)
        self.btn_delete.grid(row=2, column=2)
        self.btn_update.grid(row=3, column=2)

    def yuanselect(sql):
        conn = pymysql.connect(**db_config)
        cursor = conn.cursor()
        cursor.execute(sql)
        values = cursor.fetchall()
        cursor.close()
        conn.close()
        return values

    def button(self):
        sno = self.entry1.get()
        self.entry1.delete(0, tk.END)
        sname = self.entry2.get()
        self.entry1.delete(0, tk.END)  
        grade = self.entry3.get()
        self.entry3.delete(0, tk.END)
        cno = self.entry4.get()
        self.entry4.delete(0, tk.END)  
        depart = self.entry5.get()
        self.entry5.delete(0, tk.END)  
        sclass = self.entry6.get()
        self.entry6.delete(0, tk.END)  
        teacher = self.entry7.get()
        self.entry7.delete(0, tk.END)  
        sql = 'SELECT * FROM students left JOIN course ON students.sno = course.sno ' \
              'left JOIN grades ON students.sno = grades.sno  where 1=1 '  
        if sno:
            sql = sql + f'and students.sno ="{sno}"'
        if sname:
            sql = sql + f"and sname='{sname}'"
        if grade:
            sql = sql + f"and grade='{grade}'"
        if cno:
            sql = sql + f"and course.cno='{cno}'"
        if depart:
            sql = sql + f"and depart='{depart}'"
        if sclass:
            sql = sql + f"and sclass='{sclass}'"
        if teacher:
            sql = sql + f"and teacher='{teacher}'"
        print(sql)
        values = self.yuanselect(sql)
        self.text.delete('1.0', tk.END)
        for row in values:
            self.text.insert(tk.END, f"学号：{row[0]} 姓名：{row[1]} 班级：{row[2]} 学部：{row[3]} 课程号：{row[5]} "
                                     f"教师职工号：{row[6]} 成绩：{row[9]}\n")

    def add_student(self):
        sno = self.entry1.get()
        sname = self.entry2.get()
        grade = self.entry3.get()
        cno = self.entry4.get()
        depart = self.entry5.get()
        sclass = self.entry6.get()
        teacher = self.entry7.get()

        conn = pymysql.connect(**db_config)
        cursor = conn.cursor()

        sql1 = f"INSERT INTO students(sno, sname, sclass, depart) VALUES ('{sno}','{sname}','{sclass}','{depart}')"
        sql2 = f"INSERT INTO grades(sno, cno, grade) VALUES ('{sno}','{cno}','{grade}')"
        sql3 = f"INSERT INTO course(sno, cno, teacher) VALUES ('{sno}','{cno}','{teacher}')"

        print(sql1)
        cursor.execute(sql1)
        print(sql2)
        cursor.execute(sql2)
        print(sql3)
        cursor.execute(sql3)
        conn.commit()
        messagebox.showinfo("成功", "学生信息添加成功")
        conn.close()

    def delete_student(self):
        sno = self.entry1.get()
        conn = pymysql.connect(**db_config)
        cursor = conn.cursor()
        sql = "DELETE FROM students WHERE sno = '{}'".format(sno)
        print(sql)
        cursor.execute(sql)
        conn.commit()
        messagebox.showinfo("成功", "学生信息删除成功")
        conn.close()

    def update_student(self):
        sno = self.entry1.get()
        sname = self.entry2.get()
        grade = self.entry3.get()
        cno = self.entry4.get()
        depart = self.entry5.get()
        sclass = self.entry6.get()
        teacher = self.entry7.get()

        conn = pymysql.connect(**db_config)
        cursor = conn.cursor()

        sql = f"UPDATE students SET sname='{sname}',  sclass='{sclass}', depart='{depart}' WHERE sno='{sno}'"
        sql1 = f"UPDATE grades SET  grade='{grade}', cno='{cno}' WHERE sno='{sno}'"
        sql2 = f"UPDATE course SET  sno='{sno}', teacher='{teacher}' WHERE sno='{sno}'"

        print(sql)
        cursor.execute(sql)
        print(sql1)
        cursor.execute(sql1)
        print(sql2)
        cursor.execute(sql2)
        conn.commit()
        messagebox.showinfo("成功", "学生信息更新成功")
        conn.close()
    def run(self):
        self.window.mainloop()

if __name__ == "__main__":
    a=Student()
    a.run()