使用面向对象和数据库完成学生成绩管理系统

一、新建数据库

使用navicat在本地新建数据库student_system,在数据库中新建数据表student,
新建数据表时，命令如下：
CREATE TABLE student ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, student_number VARCHAR(10) NOT NULL UNIQUE, student_name VARCHAR(255) NOT NULL, chinese FLOAT DEFAULT '0', math FLOAT DEFAULT '0', english FLOAT DEFAULT '0' )
新建好的数据表如下：

二、mysql操作模块

mysql_util 模块 mysql_util.py

import pymysql
import traceback

class MysqlUtil(object):
    def __init__(self):
        self.host = 'localhost'
        self.user = 'root'
        self.password = 'root'
        self.db = 'student_system'
        self.charset = 'utf8'
        self.connect()

    def connect(self):
        self.connection = pymysql.connect(
            host='localhost',
            user='root',
            password='root',
            db='student_system',
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor
        )

    def execute(self,sql):
        """
        执行增/删/修改
        :param sql: sql语句
        :return: 影响的记录条数
        """
        try:
            with self.connection.cursor() as cursor:
                result = cursor.execute(sql)
            self.connection.commit()
            return result
        except:
            self.traceback()

    def find(self,sql,fetch_one=False):
        """
        查找一条记录或者全部记录
        :param sql: sql语句
        :param fetch_one: bool值，若为True,查找一条，若为False,查找多条
        :return: 查找的记录
        """
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql)
                if fetch_one:
                    result = cursor.fetchone()
                else:
                    result = cursor.fetchall()
                return result
        except:
            self.traceback()
            # 回滚操作
            self.connection.rollback()

    def traceback(self):
        with open('log.txt', 'a') as file:
            traceback.print_exc(file=file)
            file.flush()

    def close(self):
        if getattr(self,'connection',0):
            self.connection.close()

    def __del__(self):
        self.close()

if __name__ == '__main__':
    db = MysqlUtil()
    sql = 'select version()'
    result = db.find(sql,fetch_one=True)
    print(result)

三、主程序 run.py

from mysql_util import MysqlUtil
from beautifultable import BeautifulTable

def show_menu():
    print('''-----------------------------
          学生成绩管理系统 v1.0
          
          1:添加学生成绩信息
          2:查询学生成绩信息
          3:显示所有学生成绩信息
          4:删除学生成绩信息
          5:修改学生成绩信息
          6:总分成绩信息排名
          7:单科成绩信息排名
          8:查询单科成绩信息(最高分/最低分/平均分)
          0:退出系统
          
    -----------------------------''')

def confirm():
    confirm = input("确认操作请输入y,否则输入n:")
    if confirm != 'y':
        return False
    else:
        return True

def print_table(columns_header=None,rows_values=None):
    table = BeautifulTable()
    table.columns.header = columns_header
    if isinstance(rows_values,list):
        for student in rows_values:
            table.rows.append(student.values())
    else:
        table.rows.append(rows_values.values())
    print(table)

def get_student_info(student_number):
    sql = f'select {fields} from student where student_number = {student_number}'
    result = db.find(sql, fetch_one=True)
    return result


def main():
    show_menu()

    while True:
        number = int(input("请输入您的选择："))
        if number == 1: # 添加学生成绩信息
            student_number = input("请输入学号：")
            student_name = input("请输入姓名：")
            chinese = input("请输入语文成绩：")
            math = input("请输入数学成绩：")
            english = input("请输入英语成绩：")
            values = (student_number,student_name,chinese,math,english)
            sql = f'insert into student({fields}) values{values}'
            if not confirm():
                continue
            result = db.execute(sql)
            result = '添加成功' if result else '添加失败'
            print(result)
        elif number == 2: # 查询学生成绩信息
            student_number = int(input("请输入学号："))
            result = get_student_info(student_number)
            if result:
                print_table(columns_header=fields.split(','),rows_values=result)
            else:
                print("学号不存在")
        elif number == 3: # 显示所有学生成绩信息
            sql = f'select {fields} from student'
            result = db.find(sql,fetch_one=False)
            if result: # [{},{},{}}
                print_table(fields.split(','),result)
            else:
                print("暂时没有学生")
        elif number == 4: # 删除学生成绩信息
            student_number = int(input("请输入学号："))
            if not get_student_info(student_number):
                print('该学号不存在')
                continue
            sql = f'delete from student where student_number = {student_number}'
            if not confirm():
                continue
            result = db.execute(sql)
            result = '删除成功' if result else '删除失败'
            print(result)
        elif number == 5: # 修改学生成绩信息
            student_number = int(input("请输入学号："))
            if not get_student_info(student_number):
                print('该学号不存在')
                continue
            update_content = input("请输入要修改的内容，多个用英文符号来区分(例如：chinese=90,math=90,english=90):")
            update_content = update_content.replace("，",",") # 防止用户输入中文逗号
            sql = f'update student set {update_content} where student_number = {student_number}'
            if not confirm():
                continue
            result = db.execute(sql)
            result = '修改成功' if result else '修改失败'
            print(result)
        elif number == 6: # 总分成绩信息排名
            total_fields = ','.join((fields,'chinese+math+english as total'))
            order_by = 'total desc'
            sql = f'select {total_fields} from student order by {order_by}'
            # print(sql)
            result = db.find(sql)
            if result: # [{},{},{}}
                print_table(total_fields.split(','),result)
            else:
                print("暂时没有学生")
        elif number == 7: # 单科成绩信息排名
            subject = input("请输入要查询的科目，例如：chinese或者math或者english:")
            sql = f'select {fields} from student order by {subject} desc'
            result = db.find(sql)
            if result:  # [{},{},{}}
                print_table(fields.split(','),result)
            else:
                print("暂时没有学生")
        elif number == 8: # 查询单科成绩信息(最高分/最低分/平均分)
            subject = input("请输入要查询的科目，例如：chinese或者math或者english:")
            sql = f'select max({subject}) as highest,min({subject}) as lowest,avg({subject}) as average from student'
            result = db.find(sql,fetch_one=True)
            count_fields = 'highest,lowest,average'
            if result:
                print_table(count_fields.split(','),result)
            else:
                print("暂时没有该科目成绩")
        elif number == 0: # 退出系统
            print("退出成功！")
            break
        else:
            print("输入无效，请按照提示输入相应的数字")
if __name__ == '__main__':
    db = MysqlUtil()
    fields = 'student_number,student_name,chinese,math,english'
    main()