python学生管理系统

import pymysql
# 连接数据库
connect = pymysql.connect(
            host="localhost",
            user="root",
            password="160386",
            db="school"
)
# 创建游标
cur = connect.cursor()
# 执行语句，返回受影响的行
cur.execute("SET @@global.sql_mode= ''")

# 管理员函数
def manage():
    name = input("请管理员输入用户名：")
    password = input("请管理员输入密码：")

    sql = "SELECT * FROM manage WHERE name=%s AND password=%s"
    val = (name, password)
    cur.execute(sql, val)
    result = cur.fetchone()

    if result:
        print("登录成功！")
        while True:
            print("\n*****管理员登录界面*****")
            print("1. 查看学生信息")
            print("2. 增加学生信息")
            print("3. 修改学生信息")
            print("4. 删除学生信息")
            print("5. 查看课程信息")
            print("6. 增加课程信息")
            print("7. 修改课程信息")
            print("8. 删除课程信息")
            print("9. 退出管理员登录界面")
            print("请选择要进行的操作：")
            choice = input()
            if choice == "1":
                # 查看学生信息
                sql = "SELECT * FROM student"
                cur.execute(sql)
                result = cur.fetchall()
                for student in result:
                    print(student)
            elif choice == "2":
                # 增加学生信息
                print("请输入学生信息：")
                student_id = input("学号：")
                student_name = input("姓名：")
                student_gender = input("性别：")
                student_email = input("邮箱：")
                student_phone = input("电话：")
                student_password = input("密码：")
                sql = "INSERT INTO student (id, name, gender,email,phone,password ) VALUES (%s, %s, %s, %s,%s,%s)"
                val = (student_id, student_name, student_gender, student_email, student_phone, student_password)
                cur.execute(sql, val)
                connect.commit()
                print("学生信息添加成功！")

            elif choice == "3":
                # 修改学生信息
                student_id = input("请输入要修改的学号：")
                sql = "SELECT * FROM student WHERE id = %s"
                val = (student_id,)
                cur.execute(sql, val)
                result = cur.fetchall()
                if len(result) == 0:
                    print("找不到该学生！")
                    continue
                print('原学生信息为：', result[0])
                print("请输入修改后的学生信息：")
                student_name = input("姓名：")
                student_gender = input("性别：")
                student_email = input("邮箱：")
                student_phone = input("电话：")
                student_password = input("密码：")
                sql = "UPDATE student SET name = %s, gender = %s,email = %s,phone=%s,password=%s WHERE id = %s"
                val = (student_name, student_gender, student_email, student_phone, student_password, student_id)
                cur.execute(sql, val)
                connect.commit()
                sql = "UPDATE grade SET student_name = %s WHERE student_id = %s"
                val = (student_name, student_id)
                cur.execute(sql, val)
                connect.commit()
                sql = "UPDATE selected SET student_name = %s WHERE student_id = %s"
                val = (student_name,student_id)
                cur.execute(sql, val)
                connect.commit()
                print("学生信息修改成功！")

            elif choice == "4":
                # 删除学生信息
                student_id = input("请输入要删除的学生学号：")
                sql = "SELECT * FROM student WHERE id = %s"
                val = (student_id,)
                cur.execute(sql, val)
                result = cur.fetchall()
                if len(result) == 0:
                    print("找不到该学生！")
                    continue
                # 删除学生表中的记录
                sql = "DELETE FROM student WHERE id = %s"
                cur.execute(sql, val)
                connect.commit()
                # 删除选课表中的记录
                sql = "DELETE FROM selected WHERE student_id = %s"
                cur.execute(sql, val)
                connect.commit()
                # 删除成绩表中的记录
                sql = "DELETE FROM grade WHERE student_id = %s"
                cur.execute(sql, val)
                connect.commit()
                print("学生信息删除成功！")

            elif choice == "5":
                # 查看课程信息
                sql = "SELECT * FROM course"
                cur.execute(sql)
                result = cur.fetchall()
                for course in result:
                    print(course)

            elif choice == "6":
                # 增加课程信息
                print("请输入课程信息：")
                course_id = input("课程ID：")
                course_name = input("课程名称：")
                course_teacher = input("主讲教师ID：")
                sql = "INSERT INTO course (id, name, teacher) VALUES (%s, %s, %s)"
                val = (course_id, course_name, course_teacher)
                cur.execute(sql, val)
                connect.commit()
                sql = "SELECT * FROM teacher WHERE id = %s"
                val1 = (course_teacher, )
                cur.execute(sql,val1)
                teacher = cur.fetchall()
                teacher_name = teacher[0][1]
                sql = "INSERT INTO teached (teacher_id,teacher_name,course_id,course_name) VALUES (%s,%s,%s,%s)"
                val = (course_teacher, teacher_name, course_id, course_name)
                cur.execute(sql,val)
                connect.commit()
                print("课程信息添加成功！")

            elif choice == "7":
                # 修改课程信息
                course_id = input("请输入要修改的课程ID：")
                sql = "SELECT * FROM course WHERE id = %s"
                val = (course_id,)
                cur.execute(sql, val)
                result = cur.fetchall()
                if len(result) == 0:
                    print("找不到该课程！")
                    continue
                print("原课程名称：{0}   原主讲教师ID：{1}".format(result[0][1], result[0][2]))
                print("请输入修改后的课程信息：")
                course_name = input("课程名称：")
                course_teacher = input("主讲教师ID：")

                sql = "UPDATE course SET name = %s, teacher = %s WHERE id = %s"
                val = (course_name, course_teacher, course_id)
                cur.execute(sql, val)
                connect.commit()

                sql = "SELECT * FROM teacher WHERE id = %s"
                data = (course_teacher, )
                cur.execute(sql, data)
                data1 = cur.fetchall()
                teacher_name = data1[0][1]

                sql = "UPDATE teached SET teacher_id = %s,course_name = %s, teacher_name = %s WHERE course_id = %s"
                val = (course_teacher, course_name, teacher_name, course_id)
                cur.execute(sql, val)
                connect.commit()

                sql = "UPDATE grade SET course_name = %s ,teacher = %s WHERE course_id = %s"
                val = (course_name, teacher_name, course_id)
                cur.execute(sql, val)
                connect.commit()

                sql = "UPDATE selected SET course_name = %s ,teacher = %s WHERE course_id = %s"
                val = (course_name, teacher_name, course_id)
                cur.execute(sql, val)
                connect.commit()
                print("课程信息修改成功！")

            elif choice == "8":
                # 删除课程信息
                course_id = input("请输入要删除的课程ID：")
                sql = "SELECT * FROM course WHERE id = %s"
                val = (course_id,)
                cur.execute(sql, val)
                result = cur.fetchall()
                if len(result) == 0:
                    print("找不到该课程！")
                    continue
                # 删除课程表中的记录
                sql = "DELETE FROM course WHERE id = %s"
                cur.execute(sql, val)
                connect.commit()
                # 删除选课表中的记录
                sql = "DELETE FROM selected WHERE course_id = %s"
                cur.execute(sql, val)
                connect.commit()
                # 删除成绩表中的记录
                sql = "DELETE FROM grade WHERE course_id = %s"
                cur.execute(sql, val)
                connect.commit()
                # 删除授课表中的记录
                sql = "DELETE FROM teached WHERE course_id = %s"
                cur.execute(sql, val)
                connect.commit()
                print("课程信息删除成功！")
            elif choice == "9":
                print()
                return main()
    else:
        print("用户名或密码错误，登录失败！")