mysql增删改和学生管理sql

importpymysql
#2.建连
conn = pymysql.connect("localhost","root",'root','李森')
print(conn)
#3.获取游标
cur =conn.cursor()
#4.增
sql="insert into student_1 values(default,%s,%s,%s,%s)"cur.execute(sql,('张三','4','男','上海'))
#删
sql ="delete from student_1 where id = 33"cur.execute(sql)
#改
sql = "update student_1 set name = %s where id = %s"cur.execute(sql,('卫思敏',"20"))
#5.提交
conn.commit()
#6.关游标,关连接
cur.close()
conn.close()

importpymysql

importtime
defprint_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("输入错误，请重新输入。")