python+sqlite3学生信息管理系统

谁践踏了优雅 2024-02-18 19:44 36阅读 0赞

此文章非原创，只是自己百度和修改，看别人的都不能运行，需要自己修改，为了像我这样的同学少走一些弯路，给大家一些参考。

import sqlite3
    cx = sqlite3.connect('D:/student3.db')
    
    cx.execute( '''CREATE TABLE StudentTable(
        ID          INTEGER     PRIMARY KEY     AUTOINCREMENT,
        StuId       INTEGER     NOT NULL,
        NAME        TEXT        NOT NULL,
        CLASS       INT         NOT NULL
    );''')
    
    print("Table created successfully!")
    
    cx.execute('''CREATE TABLE CourseTable  (
        CourseId        INT         NOT NULL,
        Name            TEXT        NOT NULL,
        Teacher         TEXT        NOT NULL,
        Classroom       TEXT        NOT NULL,
        StartTime       CHAR(11)    NOT NULL,
        EndTime         CHAR(11)    NOT NULL
    );''')
    
    cu = cx.cursor()
    
    CourseTable = [('1', 'Qt', 'ming', 602, 'Monday9:00', 'Monday11:00'),
                       ('2', 'Linux', 'han', 605, 'Friday13:20', 'Friday14:20'),
                       ('3', 'sqlite3', 'hah', 608, 'Thursday15:00', 'Thursday17:00'),
                       ]
    cu.executemany("insert into CourseTable values(?, ?, ?, ?, ?, ?)", CourseTable)
    cx.commit()
    
    print("Table created successfully!")
    
    cx.execute('''CREATE TABLE XuankeTable(
        ID      INTEGER     PRIMARY KEY     AUTOINCREMENT,
        StuId       INT     NOT NULL,
        CourseId    INT     NOT NULL
    );''')
    
    print("Table created successfully")
    
    def insert_stu():#插入学生信息
        cu = cx.cursor()
        stu_id = input("请输入学生学号:")
        cu.execute("select StuId from StudentTable where StuId =%s" %(stu_id))
        row = cu.fetchone()
        if row:
            print("Sorry,该学号已存在，请重新输入")
        else:
            stu_name = input("请输入学生姓名")
            stu_class = input("请输入学生班级")
            sql1 = "insert into StudentTable(StuId, NAME, CLASS)"
            sql1 += " values(%s, '%s', %s) "%(stu_id, stu_name, stu_class)
            cu.execute(sql1)
            cx.commit()
            print("恭喜你，学生录入成功!")
        cu.close()
    
    def xuanke():#学生选课
        cu = cx.cursor()
        stu_id = input("请输入要选课的学生学号:")
        sql2 = "select StuId from StudentTable where StuId =%s"%(stu_id)
        cu.execute(sql2)
        row = cu.fetchone()
        if row:
            sql3 = "select CourseId, Name, Teacher, Classroom, StartTime, EndTime from CourseTable"
            cu.execute(sql3)
            rows = cu.fetchall()
            for row in rows:
                print("CourseId = ", row[0])
                print("Name = ", row[1])
                print("Teacher = ", row[2])
                print("Classroom = ", row[3])
                print("StartTime = ", row[4])
                print("EndTime = ", row[5], "\n")
            cou_id = input("请输入要选的课程号是:")
            sql0 = "select CourseId from CourseTable where CourseId = %s"%(cou_id)
            cu.execute(sql0)
            row = cu.fetchone()
            if row:
                '''sql = "select StuId CourseId from XuankeTable"
                sql += "where CourseId = %s and StuId = %s"%(cou_id,stu_id)
                cu.execute(sql)
                row = cu.fetchone()
                if row:
                    print("该课程已选，不能重复选课！")
                else:'''
                sql3 = "insert into XuankeTable (stuId,CourseId) values(%s,%s)"%(stu_id,cou_id)
                cu.execute(sql3)
                cx.commit()
                print("带喜你，选课成功！")
            else:
                print("Sorry，该课程不存在！")
        else:
            print("Sorry,没有该学生号！")
        cu.close()
    
    def stu_id_search():#通过学生Id，查询学生信息
        cu = cx.cursor()
        search_stu_id = input("请输入要查询的学号:")
        sql4 = "select ID,StuId,NAME,CLASS from StudentTable where StuId=%s"%(search_stu_id)
        cu.execute(sql4)
        row = cu.fetchone()
        cx.commit()
        if row:
            sql5 = "select ID,StuId,NAME,CLASS from StudentTable"
            cu.execute(sql5)
            rows = cu.fetchall()
            for row in rows:
                print("---------")
                print("您要查询的学生信息为:")
                print("ID=",row[0])
                print("StuId",row[1])
                print("NAME=",row[2])
                print("CLASS = ",row[3],"\n")
        else:
            print("Sorry, 没有该学生信息！")
        cu.close()
    
    def stu_id_cou():#通过学生Id，查询学生Id的课程信息
        cu = cx.cursor()
        stu_id = input("请输入要查询学生号：")
        sql5 = "select StuId from StudentTable where stuId =%s"%(stu_id)
        cu.execute(sql5)
        row = cu.fetchall()
        if row:
            sql6 = "select * from XuankeTable a left join CourseTable b on a.CourseId=b.CourseId"
            cu.execute(sql6)
            rows = cu.fetchall()
            for row in rows:
                print("该学生所选课程为：")
                print("StuId = ",row[1])
                print("CourseId = ",row[2])
                print("Name = ", row[4])
                print("Teacher = ", row[5])
                print("Classroon =",row[6])
                print("StartTime = ",row[7])
                print("EndTime = ", row[8], "\n")
                print("-----------------")
        else:
            print("sorry,没有该学生选课信息!")
        cu.close()
    
    def cou_id_serach():#通过课程Id，查询课程信息
        cu = cx.cursor()
        cou_id = input("请输入要查询的课程号:")
        sql7 = "select CourseId,Name,Teacher,Classroom,StartTime,EndTime from CourseTable where CourseId =%s"%(cou_id)
        cu.execute(sql7)
        row = cu.fetchone()
        if row:
            print("您要查询的课程信息为:")
            print("CourseId = ", row[0])
            print("Name = ", row[1])
            print("Teacher = ", row[2])
            print("Classroom = ", row[3])
            print("StartTime = ",row[4])
            print("EndTime = ", row[5], "\n")
        else:
            print("Sorry,没有该课程信息！")
        cu.close()
    
    def cou_id_stu():#通过课程号查询所选学生信息
        cu = cx.cursor()
        cou_id = input("请输入课程号:")
        sql8 = "select CourseId from XuankeTable where CourseId =%s"%(cou_id)
        cu.execute(sql8)
        row = cu.fetchone()
        if row:
            sql9 = "select * from XuankeTable a left join StudentTable b on a.StuId=b.StuId"
            cu.execute(sql9)
            rows = cu.fetchall()
            for row in rows:
                print("-----------------------------")
                print("选择该课程的学生为：")
                print("StuId = ", row[1])
                print("NAME = ", row[5])
                print("CLASS = ", row[6], "\n")
            else:
                print("Sorry，没有该课程信息！")
            cu.close()
    
    def menu():
        print("1.进入学生信息系统（学生信息录入）")
        print("2.进入学生选课系统（学生选课操作）")
        print("3.进入学生选课信息系统（学生信息查询和选课情况查询）")
        print("4.退出程序")
    
    def student():
        print("1.录入学生信息")
        print("2.返回主菜单")
    
    def Course():
        print("1.开始选课")
        print("2.返回主菜单")
    
    def information():
        print("1.按学号查询学生信息")
        print("2.按学号查看学生选课课程列表")
        print("3.按课程号查看课程信息")
        print("4.按课程号查看选课学生列表")
        print("5.返回主菜单")
    
    while True:
        menu()
        print("---------------------")
        x = input("请输入您的选择菜单号:")
        if x == '1':
            student()
            stu = input("您已进入学生录入系统，请再次输入选择菜单:")
            print("---------------------")
            if stu == '1':
                insert_stu()
                continue
            if stu == '2':
                continue
            else:
                print("输入的选项不存在，请重新输入！")
                continue
    
        if x == '2':
            #进入造课信息系统
            Course()
            cou = input("您已进入学生选课系统，请再次输入选择菜单：")
            print("------------------")
            if cou == '1':
                xuanke()
                continue
            if cou == '2':
                continue
            else:
                print("输入的选项不存在，请重新输入！")
                continue
    
        if x == '3':
            #进入学生选课信息表
            information()
            inf = input("您已进入学生选课信息系统，请再次输入选择菜单：")
            print("------------------")
            if inf == '1':
                stu_id_search()
                continue
            if inf == '2':
                stu_id_cou()
                continue
            if inf == '3':
                cou_id_serach()
                continue
            if inf == '4':
                cou_id_stu()
            else:
                print("输入的选项不存在，请重新输入！")
                continue
    
        if x == '4':
            print("谢谢使用")
            exit()
        else:
            print("输入的选项不存在，请重新输入！")
            continue