pp7bw52jf
/
cursor.execute
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'main'
${ noResults }
cursor.execute/main.py

389 lines
16 KiB
Raw Permalink Normal View History Unescape

ADD file via upload
6 months ago
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()