python成绩管理系统-数据库版本

一，说明：

本系统功能是利用python来编写代码，同时利用数据库文件相关功能，来实现数据库成绩管理系统。同时提供其他两个版本的数据库版本的成绩管理系统来提供借鉴，仅供参考，切勿抄袭。

二，第一个版本（本地数据库化带日志文件）：

#基于学生成绩管理系统,使用本地数据库和之前的成绩管理系统代码进行整编，有用户管理功能，带有日志，及相关数据库文件生成
#作者：xxx  本地数据库，因为VM虚拟机搭建WEB站点动态IP及NAT限制，为使其他人能访问，使用本地数据库
#一些功能模块参考一些优秀的GitHub的第三方作者,理解优秀代码其中的含义，根据实际要求，并最终写出相应的功能，并要感谢优秀的GitHub的作者
import logging
import hashlib
import shelve
#引入并定义学生类
class student():
    def __init__(self,id,ch,ma,en):
        self.id=id
        self.ch=ch
        self.ma=ma
        self.en=en
    def __str__(self):
        return f"学号:{self.id}\n总分:{self.total()}\n"
    def total(self):
        return self.ch+self.ma+self.en
    def pm(self,alist):
        return len(list(filter(lambda i:i.total()>self.total(),alist)))+1
        
#定义全局变量默认值
user=''
datas=[]
accouts={"admin":{"pwd":"21232f297a57a5a743894a0e4a801fc3","role":"1234567890"},"zhangsan":{"pwd":"e10adc3949ba59abbe56e057f20f883e","role":"12360"},"1":{"pwd":"c4ca4238a0b923820dcc509a6f75849b","role":"1234567890"}}
funcdic=["-----------------本程序为学生成绩管理系统，请选择系统功能：---------------------\n",
    "                 1.增加学生信息;\n",
    "                 2.修改学生的相关信息;\n",
    "                 3.删除学生的相关信息;\n",
    "                 4.查询学生信息;\n",
    "                 5.学生成绩分类统计;\n",
    "                 6.学生成绩排序;\n",
    "                 7.显示所有学生的信息；\n",
    "                 8.用户管理；\n",
    "                 0.退出程序;\n"]
subjectdic={"语文":0,"数学":1,"英语":2}#定义字典
logging.basicConfig(level= logging.DEBUG,
                    filename = "xxgl.log",
                    format   = "%(asctime)s  %(filename)s : %(levelname)s  %(message)s",
                    datefmt  = "%Y-%m-%d %H:%M:%S",
                    filemode = "a")
                    
#数据库初始化，并输入带数据库的数据
def init():#此模块可将已有数据库进行合一处理
    global datas,accouts
    with shelve.open("mydb") as f:
        try:
            datas=f["data"]
            accouts=f["user"]
            logging.info("从数据库读入信息成功！")
        except:
            f["data"]=datas
            f["user"]=accouts
            logging.warning("数据库恢复到初始值！")
#用户登录
def login():
    global user
    uname=input("请输入用户名：")
    passwd=input("请输入密码：")
    password=hashlib.md5(passwd.encode()).hexdigest()
    if uname in accouts:
        if password==accouts[uname]["pwd"]:
            logging.info(f"用户{uname}登录成功！")
            user=uname
            return
    print("用户名或密码错！")
    logging.warning(f"用户{uname}登录失败！")
#信息保存
def save():
    with shelve.open("mydb") as f:#打开相关数据库文件，进行统一处理
        f["data"]=datas
        f["user"]=accouts
#打印学生信息表
def printdata(f,title):
    table=sorted(datas,key=f)
    print(f"{title:-^70s}")
    print(f'{"学号":^10s}{"语文":^10s}{"数学":^10s}{"英语":^10s}{"总分":^10s}{"名次":^10s}')
    print("-"*75)
    for i in table:     
        print(f'{i.id:^12s}{i.ch:^12d}{i.ma:^12d}{i.en:^12d}{i.total():^12d}{i.pm(datas):^12d}')
    print("-"*75)
    logging.info(f"{user}打印了{title}!")
#增加学生信息
def add1():
    print("添加学生成绩：：".center(65,'-'))
    global datas
    
    b=[i.id for i in datas]
    while 1:
        id=input("请输入学号：").strip()
        if id not in b:
            break
        print('该生信息已存在，请重新输入！')
    ch=int(input("语文:").strip())
    ma=int(input("数学:").strip())
    en=int(input("英语:").strip())
    datas.append(student(id,ch,ma,en))
    save()
    logging.info(f"{user}增加了一条学生记录，学号{id}。")
#成绩汇总及各科统计
def datafx():
    ch=[i.ch for i in datas]
    ma=[i.ma for i in datas]
    en=[i.en for i in datas]
    print("语文成绩统计".center(50,"-"))
    print(f"平均分:{sum(ch)/len(ch):40.2f}")
    print(f"及格率:{100*sum([i>=60 for i in ch])/len(ch):40.2f}%")
    print(f"优秀率:{100*sum([i>=80 for i in ch])/len(ch):40.2f}%\n")
    print("数学成绩统计".center(50,"-"))
    print(f"平均分:{sum(ma)/len(ma):40.2f}")
    print(f"及格率:{100*sum([i>=60 for i in ma])/len(ma):40.2f}%")
    print(f"优秀率:{100*sum([i>=80 for i in ma])/len(ma):40.2f}%\n")
    print("英语成绩统计".center(50,"-"))
    print(f"平均分:{sum(en)/len(en):40.2f}")
    print(f"及格率:{100*sum([i>=60 for i in en])/len(en):40.2f}%")
    print(f"优秀率:{100*sum([i>=80 for i in en])/len(en):40.2f}%\n")
    logging.info(f"{user}查看了成绩分析。")
#查看学员成绩
def query():
    print("查看学生成绩：".center(65,'-'))
    b=[i.id for i in datas]
    while 1:
        id=input("请输入学号：")
        if id in b:
            break
        print('该生信息不存在，请重新输入！')
    a=list(filter(lambda i:i.id==id,datas))[0]   
    print(f"语文成绩：{a.ch}")
    print(f"数学成绩：{a.ma}")
    print(f"英语成绩：{a.en}\n")
    logging.info(f"{user}查看了学生{id}的成绩。")
#修改学生成绩
def update1():
    print("修改学生成绩：".center(65,'-'))
    b=[i.id for i in datas]
    while 1:
        id=input("请输入该学生学号：")
        if id in b:
            break
        print('该生信息不存在，请重新输入！')
    a=list(filter(lambda i:i.id==id,datas))[0]    
    s1=int(input("语文成绩："))
    s2=int(input("数学成绩："))
    s3=int(input("英语成绩："))
    a.ch=s1
    a.ma=s2
    a.en=s3
    save()
    print('修改成功')
    logging.info(f"{user}修改了学生{id}的成绩。")
#删除学生信息
def delete():
    print("删除学生信息：".center(65,'-'))
    global datas#全局变量声明
    b=[i.id for i in datas]
    while 1:
        id=input("请输入学号：")
        if id in b:
            break
        print('该生信息不存在，请重新输入！')
    a=list(filter(lambda i:i.id!=id,datas))
    datas=a
    save()
    logging.info(f"{user}删除了该学生{id}。")
#用户管理
def manage():
    global accouts
    print("功能列表：")
    while 1:
        while 1:
            print("用户管理功能".center(70,'-'))
            op=input("1增加用户 2删除用户 3修改密码 4查询用户  5退出 \n").strip()
            if op in list("12345"):
                break
            print("输入有误！")
        if op=="1":    #添加用户
            print(f"{'添加用户':-^60s}")
            uname=input("用户名:").strip()
            pd=input("密码：").strip()
            pd=hashlib.md5(pd.encode()).hexdigest()
            ro=input("权限：").strip()
            if uname in accouts:
                print("用户已存在！")
            else:
                accouts[uname]={"pwd":pd,"role":ro}
                logging.warning(f"{user}新建了账户{uname}!")
                save()
        elif op=="2":    #删除账户
            print(f"{'删除账户':-^60s}")
            uname=input("用户名:").strip()
            if uname in accouts:
                del accouts[uname]
                save()
                logging.warning(f"{user}删除了账户{uname}!")
            else:
                print("该学生用户名不存在！")
        elif op=="3":    #修改密码权限
            print(f"{'修改密码权限':-^60s}")
            while 1:
                uname=input("请输入用户名").strip()
                if uname not in accouts:
                    print('您输入的用户不存在，请重新输入')
                else: 
                    break
            pwd1=input("新密码：")
            pwd2=input("确认密码：")            
            if pwd1==pwd2:
                pd=hashlib.md5(pwd1.encode()).hexdigest()
                accouts[uname]["pwd"]=pd
                save()
                logging.warning(f"{user}修改了{uname}账户密码!")
            else:
                print("两次密码不一致！")
            quanxian=input("请输入权限：")
            accouts[uname]['role']=quanxian
            save()
            logging.warning(f"{user}修改了{uname}账户权限!")
        elif op=="4":   #查询用户信息
            print(f"{'查询用户信息':-^60s}")
            print(f"{'用户名':^10s}{'密码':^35s}{'权限':^10s}")
            for k,v in accouts.items():
                print(f"{k:^13s}{v['pwd']:^37s}{v['role']:^12s}")
            print()
            logging.warning(f"{user}查询了账户信息!")
        else:
            break
while 1:
    init()
    login()
    if user in accouts:
        role=accouts[user]["role"]                
        print("欢迎使用成绩管理系统".center(60,"-"))
        print("作者：xxx   初始化数据库版本\n".center(65))
        while 1:
            print("功能列表：")
            for i in range(10):
                print(funcdic[i])
            while 1:
                select=input("你的选择是:").strip()
                if select in role:
                    break
                print("输入有误，请选择功能编号！")
            if select=="1":        #增加学生信息,
                add1()
            elif select=="2":        #修改学生信息
                update1()
            elif select=="3":        #删除学生信息
                delete()
            elif select=="4":        #查询学生信息
                query()
            elif select=="5":        #成绩汇总显示及各类统计
                datafx()
            elif select=="6":        # 成绩排序
                printdata(lambda i:i.pm(datas),"成绩排名表")
            elif select=="7":          #所有成绩显示
                printdata(lambda i:i.id,"学生成绩单")
            elif select=="8":        #用户管理功能
                manage()
            elif select=="0":
                break
            else:
                logging.info(f"{user}退出了系统。")
                exit()

三，第二个版本（带图形化页面的）：

"""
学生信息管理系统（Tkinter、mariadb实现）
版本：2.0
作者：xxx
简述：该系统可使用管理员和学生身份登录，
    管理员身份拥有功能：显示所有学生信息、学生排序、修改信息、删除信息、查找信息
    学生身份拥有功能：查看自身信息
    系统可在多个界面自由切换，不同身份间自由切换，拥有错误输入处理能力，其他功能拓展能力

    2.0版本更新：
    新增：mariadb数据库操作，如果连接成功所以操作后的数据将上传数据库，若无法连接数据库原本的功能不会受到影响
    修复少量bug，对界面进行优化，增加按钮接触变化效果

main.py需要配合user.py使用
"""

from tkinter import *
from tkinter.ttk import Treeview
import tkinter.messagebox
import user124
import pymysql


# ------------------全局变量-------------------------
# 连接数据库的标志
FlAG_CONNECT = 0
# 数据库游标
CURSOR = 0
# 数据库连接类
DB = 0

# -------------------------Tkinter基本框架-----------------------------------
root = Tk()
root.title('学生信息管理系统  --by xxx')  # 窗口标题
root.geometry('600x450')  # 窗口大小
root.resizable(width=False, height=False)  # 设置窗口无法拉伸
frame = Frame(root)

# ----------------------------数据结构-----------------------------------------------------------
student_list = [{'学号': '101', '姓名': '张三', '性别': '男', '语文': '100', '数学': '100', '英语': '50'},
                {'学号': '102', '姓名': '李四', '性别': '男', '语文': '9', '数学': '100', '英语': '9'},
                {'学号': '103', '姓名': '王五', '性别': '男', '语文': '60', '数学': '77', '英语': '70'},
                {'学号': '104', '姓名': '赵六', '性别': '男', '语文': '90', '数学': '80', '英语': '66'},
                {'学号': '105', '姓名': '小红', '性别': '女', '语文': '50', '数学': '88', '英语': '80'},
                {'学号': '106', '姓名': '老王', '性别': '男', '语文': '78', '数学': '60', '英语': '60'}]

cols = ('学号', '姓名', '性别', '语文(点击排序)', '数学(点击排序)', '英语(点击排序)')


# ---------------------------以下是各功能模块的函数-----------------------------------------------
def connect_mariadb():
    """
    用于连接数据库
    :return:
    """
    global student_list
    global FlAG_CONNECT
    global CURSOR
    global DB
    # 建立连接
    try:
        DB = pymysql.connect(host="", user="", passwd="",
                             db="gdupt", charset='utf8')
        # 使用 cursor() 方法创建一个游标对象 cursor
        CURSOR = DB.cursor(cursor=pymysql.cursors.DictCursor)
        # 使用 execute()  方法执行 SQL 查询
        CURSOR.execute("SELECT VERSION()")
        # 使用 fetchone() 方法获取单条数据.
        data = CURSOR.fetchone()
        print("Database version : %s " % data)

        CURSOR.execute("select * from studentinfo")
        student_list = CURSOR.fetchall()
        # print(student_list)
        FlAG_CONNECT = 1

    except pymysql.err.OperationalError:
        print("无法连接数据库，将使用初始数据")
        return


def treeview_sort_column(tv, col, reverse):
    """
    功能：对学生信息进行排序
    :param tv: treeview类
    :param col: treeview列表
    :param reverse: 正反序
    """
    try:
        lis = [(tv.set(k, col), k) for k in tv.get_children('')]
        lis.sort(key=lambda t: int(t[0]), reverse=reverse)

        for index, (val, k) in enumerate(lis):
            tv.move(k, '', index)
        tv.heading(col, command=lambda: treeview_sort_column(tv, col, not reverse))
    except ValueError:
        return


def admin_modify(student_id):
    """
    功能：管理员身份对学生信息进行修改
    :param student_id: 学生序号
    """
    for widget in root.winfo_children():
        widget.destroy()
    Label(root, text='学生信息管理系统', font=('Arial', 16), width=16, height=2).pack()
    Label(root, text='修改学生信息', font=('Arial', 12), width=16, height=1).pack()
    try:
        if len(student_id) > 1:
            stu_id = '1' + str(student_id)
        else:
            stu_id = '10' + str(student_id)
        Label(root, text="学号：{0}".format(student_list[int(student_id) - 1]['学号']),
              font=('Arial', 12), width=10, height=1).place(x=210, y=100, anchor='nw')
        Label(root, text="姓名：", font=('Arial', 12), width=10, height=1).place(x=196, y=130, anchor='nw')
        entry_name = Entry(root, show=None)
        entry_name.insert(0, str(student_list[int(student_id) - 1]['姓名']))
        entry_name.place(x=270, y=130, anchor='nw')

        Label(root, text="性别：", font=('Arial', 12), width=10, height=1).place(x=196, y=160, anchor='nw')
        entry_gender = Entry(root, show=None)
        entry_gender.insert(0, str(student_list[int(student_id) - 1]['性别']))
        entry_gender.place(x=270, y=160, anchor='nw')

        Label(root, text="语文：", font=('Arial', 12), width=10, height=1).place(x=196, y=190, anchor='nw')
        entry_chinese = Entry(root, show=None)
        entry_chinese.insert(0, str(student_list[int(student_id) - 1]['语文']))
        entry_chinese.place(x=270, y=190, anchor='nw')
        Label(root, text="原：{}分".format(student_list[int(student_id) - 1]['语文']),
              font=('Arial', 12), width=8, height=1).place(x=420, y=190, anchor='nw')
        student_list[int(student_id) - 1]['语文'] = entry_chinese.get()

        Label(root, text="数学：", font=('Arial', 12), width=10, height=1).place(x=196, y=220, anchor='nw')
        entry_math = Entry(root, show=None)
        entry_math.insert(0, str(student_list[int(student_id) - 1]['数学']))
        entry_math.place(x=270, y=220, anchor='nw')
        Label(root, text="原：{}分".format(student_list[int(student_id) - 1]['数学']),
              font=('Arial', 12), width=8, height=1).place(x=420, y=220, anchor='nw')

        Label(root, text="英语：", font=('Arial', 12), width=10, height=1).place(x=196, y=250, anchor='nw')
        entry_english = Entry(root, show=None)
        entry_english.insert(0, str(student_list[int(student_id) - 1]['英语']))
        entry_english.place(x=270, y=250, anchor='nw')
        Label(root, text="原：{}分".format(student_list[int(student_id) - 1]['英语']),
              font=('Arial', 12), width=8, height=1).place(x=420, y=250, anchor='nw')

        def update_student():
            global FlAG_CONNECT
            student_list[int(student_id) - 1]['姓名'] = entry_name.get()
            student_list[int(student_id) - 1]['性别'] = entry_gender.get()
            student_list[int(student_id) - 1]['语文'] = entry_chinese.get()
            student_list[int(student_id) - 1]['数学'] = entry_math.get()
            student_list[int(student_id) - 1]['英语'] = entry_english.get()
            # -------------------数据库操作相关-------------------------------------
            try:
                sql = "update studentinfo set 姓名='%s',性别='%s',语文='%s',数学='%s',英语='%s' where 学号=%s;" \
                    % (entry_name.get(), entry_gender.get(), entry_chinese.get(), entry_math.get(),
                       entry_english.get(), stu_id)
                CURSOR.execute(sql)
                DB.commit()
            except pymysql.err.OperationalError:
                print("数据库已断开连接，无法保存数据")
                FlAG_CONNECT = 0
            except AttributeError:
                print("数据库已断开连接，无法保存数据")
                FlAG_CONNECT = 0
            # --------------------------------------------------------------------
        Button(root, text='确认', width=10, height=1, overrelief='groove',
               command=lambda: (update_student(), admin_display())).place(x=250, y=310, anchor='nw')
        Button(root, text='←返回', width=10, height=1, overrelief='groove',
               command=lambda: admin_display()).place(x=500, y=12, anchor='nw')
    except TypeError:
        admin_display()


def admin_add_student():
    """
    功能：管理员身份增加学生信息
    """
    for widget in root.winfo_children():
        widget.destroy()
    Label(root, text='学生信息管理系统', font=('Arial', 16), width=16, height=2).pack()
    Label(root, text='增加学生信息', font=('Arial', 12), width=16, height=1).pack()
    Label(root, text="学号：{0}".format(str(int(student_list[0]['学号']) + len(student_list))),
          font=('Arial', 12), width=10, height=1).place(x=210, y=100, anchor='nw')

    Label(root, text="姓名：", font=('Arial', 12), width=10, height=1).place(x=196, y=130, anchor='nw')
    entry_name = Entry(root, show=None)
    entry_name.insert(0, '小明')
    entry_name.place(x=270, y=130, anchor='nw')

    Label(root, text="性别：", font=('Arial', 12), width=10, height=1).place(x=196, y=160, anchor='nw')
    entry_gender = Entry(root, show=None)
    entry_gender.insert(0, '男')
    entry_gender.place(x=270, y=160, anchor='nw')

    Label(root, text="语文：", font=('Arial', 12), width=10, height=1).place(x=196, y=190, anchor='nw')
    entry_chinese = Entry(root, show=None)
    entry_chinese.insert(0, '80')
    entry_chinese.place(x=270, y=190, anchor='nw')

    Label(root, text="数学：", font=('Arial', 12), width=10, height=1).place(x=196, y=220, anchor='nw')
    entry_math = Entry(root, show=None)
    entry_math.insert(0, '80')
    entry_math.place(x=270, y=220, anchor='nw')

    Label(root, text="英语：", font=('Arial', 12), width=10, height=1).place(x=196, y=250, anchor='nw')
    entry_english = Entry(root, show=None)
    entry_english.insert(0, '80')
    entry_english.place(x=270, y=250, anchor='nw')

    def update_student():
        global FlAG_CONNECT
        stu_id = str(int(student_list[0]['学号']) + len(student_list))
        stu_name = entry_name.get()
        stu_gender = entry_gender.get()
        stu_chinese = entry_chinese.get()
        stu_math = entry_math.get()
        stu_english = entry_english.get()
        student_list.append({'学号': stu_id, '姓名': stu_name, '性别': stu_gender, '语文': stu_chinese, '数学': stu_math,
                             '英语': stu_english})
        # -------------------数据库操作相关-------------------------------------
        try:
            sql = "insert into studentinfo(学号,姓名,性别,语文,数学,英语)values(%s,%s,%s,%s,%s,%s);"
            param = (stu_id, stu_name, stu_gender, stu_chinese, stu_math, stu_english)
            CURSOR.execute(sql, param)
            DB.commit()
        except pymysql.err.OperationalError:
            print("数据库已断开连接，无法保存数据")
            FlAG_CONNECT = 0
        except AttributeError:
            print("数据库已断开连接，无法保存数据")
            FlAG_CONNECT = 0
        # -------------------------------------------------------------------
    Button(root, text='确认', width=10, height=1, overrelief='groove',
           command=lambda: (update_student(), admin_display())).place(x=250, y=310, anchor='nw')
    Button(root, text='←返回', width=10, height=1, overrelief='groove',
           command=lambda: admin_display()).place(x=500, y=12, anchor='nw')


def admin_delete_student(tv, student_id):
    """
    功能：管理员身份删除学生
    :param tv: treeview类
    :param student_id: 学生序号
    """
    global FlAG_CONNECT
    try:
        if len(student_id) > 1:
            stu_id = '1' + str(student_id)
        else:
            stu_id = '10'+str(student_id)
        tv.item('I00' + str(student_id), values=(student_list[int(student_id) - 1]['学号'],
                                                 '空', '空', '0', '0', '0'))
        student_list[int(student_id) - 1]['姓名'] = 'None'
        student_list[int(student_id) - 1]['性别'] = 'None'
        student_list[int(student_id) - 1]['语文'] = '0'
        student_list[int(student_id) - 1]['数学'] = '0'
        student_list[int(student_id) - 1]['英语'] = '0'
        # -------------------数据库操作相关-------------------------------------
        try:
            sql = "update studentinfo set 姓名='%s',性别='%s',语文='%s',数学='%s',英语='%s' where 学号=%s;" \
                % ("空", "空", "0", "0", "0", stu_id)
            CURSOR.execute(sql)
            DB.commit()
        except pymysql.err.OperationalError:
            print("数据库已断开连接，无法保存数据")
            FlAG_CONNECT = 0
        except AttributeError:
            print("数据库已断开连接，无法保存数据")
            FlAG_CONNECT = 0
        # -------------------------------------------------------------------
    except TclError:
        return
    except TypeError:
        return


def admin_find_student(entry):
    """
    功能：管理员身份查找学生
    :param entry: Entry类
    """
    student_id = entry.get()
    student_name = entry.get()
    for stu in student_list:
        if stu['姓名'] == student_name:
            tkinter.messagebox.showinfo('查找窗口', "该学生信息如下：               \n"
                                                "学号：{0} \n姓名：{1} \n性别：{2} \n语文：{3} \n数学：{4} \n英语：{5}"
                                        .format(stu['学号'], stu['姓名'], stu['性别'], stu['语文'], stu['数学'],
                                                stu['英语']))
            return
    try:
        tkinter.messagebox.showinfo('查找窗口', "该学生信息如下：               \n"
                                            "学号：{0} \n姓名：{1} \n性别：{2} \n语文：{3} \n数学：{4} \n英语：{5}"
                                    .format(student_list[int(student_id) - 1]['学号'],
                                            student_list[int(student_id) - 1]['姓名'],
                                            student_list[int(student_id) - 1]['性别'],
                                            student_list[int(student_id) - 1]['语文'],
                                            student_list[int(student_id) - 1]['数学'],
                                            student_list[int(student_id) - 1]['英语']))
    except IndexError:
        tkinter.messagebox.showinfo("查找窗口", "查找失败，请输入学号最后一位或姓名")
    except ValueError:
        tkinter.messagebox.showinfo("查找窗口", "查找失败，请输入学号最后一位或姓名")


def focus(tv):
    """
    获取treeview焦点
    :param tv: treeview类
    """
    try:
        return tv.focus()[-1]
    except IndexError:
        return


def admin_display():
    """
    功能：显示管理员界面
    """
    for widget in root.winfo_children():
        widget.destroy()
    admin_object.student_index = 0  # 用于防止重复打印学生信息
    if FlAG_CONNECT:
        Label(root, text='数据库连接成功', font=('Arial', 12), fg='red', width=16, height=2).place(x=0, y=0, anchor='nw')
    else:
        Label(root, text='数据库连接失败', font=('Arial', 12), fg='red', width=16, height=2).place(x=0, y=0, anchor='nw')

    Label(root, text='学生信息管理系统', font=('Arial', 16), width=16, height=2).place(x=190, y=0, anchor='nw')
    ybar = Scrollbar(root, orient='vertical')
    treeview = Treeview(root, height=31, show="headings", columns=cols, yscrollcommand=ybar.set)
    ybar['command'] = treeview.yview
    for col in cols:
        treeview.heading(col, text=col, command=lambda _col=col: treeview_sort_column(treeview, _col, False))
        treeview.column(col, width=10, anchor='n')  # 每一行的宽度,'w'意思为靠右

    ybar.pack(side=RIGHT, fill=Y)
    treeview.place(x=0, y=80, anchor='nw', width=550)

    Button(root, text='更新学生信息', width=10, height=1, overrelief='groove',
           command=lambda _treeview=treeview: admin_object.print_student(_treeview, CURSOR,
                                                                         FlAG_CONNECT)).place(x=8, y=43, anchor='nw')
    Button(root, text='修改（选中信息）', width=15, height=1, overrelief='groove',
           command=lambda: admin_modify(focus(treeview))).place(x=100, y=43, anchor='nw')
    Button(root, text='增加学生', width=8, height=1, overrelief='groove',
           command=lambda: admin_add_student()).place(x=228, y=43, anchor='nw')
    Button(root, text='删除学生', width=8, height=1, overrelief='groove',
           command=lambda: admin_delete_student(treeview, focus(treeview))).place(x=306, y=43, anchor='nw')
    entry_find = Entry(root, show=None)
    entry_find.place(x=390, y=48, anchor='nw')
    Button(root, text='查找', width=3, height=1, overrelief='groove',
           command=lambda: admin_find_student(entry_find)).place(x=540, y=43, anchor='nw')
    Button(root, text='←退出', width=10, height=1, overrelief='groove',
           command=lambda: login_display()).place(x=500, y=12, anchor='nw')


def student_zhangsan_display():
    """
    功能：显示学生张三界面
    """
    for widget in root.winfo_children():
        widget.destroy()
    if FlAG_CONNECT:
        Label(root, text='数据库连接成功', font=('Arial', 12), fg='red', width=16, height=2).place(x=0, y=0, anchor='nw')
    else:
        Label(root, text='数据库连接失败', font=('Arial', 12), fg='red', width=16, height=2).place(x=0, y=0, anchor='nw')
    Label(root, text='学生信息管理系统', font=('Arial', 16), width=16, height=2).pack()
    Label(root, text='学号：{0}'.format(student_list[0]['学号']),
          font=('Arial', 16), width=16, height=2).place(x=196, y=80, anchor='nw')
    Label(root, text='姓名：{0}'.format(student_list[0]['姓名']),
          font=('Arial', 16), width=16, height=2).place(x=200, y=120, anchor='nw')
    Label(root, text='性别：{0}'.format(student_list[0]['性别']),
          font=('Arial', 16), width=16, height=2).place(x=190, y=160, anchor='nw')
    Label(root, text='语文：{0}'.format(student_list[0]['语文']),
          font=('Arial', 16), width=16, height=2).place(x=196, y=200, anchor='nw')
    Label(root, text='数学：{0}'.format(student_list[0]['数学']),
          font=('Arial', 16), width=16, height=2).place(x=196, y=240, anchor='nw')
    Label(root, text='英语：{0}'.format(student_list[0]['英语']),
          font=('Arial', 16), width=16, height=2).place(x=190, y=280, anchor='nw')
    Button(root, text='←退出', width=10, height=1, overrelief='groove',
           command=lambda: login_display()).place(x=500, y=12, anchor='nw')


def login(radiobutton_var, entry_account, entry_password):
    """
    功能：登录界面信息处理
    :param radiobutton_var: radiobutton类
    :param entry_account: Entry类
    :param entry_password: Entry类
    """
    # ---------------------------------管理员身份登录------------------------------------------------------
    if radiobutton_var.get() == 'admin':
        if entry_account.get() == admin_object.account and entry_password.get() == admin_object.password:
            # --管理员身份组件--
            admin_display()
        else:
            Label(root, text='账号或密码错误', font=('Arial', 12),
                  width=15, height=1, fg='red').place(x=220, y=190, anchor='nw')
    # ------------------------------------学生身份登录-----------------------------------------------------------
    elif radiobutton_var.get() == 'student':
        if entry_account.get() == student_object.account and entry_password.get() == student_object.password:
            # --学生身份组件--
            student_zhangsan_display()
    else:
        Label(root, text='未选择登录身份', font=('Arial', 12),
              width=15, height=1, fg='red').place(x=220, y=190, anchor='nw')


def login_display():
    """
    功能：显示登录界面所有组件
    """
    for widget in root.winfo_children():
        widget.destroy()
    Label(root, text='学生信息管理系统', font=('Arial', 16), width=16, height=5).pack()
    Label(root, text='请选择登录身份', font=('Arial', 12), fg='red', width=12, height=3).place(x=210, y=80, anchor='nw')

    radiobutton_var = StringVar()
    radiobutton_var.set(' ')
    student_radiobutton = Radiobutton(root, text='学生', font=('Arial', 12),
                                      variable=radiobutton_var, value='student')
    student_radiobutton.place(x=210, y=120, anchor='nw')
    admin_radiobutton = Radiobutton(root, text='老师或管理员', font=('Arial', 12),
                                    variable=radiobutton_var, value='admin')
    admin_radiobutton.place(x=210, y=150, anchor='nw')

    Label(root, text='账号', font=('Arial', 12), width=5, height=1).place(x=180, y=220, anchor='nw')
    entry_account = Entry(root, show=None)
    entry_account.insert(0, '1234')
    entry_account.place(x=230, y=220, anchor='nw')
    Label(root, text='密码', font=('Arial', 12), width=5, height=1).place(x=180, y=250, anchor='nw')
    entry_password = Entry(root, show='*')
    entry_password.insert(0, '1234')
    entry_password.place(x=230, y=250, anchor='nw')

    Button(root, text='登录', width=15, height=2, overrelief='groove',
           command=lambda: login(radiobutton_var, entry_account, entry_password)).place(x=240, y=280, anchor='nw')


if __name__ == '__main__':
    connect_mariadb()
    # --------------实例化管理员类和学生类------------------
    admin_object = user124.Admin(cols, student_list)
    student_object = user124.StudentZhangsan()

    login_display()
    root.mainloop()

class Admin:
    def __init__(self, cols, stu_list):
        self.account = '1234'
        self.password = '1234'
        self.cols = cols
        self.student_list = stu_list
        self.student_index = 0 # 用于防止重复打印学生信息

    def print_student(self, tv, cursor, connect):
        if connect == 1:
            cursor.execute("select * from studentinfo")
            self.student_list = cursor.fetchall()
        # print(self.student_list)
        if self.student_index == 0:
            for student_index in self.student_list:
                tv.insert("", "end", values=(student_index['学号'], student_index['姓名'],
                                             student_index['性别'], student_index['语文'],
                                             student_index['数学'], student_index['英语']))
        self.student_index = 1


class StudentZhangsan:
    def __init__(self):
        self.account = '1234'
        self.password = '1234'

四，第三个版本（通常的数据库版本）:

# -*-coding:utf-8-*-
import pymysql


def menu():
    print("-------------学生信息管理系统-------------")
    print("-" * 35)
    print("           功能菜单          ")
    print("          1、添加学生的信息           ")
    print("          2、删除学生的信息           ")
    print("          3、修改学生的信息           ")
    print("          4、查询学生的信息           ")
    print("          5、显示所有学生的信息        ")
    print("          6、对学生成绩进行排序        ")
    print("          7、退出本系统              ")
    print("-" * 35)


def connection():
    try:
        db = pymysql.connect(host="", user="", passwd="", db="", port=3306,charset="utf8")
        print('数据库连接成功!')
    except pymysql.Error as e:
        print('数据库连接失败' + str(e))
    db.close()


def CreateTable():
    db = pymysql.connect(host="", user="", passwd="", db="", port=3306,charset="utf8")
    cur = db.cursor()
    cur.execute('DROP TABLE IF EXISTS Student')
    sql = "CREATE TABLE studentinfo (Name varchar(20) NOT NULL,Sex varchar(4) NOT NULL ,Chinese float,Math float,English float )"
    cur.execute(sql)
    db.close()


def Insert():  # 添加
    db = pymysql.connect(host="", user="", passwd="", db="", port=3306,charset="utf8")
    cur = db.cursor()
    sql = " INSERT INTO studentinfo (ID, Name, Sex, Chinese, Math, English, Total) VALUE (%s,%s,%s,%s,%s,%s,%s) "
    a = input("请输入需要添加的学生学号：")
    b = input("请输入需要添加的学生姓名：")
    c = input("请输入需要添加的学生性别：")
    d = input("请输入需要添加的学生语文成绩：")
    e = input("请输入需要添加的学生数学成绩：")
    f = input("请输入需要添加的学生英语成绩：")
    total = int(d) + int(e) + int(f)
    value = (a, b, c, d, e, f, total)

    try:
        cur.execute(sql, value)
        db.commit()
        print('学生信息添加成功！')
    except pymysql.Error as e:
        print("学生信息添加失败：" + str(e))
        db.rollback()
    db.close()


def deletedata():  # 删除
    db = pymysql.connect(host="", user="", passwd="", db="", port=3306,charset="utf8")
    cur = db.cursor()
    sql = "DELETE FROM studentinfo where ID=%s"
    a = input("请输入要删除学生的学号：")
    value = (a)
    try:
        cur.execute(sql, value)
        db.commit()
        print('学生信息删除成功')
    except pymysql.Error as e:
        print("学生信息删除失败：" + str(e))
        # 发生错误时回滚
        db.rollback()


def querystudentinfo(querystudentid):  # 查询
    db = pymysql.connect(host="", user="", passwd="", db="", port=3306,charset="utf8")
    cursor = db.cursor()
    sql = "select * from studentinfo where ID=%s" % querystudentid
    cursor.execute(sql)
    if cursor.rowcount == 0:
        print("不存在该学生信息")
        return False
    else:
        print("该学生信息如下：")
        results = cursor.fetchall()  # 获取数据
        print("学号=%s,姓名=%s,性别=%s,语文=%s,数学=%s,英语=%s,总分=%s" % \
              (results[0][0], results[0][1], results[0][2], results[0][3], results[0][4], results[0][5], results[0][6]))
        return True


def update():  # 修改
    modifyid = input("请输入要修改的学生学号：")
    if querystudentinfo(modifyid):
        name = input("请重新输入学生姓名：")
        sex = input("请重新输入学生性别：")

        chinese = input("请重新输入要修改的学生语文成绩：")
        while not chinese.isdigit() or float(chinese) > 100 or float(chinese) < 0:
            chinese = input("输入错误，请重新输入：")

        math = input("请重新输入要修改的学生数学成绩：")
        while not math.isdigit() or float(math) > 100 or float(math) < 0:
            math = input("输入错误，请重新输入：")

        english = input("请重新输入要修改的学生英语成绩：")
        while not english.isdigit() or float(english) > 100 or float(english) < 0:
            english = input("输入错误，请重新输入：")

        print("  学生信息修改成功！  ")

        total = float(chinese) + float(math) + float(english)


        db = pymysql.connect(host="", user="", passwd="", db="", port=3306,charset="utf8")
        cursor = db.cursor()
        sql1 = "update studentinfo set name ='%s' where id = %s" % (name, modifyid)
        cursor.execute(sql1)
        sql2 = "update studentinfo set sex ='%s' where id = %s" % (sex, modifyid)
        cursor.execute(sql2)
        sql3 = "update studentinfo set chinese = %s where id = %s" % (chinese, modifyid)
        cursor.execute(sql3)
        sql4 = "update studentinfo set math = %s where id = %s" % (math, modifyid)
        cursor.execute(sql4)
        sql5 = "update studentinfo set english = %s where id =%s" % (english, modifyid)
        cursor.execute(sql5)
        sql6 = "update studentinfo set total = %s where id = %s" % (total, modifyid)
        cursor.execute(sql6)

        db.commit()
        db.close()


def Find():  # 显示
    db = pymysql.connect(host="", user="", passwd="", db="", port=3306,charset="utf8")
    cur = db.cursor()
    sql = "SELECT * FROM studentinfo"
    try:
        cur.execute(sql)
        results = cur.fetchall()
        print("=" * 30)
        print("学号      姓名     性别     语文      数学      英语      总分")
        for row in results:
            id = row[0]
            name = row[1]
            sex = row[2]
            chinese = row[3]
            math = row[4]
            english = row[5]
            total = row[6]
            print('%s     %s     %s      %s     %s      %s     %s' % (id , name, sex, chinese, math, english, total))
        print("=" * 30)
    except pymysql.Error as e:
        print("数据显示失败：" + str(e))
    db.close()


def sort():  # 排序
    db = pymysql.connect(host="", user="", passwd="", db="", port=3306,charset="utf8")
    cursor = db.cursor()
    sql = "select * from studentinfo"
    cursor.execute(sql)
    results = cursor.fetchall()
    result = list(results)
    result.sort(key=lambda row: row[6], reverse=True)
    print("学生信息按总成绩排序如下：")
    print("=" * 30)
    print("学号   姓名   性别   语文    数学    英语    总分")
    for row in result:
        id = row[0]
        name = row[1]
        sex = row[2]
        chinese = row[3]
        math = row[4]
        english = row[5]
        total = row[6]
        # 打印结果

        print('%s     %s     %s      %s     %s      %s     %s ' % (id , name, sex, chinese, math, english, total))
    print("=" * 30)

def main():
    connection()
    while True:
        menu()
        number = input("请输入功能菜单的对应数字：")
        if number == '1':
            Insert()
        elif number == '2':
            deletedata()
        elif number == '3':
            update()
        elif number == '4':
            querystudentid = input("请输入要查询的学生学号：")
            querystudentinfo(querystudentid)
        elif number == '5':
            Find()
        elif number == '6':
            sort()
        elif number >= '7':
            print("                                ")
            print("********* 欢迎您再次使用！！！*********")
            break
main()

文章或代码如有错误和纰漏，欢迎批评指正！！！

谢谢！！！