import pymysql
def menu():
print("\t\t学生信息管理系统\t\t")print("\t\t1 增加学生\t\t")print("\t\t2 删除学生\t\t")print("\t\t3 批改学生\t\t")print("\t\t4 按问题降序排序：\t\t")print("\t\t5 按问题升序排序：\t\t")print("\t\t6 查问：\t\t")print("\t\t7 退出程序\t\t")passdef insert():
print("插入学生信息")name = input("请输出学生姓名：")sex = input("请输出学生性别：")snum = input("请输出学生学号：")chinese = int(input("请输出学生语文问题："))math = int(input("请输出学生数学问题："))stotal = int(input("请输出学生总成绩："))db = pymysql.connect(host='localhost', user='root', password='333', database='studentdb')cursor = db.cursor()sql = "insert into st(sname,ssex,snum,chinese,math,stotal) values('%s','%s','%s',%d,%d,%d)"%(name,sex,snum,chinese,math,stotal)try: cursor.execute(sql) db.commit()except: db.rollback() print("error")db.close()passdef delete():
while 1: print("删除学生信息") choose1 = int(input("请输入您的删除形式：1 按姓名 2 按学号: ")) if choose1 == 1: print("按姓名删除") strname = input("请输出删除学生的名字：") db = pymysql.connect(host='localhost', user='root', password='333', database='studentdb') cursor = db.cursor() sql1 = "select *from st where sname='%s'"%(strname) cursor.execute(sql1) if cursor.rowcount>0: try: sql = "DELETE FROM st WHERE sname = '%s'" % (strname) cursor.execute(sql) db.commit() print("删除姓名为%s的学生信息胜利" % (strname)) break except: db.rollback() print("error") db.close() else: print("没有这个学生的信息，输出谬误") break elif choose1 == 2: print("按学号删除") strnum = input("请输出删除学生的学号：") db = pymysql.connect(host='localhost', user='root', password='333', database='studentdb') cursor = db.cursor() sql1 = "select *from st where snum='%s'"%(strnum) cursor.execute(sql1) if cursor.rowcount>0: sql = "DELETE FROM st WHERE snum = '%s'" % (strnum) try: cursor.execute(sql) db.commit() print("删除学号为%s学生的信息胜利" % (strnum)) break except: db.rollback() print("error") db.close() else: print("没有这个学生的信息，输出谬误") break else: print("输出谬误，从新输出") break pass passpassdef change():
while 1: print("扭转学生信息") myname = input("请输出扭转学生的名字:") db = pymysql.connect(host='localhost', user='root', password='333', database='studentdb') cursor = db.cursor() sql1 = "select *from st where sname='%s'"%(myname) cursor.execute(sql1) results = cursor.fetchall() name12='0' for row in results: name12 = row[0] if myname == name12: try: sex = input("请输出学生性别：") snum = input("请输出学生学号：") chinese = int(input("请输出学生语文问题：")) math = int(input("请输出学生数学问题：")) stotal = int(input("请输出学生总成绩：")) #有问题解决不了 sql3 = "update st set ssex ='%s',snum='%s',chinese=%d,math=%d,stotal=%d where sname='%s'"%(sex,snum,chinese,math,stotal,name12) cursor.execute(sql3) db.commit() print("批改姓名为%s学生的信息胜利"%(myname)) break except: db.rollback() print("error") db.close() break else: print("没有这个学生的信息，输出谬误") break pass passpassdef sortbyscoredesc():
print("按问题降序")db = pymysql.connect(host='localhost', user='root', password='333', database='studentdb')cursor = db.cursor()sql = "select * from st order by stotal desc"try: cursor.execute(sql) results = cursor.fetchall() for row in results: name1 = row[0] sex1 = row[1] num1 = row[2] chinses1 = row[3] math1 = row[4] total1 = row[5] # 打印后果 print("学生姓名:%s\t,学生性别:%s\t,学生学号:%s\t,语文问题:%d\t,数学问题:%d\t,总成绩:%d\t" % \ (name1, sex1, num1, chinses1, math1, total1)) db.commit()except: db.rollback() print("error")db.close()passdef sortbyscore():
print("按问题升序排序")db = pymysql.connect(host='localhost', user='root', password='333', database='studentdb')cursor = db.cursor()sql = "select * from st order by stotal asc "try: cursor.execute(sql) results = cursor.fetchall() for row in results: name1 = row[0] sex1 = row[1] num1 = row[2] chinses1 = row[3] math1 = row[4] total1 = row[5] # 打印后果 print("[农产品期货](https://www.gendan5.com/cf/af.html)学生姓名:%s\t,学生性别:%s\t,学生学号:%s\t,语文问题:%d\t,数学问题:%d\t,总成绩:%d\t" % \ (name1, sex1, num1, chinses1, math1, total1)) db.commit()except: db.rollback() print("error")db.close()passdef select():
print("查问学生信息")db = pymysql.connect(host='localhost',user='root',password='333',database='studentdb')cursor = db.cursor()sql = "select * from st"try: cursor.execute(sql) results = cursor.fetchall() for row in results: name1 = row[0] sex1 = row[1] num1 = row[2] chinses1 = row[3] math1 = row[4] total1 = row[5] # 打印后果 print("学生姓名:%s\t,学生性别:%s\t,学生学号:%s\t,语文问题:%d\t,数学问题:%d\t,总成绩:%d\t" % \ (name1,sex1,num1,chinses1,math1,total1)) db.commit()except: db.rollback() print("error")db.close()passdef exitexe():
while 1: print("确定要退出零碎吗？退出(y) 不退出(n)") c = input("请输入您的抉择aaaa：\t") if c == 'y': exit() elif c == 'n': break else: print("输出有误 从新输出") passpassif name == '__main__':
while True: menu() choose = int(input("请输入您的抉择:\t")) if choose == 1: insert() elif choose == 2: delete() elif choose == 3: change() elif choose == 4: sortbyscoredesc() elif choose == 5: sortbyscore() elif choose == 6: select() elif choose == 7: exitexe() pass pass