|
|
import pymysql
|
|
|
|
|
|
# 连接数据库
|
|
|
db = pymysql.connect(host="localhost", port=3306, user="root", password="xieyuxin1810", database="studentinfo")
|
|
|
cursor = db.cursor()
|
|
|
|
|
|
|
|
|
# 录入学生信息：包括学号、姓名、英语成绩、python成绩、数学成绩、总成绩
|
|
|
def input_info():
|
|
|
stu_id = input("请输入学生学号（如202001）：")
|
|
|
sql = "select * from stu_info where stu_id= %s "
|
|
|
cursor.execute(sql, stu_id)
|
|
|
rs = cursor.fetchall()
|
|
|
if len(rs) > 0:
|
|
|
print("学号已存在,请重新输入")
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
input_info()
|
|
|
else:
|
|
|
return
|
|
|
stu_name = input("请输入学生姓名：")
|
|
|
stu_score_eng = int(input("请输入学生英语成绩："))
|
|
|
stu_score_py = int(input("请输入学生python成绩："))
|
|
|
stu_score_math = int(input("请输入学生数学成绩："))
|
|
|
if len(rs) == 0:
|
|
|
sql = " insert into stu_info (stu_id,stu_name,stu_score_eng,stu_score_py,stu_score_math,stu_sum_score) values (%s,%s,%s,%s,%s,%s) "
|
|
|
cursor.execute(sql, (stu_id, stu_name, stu_score_eng, stu_score_py, stu_score_math,stu_score_eng+stu_score_py+stu_score_math))
|
|
|
db.commit()
|
|
|
print("信息录入完毕！！")
|
|
|
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
input_info()
|
|
|
else:
|
|
|
return
|
|
|
|
|
|
# 查询学生信息：查询时分为按学号和按姓名查找两种
|
|
|
def find_info():
|
|
|
find = int(input("按学号查找请输入1，按姓名查找请输入2："))
|
|
|
if find == 1:
|
|
|
stu_id = input("请输入学号:")
|
|
|
sql = "select * from stu_info where stu_id= %s "
|
|
|
cursor.execute(sql, stu_id)
|
|
|
rs = cursor.fetchall()
|
|
|
if len(rs) == 0:
|
|
|
print("学号不存在,请重新输入")
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
find_info()
|
|
|
else:
|
|
|
return
|
|
|
else:
|
|
|
sql = " select stu_id,stu_name,stu_score_eng,stu_score_py,stu_score_math,stu_sum_score from stu_info where stu_id=%s"
|
|
|
cursor.execute(sql, stu_id)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0]))
|
|
|
print("姓名:%s" % (info[1]))
|
|
|
print("英语成绩:%s" % (info[2]))
|
|
|
print("python成绩:%s" % (info[3]))
|
|
|
print("数学成绩:%s" % (info[4]))
|
|
|
print("总成绩:%s" % (info[5]))
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
find_info()
|
|
|
else:
|
|
|
return
|
|
|
elif find == 2:
|
|
|
stu_name = input("请输入姓名:")
|
|
|
sql = "select * from stu_info where stu_name= %s "
|
|
|
cursor.execute(sql, stu_name)
|
|
|
rs = cursor.fetchall()
|
|
|
if len(rs) == 0:
|
|
|
print("姓名不存在,请重新输入")
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
find_info()
|
|
|
else:
|
|
|
return
|
|
|
else:
|
|
|
sql = " select stu_id,stu_name,stu_score_eng,stu_score_py,stu_score_math ,stu_sum_score from stu_info where stu_name=%s"
|
|
|
cursor.execute(sql, stu_name)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0]))
|
|
|
print("姓名:%s" % (info[1]))
|
|
|
print("英语成绩:%s" % (info[2]))
|
|
|
print("python成绩:%s" % (info[3]))
|
|
|
print("数学成绩:%s" % (info[4]))
|
|
|
print("总成绩:%s" % (info[5]))
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
find_info()
|
|
|
else:
|
|
|
return
|
|
|
|
|
|
db.commit()
|
|
|
|
|
|
|
|
|
# 删除学生信息：输入学号进行查找，查找到学生信息之后，对学生信息进行删除
|
|
|
def del_info():
|
|
|
stu_id = input("请输入要删除的学生的学号：")
|
|
|
sql = "select * from stu_info where stu_id= %s "
|
|
|
cursor.execute(sql, stu_id)
|
|
|
rs = cursor.fetchall()
|
|
|
if len(rs) > 0:
|
|
|
sql = "delete from stu_info where stu_id= %s "
|
|
|
cursor.execute(sql, stu_id)
|
|
|
print("学号为%s的学生信息已被删除！！" % stu_id)
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
del_info()
|
|
|
else:
|
|
|
return
|
|
|
else:
|
|
|
print("无此学生信息，请核对后再操作！！")
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
del_info()
|
|
|
else:
|
|
|
return
|
|
|
db.commit()
|
|
|
|
|
|
|
|
|
# 修改学生信息：输入学号后，查询到学生信息之后，对学生信息进行修改
|
|
|
def mod_info():
|
|
|
stu_id = input("请输入要修改的学生学号：")
|
|
|
sql = "select * from stu_info where stu_id= %s "
|
|
|
cursor.execute(sql, stu_id)
|
|
|
rs = cursor.fetchall()
|
|
|
if len(rs) > 0:
|
|
|
print("已找到学生，请修改信息！")
|
|
|
stu_name = input("请输入姓名：")
|
|
|
stu_score_eng = int(input("请输入英语成绩："))
|
|
|
stu_score_py = int(input("请输入python成绩："))
|
|
|
stu_score_math = int(input("请输入数学成绩："))
|
|
|
sql = "update stu_info set stu_name = %s, stu_score_eng = %s, stu_score_py = %s, stu_score_math = %s,stu_sum_score=%s where stu_id = %s "
|
|
|
cursor.execute(sql, (stu_name, stu_score_eng, stu_score_py, stu_score_math,stu_score_eng+stu_score_py+stu_score_math, stu_id))
|
|
|
db.commit()
|
|
|
print("学号为%s的学生信息已修改！！" % stu_id)
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
mod_info()
|
|
|
else:
|
|
|
return
|
|
|
else:
|
|
|
print("无此学生信息，请核对后再操作！！")
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
mod_info()
|
|
|
else:
|
|
|
return
|
|
|
|
|
|
|
|
|
# 排序学生信息：排序的方式可选择升序和降序；可选择按英语、python、数学成绩以及总成绩进行排序
|
|
|
def sort_info():
|
|
|
sub = int(input("1.按英语成绩排序\n"
|
|
|
"2.按python成绩排序\n"
|
|
|
"3.按数学成绩排序\n"
|
|
|
"4.按学生总成绩排序\n"
|
|
|
"请选择学科:"))
|
|
|
if sub == 1:
|
|
|
order = int(input("1.升序\n"
|
|
|
"2.降序\n"
|
|
|
"请输入排序方式:"))
|
|
|
if order == 1:
|
|
|
sql = "select stu_id,stu_name, stu_score_eng, stu_score_py, stu_score_math,stu_sum_score from stu_info order by stu_score_eng asc "
|
|
|
cursor.execute(sql)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0])
|
|
|
+ "-" + "姓名:%s" % (info[1])
|
|
|
+ "-" + "英语成绩:%s" % (info[2])
|
|
|
+ "-" + "python成绩:%s" % (info[3])
|
|
|
+ "-" + "数学成绩:%s" % (info[4])
|
|
|
+ "-" + "总成绩:%s" % (info[5]))
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
sort_info()
|
|
|
else:
|
|
|
return
|
|
|
elif order == 2:
|
|
|
sql = "select stu_id,stu_name, stu_score_eng, stu_score_py, stu_score_math,stu_sum_score from stu_info order by stu_score_eng desc "
|
|
|
cursor.execute(sql)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0])
|
|
|
+ "-" + "姓名:%s" % (info[1])
|
|
|
+ "-" + "英语成绩:%s" % (info[2])
|
|
|
+ "-" + "python成绩:%s" % (info[3])
|
|
|
+ "-" + "数学成绩:%s" % (info[4])
|
|
|
+ "-" + "总成绩:%s" % (info[5]))
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
sort_info()
|
|
|
else:
|
|
|
return
|
|
|
elif sub == 2:
|
|
|
order = int(input("1.升序\n"
|
|
|
"2.降序\n"
|
|
|
"请输入排序方式:"))
|
|
|
if order == 1:
|
|
|
sql = "select stu_id,stu_name, stu_score_eng, stu_score_py, stu_score_math,stu_sum_score from stu_info order by stu_score_py asc "
|
|
|
cursor.execute(sql)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0])
|
|
|
+ "-" + "姓名:%s" % (info[1])
|
|
|
+ "-" + "英语成绩:%s" % (info[2])
|
|
|
+ "-" + "python成绩:%s" % (info[3])
|
|
|
+ "-" + "数学成绩:%s" % (info[4])
|
|
|
+ "-" + "总成绩:%s" % (info[5]))
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
sort_info()
|
|
|
else:
|
|
|
return
|
|
|
elif order == 2:
|
|
|
sql = "select stu_id,stu_name, stu_score_eng, stu_score_py, stu_score_math,stu_sum_score from stu_info order by stu_score_py desc "
|
|
|
cursor.execute(sql)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0])
|
|
|
+ "-" + "姓名:%s" % (info[1])
|
|
|
+ "-" + "英语成绩:%s" % (info[2])
|
|
|
+ "-" + "python成绩:%s" % (info[3])
|
|
|
+ "-" + "数学成绩:%s" % (info[4])
|
|
|
+ "-" + "总成绩:%s" % (info[5]))
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
sort_info()
|
|
|
else:
|
|
|
return
|
|
|
elif sub == 3:
|
|
|
order = int(input("1.升序\n"
|
|
|
"2.降序\n"
|
|
|
"请输入排序方式:"))
|
|
|
if order == 1:
|
|
|
sql = "select stu_id,stu_name, stu_score_eng, stu_score_py, stu_score_math ,stu_sum_score from stu_info order by stu_score_math asc "
|
|
|
cursor.execute(sql)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0])
|
|
|
+ "-" + "姓名:%s" % (info[1])
|
|
|
+ "-" + "英语成绩:%s" % (info[2])
|
|
|
+ "-" + "python成绩:%s" % (info[3])
|
|
|
+ "-" + "数学成绩:%s" % (info[4])
|
|
|
+ "-" + "总成绩:%s" % (info[5]))
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
sort_info()
|
|
|
else:
|
|
|
return
|
|
|
elif order == 2:
|
|
|
sql = "select stu_id,stu_name, stu_score_eng, stu_score_py, stu_score_math,stu_sum_score from stu_info order by stu_score_math desc "
|
|
|
cursor.execute(sql)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0])
|
|
|
+ "-" + "姓名:%s" % (info[1])
|
|
|
+ "-" + "英语成绩:%s" % (info[2])
|
|
|
+ "-" + "python成绩:%s" % (info[3])
|
|
|
+ "-" + "数学成绩:%s" % (info[4])
|
|
|
+ "-" + "总成绩:%s" % (info[5]))
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
sort_info()
|
|
|
else:
|
|
|
return
|
|
|
elif sub == 4:
|
|
|
order = int(input("1.升序\n"
|
|
|
"2.降序\n"
|
|
|
"请输入排序方式:"))
|
|
|
if order == 1:
|
|
|
sql = "select stu_id,stu_name, stu_score_eng, stu_score_py, stu_score_math," \
|
|
|
"stu_sum_score " \
|
|
|
"from stu_info order by sumSore asc "
|
|
|
cursor.execute(sql)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0])
|
|
|
+ "-" + "姓名:%s" % (info[1])
|
|
|
+ "-" + "英语成绩:%s" % (info[2])
|
|
|
+ "-" + "python成绩:%s" % (info[3])
|
|
|
+ "-" + "数学成绩:%s" % (info[4])
|
|
|
+ "-" + "总成绩:%s" % (info[5]))
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
sort_info()
|
|
|
else:
|
|
|
return
|
|
|
|
|
|
elif order == 2:
|
|
|
sql = "select stu_id,stu_name, stu_score_eng, stu_score_py, stu_score_math," \
|
|
|
"stu_sum_score " \
|
|
|
"from stu_info order by sumSore asc "
|
|
|
cursor.execute(sql)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0])
|
|
|
+ "-" + "姓名:%s" % (info[1])
|
|
|
+ "-" + "英语成绩:%s" % (info[2])
|
|
|
+ "-" + "python成绩:%s" % (info[3])
|
|
|
+ "-" + "数学成绩:%s" % (info[4])
|
|
|
+ "-" + "总成绩:%s" % (info[5]))
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
sort_info()
|
|
|
else:
|
|
|
return
|
|
|
else:
|
|
|
print("请输入以上选项")
|
|
|
go = input("是否进行继续输入,输入Y继续")
|
|
|
if go == "Y":
|
|
|
sort_info()
|
|
|
else:
|
|
|
return
|
|
|
|
|
|
# 统计学生总数
|
|
|
# 输出信息管理系统中有几个学生的信息
|
|
|
def sum_info():
|
|
|
sql = "select * from stu_info"
|
|
|
cursor.execute(sql)
|
|
|
rs = cursor.fetchall()
|
|
|
print("一共有%s名学生。" % len(rs))
|
|
|
|
|
|
|
|
|
|
|
|
# 显示学生信息
|
|
|
def show_info():
|
|
|
sql = "select stu_id,stu_name, stu_score_eng, stu_score_py, stu_score_math,stu_sum_score from stu_info"
|
|
|
cursor.execute(sql)
|
|
|
rs = cursor.fetchall()
|
|
|
for info in rs:
|
|
|
print("学号:%s" % (info[0])
|
|
|
+ "-" + "姓名:%s" % (info[1])
|
|
|
+ "-" + "英语成绩:%s" % (info[2])
|
|
|
+ "-" + "python成绩:%s" % (info[3])
|
|
|
+ "-" + "数学成绩:%s" % (info[4])
|
|
|
+ "-" + "总成绩:%s" % (info[5]))
|
|
|
|
|
|
def show_choose():
|
|
|
print("==========================学生信息管理系统==========================")
|
|
|
print()
|
|
|
print("-----------------------------功能菜单-----------------------------")
|
|
|
print()
|
|
|
print(" 1.录入学生信息")
|
|
|
print(" 2.查找学生信息")
|
|
|
print(" 3.删除学生信息")
|
|
|
print(" 4.修改学生信息")
|
|
|
print(" 5.排序学生信息")
|
|
|
print(" 6.统计学生总数")
|
|
|
print(" 7.显示学生信息")
|
|
|
print(" 0.退出信息管理系统")
|
|
|
print()
|
|
|
print("----------------------------------------------------------------")
|
|
|
|
|
|
|
|
|
def main():
|
|
|
while True:
|
|
|
show_choose()
|
|
|
choose_menu = input("请选择功能菜单：")
|
|
|
if choose_menu == '0':
|
|
|
break
|
|
|
elif choose_menu == '1':
|
|
|
input_info()
|
|
|
elif choose_menu == '2':
|
|
|
find_info()
|
|
|
elif choose_menu == '3':
|
|
|
del_info()
|
|
|
elif choose_menu == '4':
|
|
|
mod_info()
|
|
|
elif choose_menu == '5':
|
|
|
sort_info()
|
|
|
elif choose_menu == '6':
|
|
|
sum_info()
|
|
|
elif choose_menu == '7':
|
|
|
show_info()
|
|
|
else:
|
|
|
break
|
|
|
print("欢迎您再次使用！！")
|
|
|
db.close()
|
|
|
|
|
|
|
|
|
if __name__ == '__main__':
|
|
|
main()
|
|
|
|
|
|
|
