mysql按学号删除学生信息_mysql增删改和学生管理sql

importpymysqlimporttimedefprint_infos():print("=" * 30)print("欢迎使用学生管理系统")print("1,添加学生信息")print("2,查找学生信息")print("3,删除学生信息")print("4,修改学生信息")print("5.显示学生信息")print("6,退出系统")print("=" * 30)#录学生信息

defadd_infos():

id= input("请输入学号")

name= input("请输入你的名字")

age= input("请输入你的年龄")

sex= input("请输入你的性别")

addr= input("请输入你的地址")

conn= pymysql.connect(host="localhost", port=3306, user="root", passwd='root', db='李森',charset='utf8')

cur=conn.cursor()

sql= "insert into student_1 values(%s,%s,%s,%s,%s)"cur.execute(sql, (id, name,age,sex,addr))

conn.commit()

cur.close()

conn.close()

time.sleep(1.4)#查找学生信息

deffind_infos():

a= input("按学号查输入1；按姓名查输//入2：")if a == "1":

id= input("请输入学生ID：")

conn= pymysql.connect('localhost', user='root', passwd='root', db='李森')

cur=conn.cursor()

sql= "select * from student_1 where id = %s"cur.execute(sql, id)

a_1=cur.fetchone()if a_1 isNone:print("无数据信息")else:print("ID：%d，名字：%s，年龄：%s，性别：%s，地址：%s，" % (a_1[0], a_1[1], a_1[2], a_1[3], a_1[4],))

conn.commit()

cur.close()

conn.close()elif a == "2":

name= input("请输入学生名字：")

conn= pymysql.connect('localhost', user='root', passwd='root', db='李森')

cur=conn.cursor()

sql= "select * from student_1 where name = %s"cur.execute(sql, name)

a_2=cur.fetchone()if a_2 isNone:print("无数据信息")else:print("ID：%d，名字：%s，年龄：%s，性别：%s，地址：%s" % (a_2[0], a_2[1], a_2[2], a_2[3], a_2[4],))

conn.commit()

cur.close()

conn.close()

time.sleep(1.4)#删除学生信息

defdel_infos():

id= input("请输入要删除的学生学号：")

conn= pymysql.connect('localhost', user='root', passwd='root', db='李森')

cur=conn.cursor()

sql= "delete from student_1 where id = %s"cur.execute(sql, id)print("ID为%s的学生信息已经被删除。。。" %id)

cur.execute("select * from student_1")while 1:

num=cur.fetchone()if num ==None:break

print("ID：%d，名字：%s，年龄：%s,性别：%s，地址：%s" % (num[0], num[1], num[2], num[3], num[4]))

conn.commit()

cur.close()

conn.close()

time.sleep(1.4)#修改学生信息

defalter_infos():

conn= pymysql.connect('localhost', user='root', passwd='root', db='李森')

cur=conn.cursor()

cur.execute("select * from student_1")while 1:

num=cur.fetchone()if num ==None:break

print("ID：%d，名字：%s，年龄：%s，性别：%s，地址：%s" % (num[0], num[1], num[2], num[3], num[4]))

id= input("请输入要修改的学生ID：")print("找到了这名学生，可以修改他的信息！")

name= input("请输入姓名：")

age= input("请输入年龄：")

sex= input("请输入性别：")

addr= input("请输入地址：")

sql_1= "update student_1 set name = %s where id = %s"cur.execute(sql_1, (name, id))

sql_2= "update student_1 set age = %s where id = %s"cur.execute(sql_2, (age, id))

sql_3= "update student_1 set sex = %s where id = %s"cur.execute(sql_3, (sex, id))

sql_4= "update student_1 set addr = %s where id = %s"cur.execute(sql_4, (addr, id))

conn.commit()

cur.close()

conn.close()

time.sleep(1.4)#显示所有学生信息

defshow_infos():

conn= pymysql.connect('localhost', user='root', passwd='root', db='李森')

cur=conn.cursor()

cur.execute("select * from student_1")while 1:

num=cur.fetchone()if num ==None:break

print("ID：%d，名字：%s，年龄：%s，性别：%s，地址：%s" % (num[0], num[1], num[2], num[3], num[4]))

conn.commit()

cur.close()

conn.close()

time.sleep(1.4)whileTrue:

print_infos()

num= input("请选择：")if num == "1":while 1:

add_infos()

aa= input("是否继续添加？(y/n)：")if aa == "y":continue

else:break

elif num == "2":while 1:

find_infos()

bb= input("是否继续查询？(y/n)：")if bb == "y":continue

else:break

elif num == "3":while 1:

del_infos()

cc= input("是否继续删除？(y/n)：")if cc == "y":continue

else:break

elif num == "4":while 1:

alter_infos()

dd= input("是否继续修改其他学生信息？(y/n)：")if dd == "y":continue

else:break

elif num == "5":

show_infos()elif num == "6":print("欢迎下次使用")break

else:print("输入错误，请重新输入。")