学院管理系统（mysql版）

sqlalchemy import create_engine,Column, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import ForeignKey,Table from sqlalchemy.orm import relationship ############创建数据表结构######################3 # 创建实例，并连接work库 engine = create_engine("mysql+pymysql://root:@localhost/home_work?charset=utf8") Base = declarative_base() # 生成orm基类 # 班级与讲师的对应关系表 teacher_m2m_class = Table("teacher_m2m_class",Base.metadata, Column("teacher_id", Integer, ForeignKey("teacher.teacher_id")), Column("class_id", Integer, ForeignKey("class.class_id")), ) # 班级与学生的对应关系表 class_m2m_student = Table("class_m2m_student",Base.metadata, Column("class_id",Integer,ForeignKey("class.class_id")), Column("stu_id", Integer, ForeignKey("student.stu_id")), ) class Class_m2m_Lesson(Base): ‘‘‘班级和课节对应表‘‘‘ __tablename__ = "class_m2m_lesson" id = Column(Integer, primary_key=True) class_id = Column(Integer,ForeignKey("class.class_id")) lesson_id = Column(Integer, ForeignKey("lesson.lesson_id")) classes = relationship("Class",backref="class_m2m_lessons") lessons = relationship("Lesson", backref="class_m2m_lessons") def __repr__(self): return "%s %s" % (self.classes,self.lessons) class Study_record(Base): "上课记录" __tablename__ = "study_record" id = Column(Integer,primary_key=True) class_m2m_lesson_id = Column(Integer,ForeignKey("class_m2m_lesson.id")) stu_id = Column(Integer, ForeignKey("student.stu_id")) status = Column(String(32),nullable=False) score = Column(Integer,nullable=True) class_m2m_lessons = relationship("Class_m2m_Lesson",backref="my_study_record") students = relationship("Student", backref="my_study_record") def __repr__(self): return "\033[35;0m%s,%s,状态：【%s】,成绩：【%s】\33[0m"%(self.class_m2m_lessons,self.students,self.status,self.score) class Teacher(Base): "讲师" __tablename__ = "teacher" teacher_id = Column(Integer, primary_key=True) teacher_name = Column(String(32), nullable=False, unique=True) #唯一 classes = relationship("Class", secondary=teacher_m2m_class, backref="teachers") def __repr__(self): return "讲师：【%s】"%self.teacher_name class Class(Base): "班级" __tablename__ ="class" class_id = Column(Integer, primary_key=True) class_name = Column(String(32), nullable=False,unique=True) course = Column(String(32), nullable=False) students = relationship("Student",secondary=class_m2m_student,backref="classes") def __repr__(self): return "班级名：【%s】"%self.class_name class Student(Base): "学生" __tablename__ ="student" stu_id = Column(Integer, primary_key=True) stu_name = Column(String(32), nullable=False, unique=True) QQ = Column(Integer(), nullable=False) def __repr__(self): return "学生名：【%s】"%self.stu_name class Lesson(Base): "课节" __tablename__ = "lesson" lesson_id = Column(Integer, primary_key=True) lesson_name = Column(String(32), nullable=False, unique=True) def __repr__(self): return "节次名：【%s】"%self.lesson_name Base.metadata.create_all(engine) 表结构

from sqlalchemy import desc
from .models import Teacher,Class,Student,Lesson,Class_m2m_Lesson,Study_record
import random

class  Student_Center(object):
    "学生视图"
    def __init__(self,session):
        self.session = session
        self.authentication()
        self.handler()

    def handler(self):
        while True:
            print(‘‘‘\n\33[35;1m———欢迎来【%s】进入学员管理系统————\33[0m \n
            \33[34;0mup_homework 上传作业
            show_homework 查看作业成绩
            show_rank 查看班级排名
            exit 退出管理系统
            \33[0m‘‘‘ % self.student_obj.stu_name)
            user_func = input("\033[34;0m请输入进行操作的命令:\033[0m").strip()
            if hasattr(self, user_func):
                getattr(self, user_func)()

    def authentication(self):
        ‘‘‘认证‘‘‘
        while True:
            student_name = input("\033[34;0m请输入学生名:\033[0m").strip()
            self.student_obj = self.session.query(Student).filter_by(stu_name=student_name).first()
            if not self.student_obj:
                print("\33[31;1m输入错误：请输入有效的学生名\33[0m")
                continue
            else:
                # print(self.teacher_obj)
                break

    def up_homework(self):
        "上传作业"
        class_name = input("\033[34;0m请输入班级名:\033[0m")

        for class_obj in self.student_obj.classes:
            print(class_obj.class_name)
            if class_name == class_obj.class_name:
                lesson_name = input("\033[34;0m请输入的课节名（lesson）:\033[0m")
                lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()

                if lesson_obj:  # 输入的lesson名字存在
                    class_m2m_lesson_obj = self.session.query(Class_m2m_Lesson).filter(
                        Class_m2m_Lesson.class_id == class_obj.class_id).                         filter(Class_m2m_Lesson.lesson_id == lesson_obj.lesson_id).first()
                    if class_m2m_lesson_obj:  # 班级对应的课lesson表数据存在
                        study_record_obj = self.session.query(Study_record).filter(
                            Study_record.class_m2m_lesson_id == class_m2m_lesson_obj.id).filter(
                            Study_record.stu_id == self.student_obj.stu_id).first()

                        if study_record_obj:  # 上课记录存在
                            score = random.randint(10,100)
                            study_record_obj.score = score
                            self.session.commit()
                            print("上传成功")
                        else:
                            print("\33[31;1m系统错误：当前上课记录已经创建\33[0m")
                else:
                    print("\33[31;1m系统错误：lesson未创建\33[0m")

    def show_homework(self):
        ‘‘‘查看作业成绩‘‘‘
        class_name = input("\033[34;0m请输入学习记录的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj:
            lesson_name = input("\033[34;0m请输入学习记录的课节名（lesson）:\033[0m")
            lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()

            if lesson_obj:  # 输入的lesson名字存在
                class_m2m_lesson_obj = self.session.query(Class_m2m_Lesson).filter(
                    Class_m2m_Lesson.class_id == class_obj.class_id).                     filter(Class_m2m_Lesson.lesson_id == lesson_obj.lesson_id).first()

                if class_m2m_lesson_obj:  # 班级对应的课lesson表数据存在
                    study_record_objs = self.session.query(Study_record).filter(
                        Study_record.class_m2m_lesson_id == class_m2m_lesson_obj.id).all()
                    for obj in study_record_objs:
                        if obj.stu_id == self.student_obj.stu_id:
                            print(obj)

    def show_rank(self):
        ‘‘‘查看排名‘‘‘

        class_name = input("\033[34;0m请输入学习记录的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj:
            lesson_name = input("\033[34;0m请输入学习记录的课节名（lesson）:\033[0m")
            lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()

            if lesson_obj:  # 输入的lesson名字存在
                class_m2m_lesson_obj = self.session.query(Class_m2m_Lesson).filter(
                    Class_m2m_Lesson.class_id == class_obj.class_id).                     filter(Class_m2m_Lesson.lesson_id == lesson_obj.lesson_id).first()

                if class_m2m_lesson_obj:  # 班级对应的课lesson表数据存在
                    score_rank_obj = self.session.query(Study_record).order_by(desc(Study_record.score))
                    for obj in score_rank_obj:
                        if obj.class_m2m_lesson_id == class_m2m_lesson_obj.id:
                            print(obj)

学生视图

from .models import Teacher,Class,Student,Lesson,Class_m2m_Lesson,Study_record,class_m2m_student

class  Teacher_Center(object):
    "讲师视图"
    def __init__(self,session):
        self.session = session
        self.authentication()
        self.handler()

    def handler(self):
        while True:
            print(‘‘‘\n\33[35;1m———欢迎来【%s】进入讲师管理系统————\33[0m \n
        \33[34;0mshow_classes 显示可管理的班级
        add_class 创建班级
        add_student 添加学员
        add_lesson 添加课程节次
        add_studyrecord 创建上课记录
        modify_scores 修改学员成绩
        exit 退出管理系统
        \33[0m‘‘‘ % self.teacher_obj.teacher_name)
            user_func = input("\033[34;0m请输入进行操作的命令:\033[0m").strip()
            if hasattr(self, user_func):
                getattr(self, user_func)()


    def authentication(self):
        ‘‘‘认证‘‘‘
        while True:
            teacher_name = input("\033[34;0m请输入讲师名:\033[0m").strip()
            self.teacher_obj = self.session.query(Teacher).filter_by(teacher_name=teacher_name).first()
            if not self.teacher_obj:
                print("\33[31;1m输入错误：请输入有效的讲师名\33[0m")
                continue
            else:
                #print(self.teacher_obj)
                break


    def add_class(self):
        ‘‘‘创建班级‘‘‘
        class_name = input("\033[34;0m请输入创建班级的名称:\033[0m")
        course = input("\033[34;0m请输入创建班级的类型:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()
        if not class_obj:
            class_new = Class(class_name=class_name,course=course)
            self.teacher_obj.classes.append(class_new)
            self.session.add(class_new)
            self.session.commit()
            print("\033[34;1m班级创建成功\033[0m")
        else:
            print("\33[31;1m系统错误：班级已经存在\33[0m")


    def show_classes(self):
        ‘‘‘查看所有的班级‘‘‘
        print(self.teacher_obj)
        for class_obj in self.teacher_obj.classes:
            print("讲师：【%s】\t班级：【%s】\t类型：【%s】" % (class_obj.teachers[0].teacher_name,
                                                 class_obj.class_name,class_obj.course))


    def add_student(self):
        ‘‘‘添加学员‘‘‘
        class_name = input("\033[34;0m请输入要添加学员的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj and class_obj.teachers[0] == self.teacher_obj:
            stu_name = input("\033[34;0m请输入学员的姓名:\033[0m")
            QQ = input("\033[34;0m请输入学员的QQ号:\033[0m")
            student_obj = self.session.query(Student).filter_by(QQ = QQ).first()

            if student_obj:
                if student_obj.stu_name not in class_obj.students:
                    class_obj.students.append(student_obj)
                    self.session.add(class_obj)
                    self.session.commit()
                    print("\033[34;1m学员添加成功\033[0m")
                else:
                    print("\33[31;1m系统错误：学员已经存在此班中\33[0m")

            elif not student_obj:
                student_new = Student(stu_name=stu_name, QQ=QQ)
                class_obj.students.append(student_new)
                self.session.add(student_new)
                self.session.commit()
                print("\033[34;1m学员添加成功\033[0m")
            else:
                print("\33[31;1m系统错误：学员已经存在\33[0m")
        else:
            print("\33[31;1m输入错误：班级不存在或没有权限管理此班级\33[0m")


    def add_lesson(self):
        ‘‘‘添加课程节次‘‘‘
        class_name = input("\033[34;0m请输入要添加lesson的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj and class_obj.teachers[0] == self.teacher_obj:     #输入的班级名存在且在属于当前老师管理
            lesson_name = input("\033[34;0m请输入添加的lesson名（类day1）:\033[0m")
            lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()
            if not lesson_obj:                                          #输入的lesson名字不存在
                print("lesson  不存在")
                lesson_obj = Lesson(lesson_name=lesson_name)                #增加lesson表数据
                self.session.add(lesson_obj)
                self.session.commit()

            rest = self.session.query(Class_m2m_Lesson).filter(Class_m2m_Lesson.class_id==class_obj.class_id).                filter(Class_m2m_Lesson.lesson_id==lesson_obj.lesson_id).first()
            if not rest:                                                #如果class_m2m_lesson没有存储班级和课节的对应关系
                print("class:%s----lesson:%s"%(class_obj.class_id,lesson_obj.lesson_id))
                class_m2m_lesson_new = Class_m2m_Lesson(class_id=class_obj.class_id,lesson_id=lesson_obj.lesson_id)
                self.session.add(class_m2m_lesson_new)                  #创建class_m2m_lesson对应关系
                self.session.commit()

        else:
            print("\33[31;1m输入错误：班级不存在或没有权限管理此班级\33[0m")


    def add_studyrecord(self):
        ‘‘‘添加学习记录‘‘‘
        class_name = input("\033[34;0m请输入要添加学习记录的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj and class_obj.teachers[0] == self.teacher_obj:  # 输入的班级名存在且在属于当前老师管理
            lesson_name = input("\033[34;0m请输入添加学习记录的课节名（lesson）:\033[0m")
            lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()
            if lesson_obj:                                       # 输入的lesson名字存在
                class_m2m_lesson_obj = self.session.query(Class_m2m_Lesson).filter(Class_m2m_Lesson.class_id == class_obj.class_id).                     filter(Class_m2m_Lesson.lesson_id == lesson_obj.lesson_id).first()
                if class_m2m_lesson_obj:                                            # 班级对应的课lesson表数据存在

                    study_record_obj = self.session.query(Study_record).filter_by(class_m2m_lesson_id=class_m2m_lesson_obj.id).first()
                    if not study_record_obj:                                                    # 上课记录为创建
                        for student_obj in class_obj.students:
                            status = input("输入学生 %s 的上课状态（yes/no）："%student_obj.stu_name)
                            study_record_new = Study_record(class_m2m_lesson_id=class_m2m_lesson_obj.id,
                                                            stu_id=student_obj.stu_id,
                                                            status=status)
                            self.session.add(study_record_new)
                            self.session.commit()
                    else:
                        print("\33[31;1m系统错误：当前上课记录已经创建\33[0m")
                else:
                     print("\33[31;1m系统错误：当前班级的lesson课节未创建\33[0m")
            else:
                print("\33[31;1m系统错误：lesson未创建\33[0m")
        else:
            print("\33[31;1m输入错误：班级不存在或没有权限管理此班级\33[0m")


    def modify_scores(self):
        ‘‘‘修改成绩‘‘‘
        class_name = input("\033[34;0m请输入学习记录的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj and class_obj.teachers[0] == self.teacher_obj:  # 输入的班级名存在且在属于当前老师管理
            lesson_name = input("\033[34;0m请输入学习记录的课节名（lesson）:\033[0m")
            lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()

            if lesson_obj:  # 输入的lesson名字存在
                class_m2m_lesson_obj = self.session.query(Class_m2m_Lesson).filter(
                    Class_m2m_Lesson.class_id == class_obj.class_id).                     filter(Class_m2m_Lesson.lesson_id == lesson_obj.lesson_id).first()

                if class_m2m_lesson_obj:  # 班级对应的课lesson表数据存在
                    while True:
                        study_record_objs = self.session.query(Study_record).filter(
                                Study_record.class_m2m_lesson_id==class_m2m_lesson_obj.id).all()
                        for obj in  study_record_objs:
                            print(obj)

                        student_name = input("\033[34;0m输入要修改成绩的学生名：[Q 退出]\33[0m")
                        if student_name == "q" or student_name == "Q":break
                        student_obj = self.session.query(Student).filter_by(stu_name=student_name).first()
                        if student_obj:
                            study_record_obj = self.session.query(Study_record).filter(
                                Study_record.class_m2m_lesson_id==class_m2m_lesson_obj.id).filter(
                                Study_record.stu_id == student_obj.stu_id).first()

                            if study_record_obj:                            # 上课记录存在
                                score = input("\033[34;0m输入修改后的成绩\33[0m")
                                study_record_obj.score= score
                                self.session.commit()

                    else:
                        print("\33[31;1m系统错误：当前上课记录已经创建\33[0m")
                else:
                    print("\33[31;1m系统错误：当前班级的lesson课节未创建\33[0m")
            else:
                print("\33[31;1m系统错误：lesson未创建\33[0m")
        else:
            print("\33[31;1m输入错误：班级不存在或没有权限管理此班级\33[0m")


    def exit(self):
        exit()

教师视图

不足

1. readme文件写的不够详细，测试环境没有写详细
2. 入口文件路径不对
3. 代码存在现实逻辑缺陷（因为完成作业需求，暂时不计较）

完整代码

https://github.com/lixiaoliuer1229/work_demo/tree/master/day12

 

学院管理系统（mysql版）

标签：ima   session   管理系   完整   基类   sse   ati   技术分享   ==