学生管理系统（MySQL版）

• 思维导图
• 一、 数据源
• 二、建立数据库
• • 导入MySQL数据源，并建立链接
  • 建立主程序
  • • 1.主界面
    • 2.登入后界面
    • 3.定义插入记录
    • 4.定义查询记录
    • 5.定义删除记录
    • 6.定义修改记录
    • 7.定义显示所有记录

1、首先新建MySQL连接

输入连接名“student”
主机名可填写“local host”或“127.0.0.1”
MySQL默认端口号为“3306”
用户名和密码可自己任意填写

#插入学生记录
def add_student():
    cursor = main()
    id1 = int(input('学号：'))
    name = input('姓名：')
    gem = input('性别：')
    age = int(input('年龄：'))
    class1 = input('班级：')
    major1 = input('专业：')
    college = input('学院：')
    telephone = int(input('电话：'))
    add = cursor.execute('insert into stu (id, name, genden, age, class, major, college,telephone) values(%s,%s,%s,%s,%s,%s,%s,%s)',(id1, name, gem, age, class1, major1, college,telephone))
    if add >= 1:
        conn.commit()
        print('插入记录成功！') 
    else:
        print('插入记录失败！')

#查询的菜单
def query_student():
    while True:
        print('查询学生记录')
        print('================')
        print('1、按学号查询学生记录')
        print('2、按姓名查询学生记录')
        print('3、查询全部学生记录')
        print('4、返回上级菜单')
        print('=================')
        mc3 = int(input('请输入查询的菜单号：'))
        if mc3 == 1:
            by_id()
        elif mc3 == 2:
            by_name()
        elif mc3 == 3:
            by_all()
        else:
            break
#按学号查询
def by_id():
    cursor = main()
    choice_id = int(input('请输入学号：'))
    cursor.execute('select * from stu where id =%s',(choice_id))
    students = cursor.fetchall()
    for stu in students:
        print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6], stu[7])
        print('查询成功')
        yn = input('是否继续查询(yes/no)：')
        if yn == 'yes':
            by_id()
        else:
            query_student()

#按姓名查询（以防学号输入错误）
def by_name():
    cursor = main()
    choose_name = input('请输入姓名：')
    cursor.execute('select * from stu where name =%s',(choose_name))
    students = cursor.fetchall()
    for stu in students:
        print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6], stu[7])
        print()
        re = input('是否继续查询yes/no：')
        if re == 'yes':
            by_name()
        else:
            query_student()

#查询所有学生
def by_all():
    cursor = main()
    cursor.execute('select * from stu')
    students = cursor.fetchall()
    for student in students:
        print('t{}t{}t{}t{}t{}t{}t{}t{}' .format(student[0], student[1], student[2], student[3], student[4], student[5], student[6], student[7]))

#删除学生记录
def del_student():
    cursor = main()
    id = int(input('输入想要删除学生的学号：'))
    dele = cursor.execute('delete from stu where id = {}' .format(id))
    if dele == 1:
        conn.commit()
        print('删除成功！')
    else:
        print('删除失败！')

#删除的菜单
def delete1_student():
    print('============================')
    print('1、删除学生信息')
    print('2、回到初始界面')
    print('============================')
    mc4 = int(input('Input menu number:'))
    if mc4 == 1:
        del_student()
    elif mc4 == 2:
        login()

def up_student():
        cursor = main()
        cur= int(input('请输入需要修改学生的学号：'))
        cursor.execute('select * from stu where id = %s', (cur))
        print('==============')
        print('1、修改姓名')
        print('2、修改性别')
        print('3、修改年龄')
        print('4、修改班级')
        print('5、修改专业')
        print('6、修改学院')
        print('7、修改电话')
        print('8、返回上级菜单')
        print('==============')
        mc2 = int(input('请输入菜单号：'))
        if mc2 == 1:
            name = input('请输入需要修改的名字：')
            a = cursor.execute('update stu set name = %s where id = %s', (name, cur))
            if a == 1:
                conn.commit()
                print('修改成功！')
            else:
                print('修改失败！')
        elif mc2 == 2:
            gender1 = input('请输入需要修改的性别：')
            a = cursor.execute('update stu set genden = %s where id = %s', (gender1, cur))
            if a > 1:
                conn.commit()
                print('修改成功！')
            else:
                print('修改失败！')
        elif mc2 == 3:
            age1 = int(input('请输入需要修改的年龄：'))
            a = cursor.execute('update stu set age = %s where id = %s', (age1, cur))
            if a > 1:
                conn.commit()
                print('修改成功！')
            else:
                print('修改失败！')
        elif mc2 == 4:
            class1 = input('请输入需要修改的班级：')
            a = cursor.execute('update stu set class = %s where id = %s', (class1, cur))
            if a > 1:
                conn.commit()
                print('修改成功！')
            else:
                print('修改失败！')
        elif mc2 == 5:
            major1 = input('请输入需要修改的专业：')
            a = cursor.execute('update stu set major = %s where id = %s', (major1, cur))
            if a > 1:
                conn.commit()
                print('修改成功！')
            else:
                print('修改失败！')
        elif mc2 == 6:
            college1 = input('请输入需要修改的学院：')
            a = cursor.execute('update stu set college = %s where id = %s', (college1, cur))
            if a > 1:
                conn.commit()
                print('修改成功！')
            else:
                print('修改失败！')
        elif mc2 == 7:
            telephone1 = input('请输入需要修改的电话：')
            a = cursor.execute('update stu set college = %s where id = %s', (telephone1, cur))
            while True:
                if len(telephone1) != 11:
                    print('请重新输入电话号码!')
                else:
                    break
                    if a > 1:
                        conn.commit()
                        print('修改成功！')
                    else:
                        print('修改失败！')
        else:
            login()

#显示所有的学生记录       
def print_student():
    cursor=main()
    cursor.execute('select * from stu')
    students = cursor.fetchall()
    for stu in students:
        print(stu[0], stu[1], stu[2], stu[3], stu[4], stu[5], stu[6],stu[7])