请先安装：pymysql, xlwt，numpy，pandas
 

pip3 install pymysql xlwt numpy pandas
 

注意：请先把数据库数据类型修改为utf-8：

创建库：

create databases student;

创建表：

create table grades values(name varchar(50), age int, ywscore int, sxscore int, yyscore int, count int);

# 学生信息系统
import pymysql, xlwt 
import numpy as np
import pandas as pd
import time, sys
 
def main():
    while True:
        print('+-----------------------------------+')
        print('|1)  添加学生信息                     ')
        print('|2)  查询学生信息                     ')
        print('|3)  修改学生信息                     ')
        print('|4)  删除学生信息                     ')
        print('|5)  按成绩高-->低显示学生信息         ')
        print('|6)  按成绩低-->高显示学生信息         ')
        print('|7)  按年龄高-->低显示学生信息         ')
        print('|8)  按年龄底-->高显示学生信息         ')
        print('|9)  将学生信息写入文件                ')
        print('|10) 查看导出的文件信息                ')
        print('|11) 查看所有成绩                      ')
        print('|q)  退出                             ')
        print('+-----------------------------------+')
        my_number = input('请选择:')
        try:
            if my_number == '1':
                add_student()
            elif my_number == '2':
                cat_student()
            elif my_number == '3':
                set_student()
            elif my_number == '4':
                del_student()
            elif my_number == '5':
                resort_score_student()
            elif my_number == '6':
                sort_score_student()
            elif my_number == '7':
                resort_age_student()
            elif my_number == '8':
                sort_age_student()
            elif my_number == '9':
                write_student()
            elif my_number == '10':
                read_student()
            elif my_number == '11':
                cat_student_all()
            elif my_number == 'q':
                break
        except:
            print('输入错误')
#进度条显示            
def speeds():
    print("正在执行")
    for i in range(11):
        if i != 10:
            sys.stdout.write("......")
        else:
            sys.stdout.write(". " + str(i*10)+"%/100%")
        sys.stdout.flush()
        time.sleep(0.1)
    print("\n" + "执行完成")
    
#增加信息
def add_student():
    #连接数据库
    host, user, passwd, db='127.0.0.1','root','123.com','student'
    conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
    while True:
        print('按Enter结束')
        name = str(input('请输入学生姓名:'))
        if not name:
            break  
        age = int(input('请输入学生年龄:'))
        ywscore = int(input('请输入学生语文成绩:'))
        sxscore = int(input('请输入学生数学成绩:'))
        yyscore = int(input('请输入学生英语成绩:'))
        speeds()
 
        # 得到一个可以执行SQL语句的光标对象
        cursor = conn.cursor()
        sql = "insert into grades VALUES(%s,%s,%s,%s,%s,%s);"
        names = name
        ages = int(age)
        ywscores = int(ywscore)
        sxscores = int(sxscore)
        yyscores = int(yyscore)
        count = ywscores+sxscores+yyscores
 
        # 执行SQL语句
        cursor.execute(sql, [names, ages, ywscores, sxscores, yyscores, count])
 
    # 提交事务
    conn.commit()
    cursor.close()
    conn.close()
    return 
 
# 查看所有学生成绩
def cat_student_all():
    host, user, passwd, db='127.0.0.1','root','123.com','student'
    conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
    cursor = conn.cursor()
    cat_sql = "select * from grades;"
    cursor.execute(cat_sql)
# 获取多条查询数据
    ret = cursor.fetchall()
    rets = list(ret)
    speeds()
 
    print('+---------+---------+---------+---------+---------+---------+')
    print('|  name   |   age   | ywscore | sxscore | yyscore |  count  |')
    print('+---------+---------+---------+---------+---------+---------+')
    for i in range(len(rets)):
        info = ''
        for j in range(len(rets[i])):
            if j == 0:
                if len(rets[i][j]) == 2:
                    name_0=' '
                    name_1=((rets[i][j])[0])
                    name_3='  '
                    name_2=((rets[i][j])[1])
                    name_4=name_0+name_1+name_3+name_2
                    info += ('%4s  |' %name_4)
                else:
                    info += ('%4s  |' %rets[i][j])
            else:
                info += ('%7s  |' %rets[i][j])
        print('|',end='')
        print(info)
        del info
        print('+---------+---------+---------+---------+---------+---------+')
    cursor.close()
    conn.close()
    return
 
#查看信息
def cat_student():
    host, user, passwd, db='127.0.0.1','root','123.com','student'
    conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
    while True:
        print('按Enter结束')
        s_name = input('请输入要查询的姓名：')
        
        if not s_name:
            break  
        speeds()
        print('+---------+---------+---------+---------+---------+---------+')
        print('|  name   |   age   | ywscore | sxscore | yyscore |  count  |')
        print('+---------+---------+---------+---------+---------+---------+')
        cursor = conn.cursor()
        cat_sql = "select * from grades where name=%s;"
#     执行SQL语句
        cursor.execute(cat_sql, [s_name])
        ret = cursor.fetchone()
        rets = list(ret)
        print('|', end='')
        info = ''
        for i in range(len(rets)):
            if i == 0:
                if len(rets[i]) == 2:
                    name_1=((rets[i])[0])
                    name_2=((rets[i])[1])
                    name_4=' '+name_1+'  '+name_2
                    info += ('%4s  |' %name_4)
                else:
                    info += ('%4s  |' %rets[i])
            else:
                info += ('%6s   |' %rets[i])
        print(info)
        print('+---------+---------+---------+---------+---------+---------+')
        del info
    # 提交事务
    conn.commit()
    cursor.close()
    conn.close()
    return
 
#修改信息
def set_student():
    host, user, passwd, db='127.0.0.1','root','123.com','student'
    conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
    while True:
        print('按Enter结束')
        s_name = str(input('请输入要修改成绩的学生姓名:'))
        if not s_name:
            break  
        set_score = str(input('请输入需要修改的哪科成绩:（ywscore,sxscore,yyscore）'))
        s_core = int(input('请输入成绩：'))
        if set_score == 'ywscore':
            cursor = conn.cursor()
            cat_sql = "UPDATE grades SET ywscore=%s WHERE name=%s;"
            cursor.execute(cat_sql, [s_core, s_name])
            conn.commit()
            speeds()
        elif set_score == 'sxscore':
            cursor = conn.cursor()
            cat_sql = "UPDATE grades SET sxscore=%s WHERE name=%s;"
            cursor.execute(cat_sql, [s_core, s_name])
            conn.commit()
            speeds()
        elif set_score == 'yyscore':
            cursor = conn.cursor()
            cat_sql = "UPDATE grades SET yyscore=%s WHERE name=%s;"
            cursor.execute(cat_sql, [s_core, s_name])
            conn.commit()
            speeds()
        cursor = conn.cursor()
        cat_sql = "select * from grades where name=%s;"
        cursor.execute(cat_sql, [s_name])
        ret = cursor.fetchone()
        cun = ret[2]+ret[3]+ret[4]
        cursor = conn.cursor()
        cat_sql = "UPDATE grades SET count=%s WHERE name=%s;"
        cursor.execute(cat_sql, [cun, s_name])
 
    conn.commit()
    cursor.close()
    conn.close()
    return
    
#删除信息
def del_student():
    host, user, passwd, db='127.0.0.1','root','123.com','student'
    conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
    while True:
        print('按Enter结束')
        s_name = str(input('请输入要删除的学生姓名:'))
        if not s_name:
            conn.commit()
            cursor.close()
            conn.close()
            break  
        cursor = conn.cursor()
        cat_sql = "DELETE FROM grades WHERE name=%s;"
        cursor.execute(cat_sql, [s_name])
    speeds()
    conn.commit()
    cursor.close()
    conn.close()
    return
 
#按成绩低-->高显示学生信息
def sort_score_student():
    host, user, passwd, db='127.0.0.1','root','123.com','student'
    conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
    cursor = conn.cursor()
    cat_sql = "select * from grades;"
    cursor.execute(cat_sql)
# 获取多条查询数据
    ret = cursor.fetchall()
    rets = list(ret)
    retss = (sorted(rets,key=lambda x:x[5], reverse=False))
 
    speeds()
    print()
    print('+---------+---------+---------+---------+---------+---------+')
    print('|  name   |   age   | ywscore | sxscore | yyscore |  count  |')
    print('+---------+---------+---------+---------+---------+---------+')
    for i in range(len(retss)):
        info = ''
        for j in range(len(retss[i])):
            if j == 0:
                if len(retss[i][j]) == 2:
                    name_0=' '
                    name_1=((retss[i][j])[0])
                    name_3='  '
                    name_2=((retss[i][j])[1])
                    name_4=name_0+name_1+name_3+name_2
                    info += ('%4s  |' %name_4)
                else:
                    info += ('%4s  |' %retss[i][j])
            else:
                info += ('%7s  |' %retss[i][j])
        print('|',end='')
        print(info)
        del info
        print('+---------+---------+---------+---------+---------+---------+')
    cursor.close()
    conn.close()
    return
 
#按成绩高-->低显示学生信息
def resort_score_student():
#    my_list = my_list.sort(key='score',reverse=True)
    host, user, passwd, db='127.0.0.1','root','123.com','student'
    conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
    cursor = conn.cursor()
    cat_sql = "select * from grades;"
    cursor.execute(cat_sql)
# 获取多条查询数据
    ret = cursor.fetchall()
    rets = list(ret)
    retss = (sorted(rets,key=lambda x:x[5], reverse=True))
 
    speeds()
    print()
    print('+---------+---------+---------+---------+---------+---------+')
    print('|  name   |   age   | ywscore | sxscore | yyscore |  count  |')
    print('+---------+---------+---------+---------+---------+---------+')
    for i in range(len(retss)):
        info = ''
        for j in range(len(retss[i])):
            if j == 0:
                if len(retss[i][j]) == 2:
                    name_0=' '
                    name_1=((retss[i][j])[0])
                    name_3='  '
                    name_2=((retss[i][j])[1])
                    name_4=name_0+name_1+name_3+name_2
                    info += ('%4s  |' %name_4)
                else:
                    info += ('%4s  |' %retss[i][j])
            else:
                info += ('%7s  |' %retss[i][j])
 
        print('|',end='')
        print(info)
        del info
        print('+---------+---------+---------+---------+---------+---------+')
    cursor.close()
    conn.close()
    return
 
#按年龄底-->高显示学生信息 
def sort_age_student():
#    my_list = my_list.sort(key='age',reverse=False)
    host, user, passwd, db='127.0.0.1','root','123.com','student'
    conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
    cursor = conn.cursor()
    cat_sql = "select * from grades;"
    cursor.execute(cat_sql)
# 获取多条查询数据
    ret = cursor.fetchall()
    rets = list(ret)
    retss = (sorted(rets,key=lambda x:x[1], reverse=False))
 
    speeds()
    print()
    print('+---------+---------+---------+---------+---------+---------+')
    print('|  name   |   age   | ywscore | sxscore | yyscore |  count  |')
    print('+---------+---------+---------+---------+---------+---------+')
    for i in range(len(retss)):
        info = ''
        for j in range(len(retss[i])):
            if j == 0:
                if len(retss[i][j]) == 2:
                    name_0=' '
                    name_1=((retss[i][j])[0])
                    name_3='  '
                    name_2=((retss[i][j])[1])
                    name_4=name_0+name_1+name_3+name_2
                    info += ('%4s  |' %name_4)
                else:
                    info += ('%4s  |' %retss[i][j])
            else:
                info += ('%7s  |' %retss[i][j])
        print('|',end='')
        print(info)
        del info
        print('+---------+---------+---------+---------+---------+---------+')
    cursor.close()
    conn.close()
    return
 
#按年龄高-->低显示学生信息  
def resort_age_student():
    host, user, passwd, db='127.0.0.1','root','123.com','student'
    conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
    cursor = conn.cursor()
    cat_sql = "select * from grades;"
    cursor.execute(cat_sql)
# 获取多条查询数据
    ret = cursor.fetchall()
    rets = list(ret)
    retss = (sorted(rets,key=lambda x:x[1], reverse=True))
 
    speeds()
    print()
    print('+---------+---------+---------+---------+---------+---------+')
    print('|  name   |   age   | ywscore | sxscore | yyscore |  count  |')
    print('+---------+---------+---------+---------+---------+---------+')
    for i in range(len(retss)):
        info = ''
        for j in range(len(retss[i])):
#             info += rets[i][j]
            if j == 0:
                if len(retss[i][j]) == 2:
                    name_0=' '
                    name_1=((retss[i][j])[0])
                    name_3='  '
                    name_2=((retss[i][j])[1])
                    name_4=name_0+name_1+name_3+name_2
                    info += ('%4s  |' %name_4)
                else:
                    info += ('%4s  |' %retss[i][j])
            else:
                info += ('%7s  |' %retss[i][j])
        print('|',end='')
        print(info)
        del info
        print('+---------+---------+---------+---------+---------+---------+')
    cursor.close()
    conn.close()
    return
 
#将学生信息表写入文件
def write_student():
    host, user, passwd, db='127.0.0.1','root','123.com','student'
    conn = pymysql.connect(user=user,host=host,port=3306,passwd=passwd,db=db,charset='utf8')
    cur = conn.cursor()
    sql = 'select * from grades;'
    cur.execute(sql)  # 返回受影响的行数
    fields = [field[0] for field in cur.description]  # 获取所有字段名
    all_data = cur.fetchall()  # 所有数据
 
    # 写入excel
    book = xlwt.Workbook()
    sheet = book.add_sheet('sheet1')
    
    for col,field in enumerate(fields):
        sheet.write(0,col,field)
    
    row = 1
    for data in all_data:
        for col,field in enumerate(data):
            sheet.write(row,col,field)
        row += 1
    book.save("/flq/python/jupyter/student.xlsx")
    speeds()
    return
 
#以规格读文件的学生信息
def read_student():
    df = pd.read_excel('/flq/python/jupyter/student.xlsx')
    data=df.head(99999999999999)
    speeds()
    print('获取到所有数据:\n{0}'.format(data))
 
if __name__ == '__main__':
    main()

原文链接:https://blog.csdn.net/flq18210105507/article/details/119040839