#!/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