sqlalchemy-orm学生签到 成绩记录查询系统

#!/usr/bin/env python
# Author:zhangmingda
''''''
from sqlalchemy import create_engine,ForeignKey,DATE,Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import relationship,sessionmaker
engine = create_engine("mysql+pymysql://zhangmingda:Wyf@1314@120.92.133.227/stu_record_project?charset=utf8",encoding='utf8',echo=False)
'''说明：engine 如果想写入中文，在库名后面加入?charset=utf8 encoding=utf-8 不起作用'''
'''生成基类'''
Base = declarative_base()
#'''此表只存老师id和class id的对应关系'''
teacher_m2m_classes = Table(
    'teacher_m2m_classes', Base.metadata,
    Column('teacher_id', Integer, ForeignKey('teachers.id')),
    Column('class_id', Integer, ForeignKey('classes.id'))
)
#'''此表表示课节和班级的对应关系：一节课可能多个班级同时上'''
lessons_m2m_classes = Table(
    'lessons_m2m_classes',Base.metadata,
    Column('lesson_id',Integer,ForeignKey('lessons.id')),
    Column('class_id',Integer,ForeignKey('classes.id'))
)
#'''此表表示学生和班级的对应关系：一个班级多个学生，一个学生也可能报多个班'''
students_m2m_classes = Table(
    'students_m2m_classes', Base.metadata,
    Column('student_id', Integer, ForeignKey('students.id')),
    Column('class_id', Integer, ForeignKey('classes.id'))
)

class Teacher(Base):
    '''老师表'''
    __tablename__ = 'teachers'
    __table_args__ = {'mysql_charset': 'utf8'}
    id = Column(Integer,primary_key=True)
    name = Column(String(64),nullable=False,unique=True)
    classes = relationship('Class',secondary=teacher_m2m_classes,backref='teachers')
    def __repr__(self):
        return "讲师:%s"% self.name

class Student(Base):
    '''学生表'''
    __tablename__ = 'students'
    __table_args__ = {'mysql_charset': 'utf8'}
    id = Column(Integer,primary_key=True)
    name = Column(String(64),nullable=False)
    QQ = Column(String(64),nullable=False,unique=True)
    def __repr__(self):
        return "学生:%s"% self.name

class Class(Base):
    '''班级表'''
    __tablename__ = 'classes'
    __table_args__ = {'mysql_charset': 'utf8'}
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, unique=True)
    students = relationship('Student',secondary=students_m2m_classes,backref='classes')
    def __repr__(self):
        return  self.name

class  Lesson(Base):
    '''课节表'''
    __tablename__ = 'lessons'
    __table_args__ = {'mysql_charset': 'utf8'}
    id = Column(Integer,primary_key=True)
    name = Column(String(64), nullable=False, unique=True)
    lesson_class = relationship('Class',secondary=lessons_m2m_classes,backref='lessons')
    def __repr__(self):
        return self.name #'课节;%s'%
class Stu_record(Base):
    '''上课记录表'''
    __tablename__ = 'stu_record'
    __table_args__ = {'mysql_charset': 'utf8'}
    id = Column(Integer,primary_key=True)
    lesson_id = Column(Integer,ForeignKey('lessons.id'))
    stu_id = Column(Integer,ForeignKey('students.id'))
    status = Column(String(64),nullable=True)
    homework = Column(String(64),nullable=True)
    score = Column(Integer,nullable=True)
    lessons_name = relationship('Lesson',backref='lessons_record')
    student = relationship('Student',backref='lessons_record')
    def __repr__(self):
        return '%s 课节 学员：%s   状态：%s  成绩 ：%s '%(self.lessons_name,self.student,self.status,self.score)

if __name__ == '__main__':
    Base.metadata.create_all(engine)

#!/usr/bin/env python
# Author:zhangmingda
import teacher_active,student_active,tables_class
from sqlalchemy.orm import sessionmaker

SessionClass = sessionmaker(bind=tables_class.engine)
session = SessionClass()

# teacher = teacher_active.Teacher_view(session)
# teacher.handle()



student = student_active.Student_view(session)
student.handle()

#!/usr/bin/env python
# Author:zhangmingda
import tables_class

class Teacher_view(object):
    def __init__(self,session):
        self.session = session
        self.login_status = False
        self.teacher_obj = None
        # self
    def auth(self):
        '''简易登录认证'''
        while True:
            teacher_name = input('\033[34;1m请输入讲师名字：\033[0m').strip()
            teacher_obj = self.session.query(tables_class.Teacher).filter_by(name=teacher_name).first()
            if teacher_obj:
                self.teacher_obj = teacher_obj
                self.login_status = True
                break
            else:
                continue
    def handle(self):
        while not self.login_status:
            self.auth()
        else:
            while self.login_status:
                print('\033[32;1m欢迎进入讲师操作界面,请输入要操作的代号：\033[0m')
                menu = {
                    1:"创建班级",
                    2:"创建课节",
                    3:"向班级添加学员",
                    4:"修改学员上课记录",
                    5:"修改学员成绩",
                    6:"退出系统",
                }
                menu_dict = {
                    "1": self.create_class,
                    "2": self.create_lesson,
                    "3": self.add_student,
                    "4": self.modify_record,
                    "5": self.modify_score,
                    "6": 'exit'
                }
                for i in menu:
                    print(i,menu[i])
                chose = input('请选择>>>').strip()
                if  chose in menu_dict and chose != "6" :
                    menu_dict[chose]()
                elif chose == '6':break
                else:
                    print('\033[31;1m您输入的代号不存在，请重新输入。\033[0m')

    def create_class(self):
        class_name = input('\033[32;0m要创建班级名称：\033[0m').strip()
        class_exist = self.session.query(tables_class.Class).filter_by(name=class_name).first()
        if class_exist:
            print('\033[31;1m班级名称已存在：%s\033[0m'% class_exist)
        else:
            class_obj = tables_class.Class(name=class_name)
            self.teacher_obj.classes.append(class_obj)
            self.session.add_all([class_obj])
            self.session.commit()
            print('\033[32;1m班级：%s创建成功\033[0m'%class_obj)
    def create_lesson(self):
        classes = self.teacher_obj.classes
        if not classes:
            print('\033[31;1m您不教任何一个班级，无法创建课程\033[0m')
        else :
            print('\033[32;1m您所教班级有\033[0m')
            for i in classes:
                print(i)
            while True:
                input_class = input('\033[32;1m 请输入要创建课程的班级名称>>>\033[0m').strip()
                class_obj = self.session.query(tables_class.Class).filter_by(name=input_class).first()
                if not class_obj:
                    print('\033[31;1m 输入的班级名称不存在,请重新输入。\033[0m')
                else:
                    class_lessons = class_obj.lessons
                    print('\033[33;1m班级：%s当前已关联课程：%s'%(class_obj,class_lessons))
                    lesson_name = input('\033[32;1m 输入要创建的课程名称>>> \033[0m')
                    lesson_obj_exist = self.session.query(tables_class.Lesson).filter_by(name=lesson_name).first()
                    #课程已存在，并且已经关联该班级
                    if lesson_obj_exist  in list(class_lessons):
                        print('\033[31;1m %s 下课程：%s已存在,请重新添加\033[0m'%(class_obj,lesson_obj_exist))
                     #'''课程已存在，但未关联该班级'''
                    elif lesson_obj_exist and lesson_obj_exist not in list(class_lessons): #课程表中有，但未关联班级
                        '''list()将sqlalchemy的对象list转为普通list，用来做in的判断'''
                        print('\033[31;1m 课程名：%s库中已存在\033[0m,\033[32;1m直接加入到课程%s\033[0m'%(lesson_name,class_obj))
                        class_obj.lessons.append(lesson_obj_exist) #将已有课程关联班级
                        self.session.commit()
                        # print('类型：',type(lesson_obj_exist),'测试',type(list(class_lessons)))
                        break
                    #课程不存在
                    else:
                        lesson_obj = tables_class.Lesson(name=lesson_name) #创建课程对象
                        lesson_obj.lesson_class.append(class_obj) #将课程同班级关联
                        self.session.add_all([lesson_obj]) #加入session
                        self.session.commit() #提交保存到数据库
                        break

    def add_student(self):
        classes = self.teacher_obj.classes
        if not classes:
            print('\033[31;1m您不教任何一个班级，无法添加学生\033[0m')
        else :
            print('\033[32;1m您所教班级有\033[0m')
            for i in classes:
                print(i)
            while True:
                input_class = input('\033[32;1m 请输入要添加学员的班级名称>>>\033[0m').strip()
                class_obj = self.session.query(tables_class.Class).filter_by(name=input_class).first()
                if not class_obj:
                    print('\033[31;1m 输入的班级名称不存在,请重新输入。\033[0m')
                else:
                    print('%s 班 已有学员 %s'%(class_obj,class_obj.students))
                    input_stu = input('\033[32;1m 请输入要添加的学员QQ号码：\033[0m').strip()
                    stu_obj = self.session.query(tables_class.Student).filter_by(QQ=input_stu).first()
                    if stu_obj and stu_obj  in list(class_obj.students):
                        print('\033[31;1m %s 已存在 %s 班'%(stu_obj,class_obj))
                    elif not stu_obj:
                        print("\033[31;1m %s 不存在\033[0m"% stu_obj)
                    else:
                        class_obj.students.append(stu_obj)
                        self.session.commit()
                        print("\033[32;1m %s 添加到 %s 成功！\033[0m"%(stu_obj,class_obj))
                        break
                pass
    def modify_record(self):
        classes = self.teacher_obj.classes
        if not classes:
            print('\033[31;1m您不教任何一个班级，无法添加上课记录\033[0m')
        else:
            print('\033[32;1m您所教班级有\033[0m')
            for i in classes:
                print(i.id,i)
            while True:
                input_class_id = input('\033[32;1m 请选择班级名称代号>>>\033[0m').strip()
                class_obj = self.session.query(tables_class.Class).filter_by(id=input_class_id).first()
                if not class_obj:
                    print('\033[31;1m 输入的班级代号不存在,请重新输入。\033[0m')
                    # continue
                else:
                    while True:
                        print('%s 班 已有课程 %s' % (class_obj, class_obj.lessons))
                        chose_lesson = input('\033[32;1m 请选择课程\033[0m')
                        lesson_obj = self.session.query(tables_class.Lesson).filter_by(name=chose_lesson).first()
                        if lesson_obj in list(class_obj.lessons):
                            print('\033[32;1m %s 学生有：%s\033[0m'%(class_obj,class_obj.students))
                            for stu in list(class_obj.students):
                                status = input('%s %s %s QQ:%s 打卡状态》：'%(class_obj,lesson_obj,stu,stu.QQ)).strip()
                                if not status:
                                    print('跳过 ：%s'% stu)
                                    continue
                                else:
                                    record_obj = self.session.query(tables_class.Stu_record).filter_by(lesson_id=lesson_obj.id,
                                                                                                       stu_id=stu.id).first()
                                    if record_obj:
                                        record_obj.status = status
                                        self.session.commit()
                                        print('\033[33;1m 已有该学生记录，修改签到状态提交成功！\033[0m')
                                        # break
                                    else:
                                        record_obj = tables_class.Stu_record(lesson_id=lesson_obj.id, stu_id=stu.id,
                                                                             status=status)
                                        self.session.add_all([record_obj])  # 加入session
                                        self.session.commit()  # 提交保存到数据库
                                        print('\033[33;1m 新建签到状态提交成功！！\033[0m')
                            print('\033[32;1m 所有学生签到状态修改完成\033[0m');break
                        else:
                            print('\033[31; 您选择的课程不存在该班级中\033[0m')

                    break

        pass
    def modify_score(self):
        classes = self.teacher_obj.classes
        if not classes:
            print('\033[31;1m您不教任何一个班级，无法添加上课记录\033[0m')
        else:
            print('\033[32;1m您所教班级有\033[0m')
            for i in classes:
                print(i.id, i)
            while True:
                input_class_id = input('\033[32;1m 请选择班级名称代号，修改成绩>>>\033[0m').strip()
                class_obj = self.session.query(tables_class.Class).filter_by(id=input_class_id).first()
                if not class_obj:
                    print('\033[31;1m 输入的班级代号不存在,请重新输入。\033[0m')
                    # continue
                else:
                    while True:
                        print('%s 班 已有课程 %s' % (class_obj, class_obj.lessons))
                        chose_lesson = input('\033[32;1m 请选择课程\033[0m')
                        lesson_obj = self.session.query(tables_class.Lesson).filter_by(name=chose_lesson).first()
                        if lesson_obj in list(class_obj.lessons):
                            print('\033[32;1m %s 学生有：%s\033[0m' % (class_obj, class_obj.students))
                            for stu in list(class_obj.students):
                                score = input('%s %s %s QQ:%s 输入成绩：' % (class_obj, lesson_obj, stu, stu.QQ)).strip()
                                if not score:
                                    print('跳过 ：%s' % stu)
                                    continue
                                else:
                                    record_obj = self.session.query(tables_class.Stu_record).filter_by(
                                        lesson_id=lesson_obj.id,
                                        stu_id=stu.id).first()
                                    if record_obj:
                                        record_obj.score = score
                                        self.session.commit()
                                        print('\033[33;1m 已有该学生记录，修改学生成绩提交成功！\033[0m')
                                        # break
                                    else:
                                        record_obj = tables_class.Stu_record(lesson_id=lesson_obj.id, stu_id=stu.id,
                                                                             score=score)
                                        self.session.add_all([record_obj])  # 加入session
                                        self.session.commit()  # 提交保存到数据库
                                        print('\033[33;1m 新建学生成绩提交成功！！\033[0m')
                            print('\033[32;1m 所有学生成绩提交完成\033[0m');break
                        else:
                            print('\033[31; 您选择的课程不存在该班级中\033[0m')

                    break
        pass

#!/usr/bin/env python
# Author:zhangmingda
import tables_class

class Student_view(object):
    def __init__(self,session):
        self.session = session
        self.login_status = False
        self.student_obj = None
    def sign_up(self):
        name = input('\033[32;0m创建您的用户名：\033[0m ').strip()
        QQ = input('\033[32;0m输入您的QQ号码：\033[0m ')
        stu_obj = tables_class.Student(name=name,QQ=QQ)
        stu_exist = self.session.query(tables_class.Student).filter_by(QQ=QQ).first()
        if stu_exist:
            print('\033[31;1m%s 已存在  QQ：%s\033[0m'%(stu_exist,stu_exist.QQ))
        else:
            print('库里没这QQ号的个人，开始创建')
            self.session.add(stu_obj)
            self.session.commit()
            print('\033[32;1m %s 创建成功\033[0m'% stu_obj)

    def auth(func):
        '''简易登录认证装饰器'''
        def wapper(*args,**kwargs):
            self = args[0] #将实例自己本身获取到
            while not self.login_status:
                student_name = input('\033[34;1m请输入学生名字：\033[0m').strip()
                QQ = input('\033[34;1m请输入QQ号码：\033[0m').strip()
                student_obj = self.session.query(tables_class.Student).filter_by(name=student_name,QQ=QQ).first()
                if student_obj:
                    self.student_obj = student_obj
                    self.login_status = True
                    func(self)
                    break
                else:
                    print('\033[31;1m 用户不存在\033[0m')
            else:
                func(self)
        return wapper

    @auth
    def hand_in_homework(self):

        classes = self.student_obj.classes
        if not classes:
            print('\033[31;1m您未加入任何班级，无法交作业\033[0m')
        else :
            print('\033[32;1m您已报培训班\033[0m')
            for i in classes:
                print(i)
            while True:
                input_class = input('\033[32;1m 请输入要交作业的班级名称>>>\033[0m').strip()
                class_obj = self.session.query(tables_class.Class).filter_by(name=input_class).first()
                if not class_obj:
                    print('\033[31;1m 输入的班级名称不存在,请重新输入。\033[0m')
                else:
                    class_lessons = class_obj.lessons
                    print('\033[33;1m班级：%s当前已关联课程：%s'%(class_obj,class_lessons))
                    lesson_name = input('\033[32;1m 输入要交作业的课程名称>>> \033[0m')
                    lesson_obj_exist = self.session.query(tables_class.Lesson).filter_by(name=lesson_name).first()
                    #课程已存在，并且已经关联该班级
                    if lesson_obj_exist  in list(class_lessons):
                        lesson_id = lesson_obj_exist.id
                        stu_id = self.student_obj.id
                        homework = 'Already Submit'
                        record_obj = self.session.query(tables_class.Stu_record).filter_by(lesson_id=lesson_id,stu_id=stu_id).first()
                        if record_obj:
                            record_obj.homework = homework
                            self.session.commit()
                            print('\033[33;1m 作业提交成功！\033[0m')
                            break
                        else:
                            record_obj = tables_class.Stu_record(lesson_id=lesson_id,stu_id=stu_id,homework=homework)
                            self.session.add_all([record_obj]) #加入session
                            self.session.commit() #提交保存到数据库
                            print('\033[33;1m 作业提交成功！\033[0m')
                            break

    @auth
    def show_score(self):
        classes = self.student_obj.classes
        if not classes:
            print('\033[31;1m您未加入任何班级，无法查看成绩\033[0m')
        else:
            print('\033[32;1m您已报培训班\033[0m')
            for i in classes:
                print(i)
            while True:
                input_class = input('\033[32;1m 请输入要查看成绩的班级名称>>>\033[0m').strip()
                class_obj = self.session.query(tables_class.Class).filter_by(name=input_class).first()
                if not class_obj:
                    print('\033[31;1m 输入的班级名称不存在,请重新输入。\033[0m')
                else:
                    class_lessons = class_obj.lessons
                    print('\033[33;1m班级：%s当前已关联课程：%s' % (class_obj, class_lessons))
                    lesson_name = input('\033[32;1m 输入要查看成绩的课程名称，查看全部输入：all>>> \033[0m')
                    lesson_obj_exist = self.session.query(tables_class.Lesson).filter_by(name=lesson_name).first()
                    # 课程已存在，并且已经关联该班级
                    if lesson_obj_exist in list(class_lessons):
                        lesson_id = lesson_obj_exist.id
                        stu_id = self.student_obj.id
                        stu_record_obj = self.session.query(tables_class.Stu_record).filter_by(lesson_id=lesson_id,stu_id=stu_id).first()
                        # print('\033[32;1m%s %s 成绩：%s\033[0m'% (self.student_obj,lesson_obj_exist,stu_record_obj.score))
                        print(stu_record_obj)
                        break
                    elif lesson_name == 'all':
                        print('\033[32;1m 您 %s 全部课程成绩如下 \033[0m'% class_obj)
                        stu_record_obj_all = self.session.query(tables_class.Stu_record).filter_by(stu_id=self.student_obj.id).all()
                        for stu_record_obj in stu_record_obj_all:
                            if stu_record_obj.lessons_name in list(class_lessons):
                                print(stu_record_obj)
                        break
                    else:
                        print('\033[31 您输入的课程不存在\033[0m')
    @auth
    def show_ranking(self):
        classes = self.student_obj.classes
        if not classes:
            print('\033[31;1m您未加入任何班级，无法查看成绩排名\033[0m')
        else:
            print('\033[32;1m您已报培训班\033[0m')
            for i in classes:
                print(i)
            while True:
                input_class = input('\033[32;1m 请输入要查看成绩的班级名称>>>\033[0m').strip()
                class_obj = self.session.query(tables_class.Class).filter_by(name=input_class).first()
                if not class_obj:
                    print('\033[31;1m 输入的班级名称不存在,请重新输入。\033[0m')
                else:
                    class_lessons = class_obj.lessons
                    print('\033[33;1m班级：%s当前已关联课程：%s' % (class_obj, class_lessons))
                    lesson_name = input('\033[32;1m 输入要查看成绩的课程名称>>> \033[0m')
                    lesson_obj_exist = self.session.query(tables_class.Lesson).filter_by(name=lesson_name).first()
                    if lesson_obj_exist in list(class_lessons):
                        students_score = self.session.query(tables_class.Stu_record).filter_by(lesson_id=lesson_obj_exist.id).order_by(tables_class.Stu_record.score.desc()).all()
                        students_score_list = list(students_score)
                        for ranking,score_obj in enumerate(students_score_list,1):
                            if score_obj.stu_id == self.student_obj.id:
                                print('\033[32;1m 您在 %s %s 中排第%s名\033[0m'%(class_obj,lesson_obj_exist,ranking))
                        break
                    else:
                        print('\033[31 您输入的课程不存在\033[0m')

    def handle(self):
        while True:
            print('\033[32;1m欢迎进入学生操作界面,请输入要操作的代号：\033[0m')
            menu = {
                1:"注册",
                2:"交作业",
                3:"查看成绩",
                4:"查看排名",
                5:"退出系统"
            }
            menu_dict = {
                "1": self.sign_up,
                "2": self.hand_in_homework,
                "3": self.show_score,
                "4": self.show_ranking,
                "5": 'exit'
            }
            for i in menu:
                print(i,menu[i])
            chose = input('请选择>>>').strip()
            if  chose in menu_dict and chose != "5" :
                menu_dict[chose]()
            elif chose == '5':break
            else:
                print('\033[31;1m您输入的代号不存在，请重新输入。\033[0m')
        pass