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

本文主要是介绍使用面向对象和数据库完成学生成绩管理系统，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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()

这篇关于使用面向对象和数据库完成学生成绩管理系统的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！