from sqlalchemy importTable, Column, Integer,String,DATE, ForeignKeyfrom sqlalchemy.orm importrelationshipfrom sqlalchemy.ext.declarative importdeclarative_basefrom sqlalchemy importcreate_enginefrom sqlalchemy.orm importsessionmakerimportrandom
engine= create_engine("mysql+pymysql://root:123456@localhost/test",
encoding='utf-8')
Base=declarative_base()#以这种方式创建的我们不必再赋值其他参数#班级与学生的多对多关系
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")),
)classClass_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)classStudy_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)classTeacher(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_nameclassClass(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_nameclassStudent(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_nameclassLesson(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)#初始化阶段,现在我们将列表中放入数据
S1=Student(stu_name="mark",QQ=1)
S2=Student(stu_name="judy",QQ=2)
S3=Student(stu_name="jack",QQ=3)
T1=Teacher(teacher_name="a")
T2=Teacher(teacher_name="b")
c1=Class(class_name="1c",course="math")
c2=Class(class_name="2c",course="english")
L1=Lesson(lesson_id=1,lesson_name="math")
L2=Lesson(lesson_id=2,lesson_name="english")#实现了每个班级有多个学生,每个学生也可以报多个班级
c1.students=[S1,S2]
c2.students=[S1,S3]#实现教师班级的多对多
T1.classes=[c2]
T2.classes=[c1,c2]#初始化lessens_to_classes
cl1= Class_m2m_Lesson(class_id=1,lesson_id=1)
Session_class= sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
session = Session_class() #生成session实例
session.add_all([S1,S2,S3,T1,T2,c1,c2,L1,L2,cl1])
session.commit()classStudent_Center(object):"学生视图"
def __init__(self,session):
self.session=session
self.authentication()
self.handler()defhandler(self):whileTrue:print("\033[36;1m欢迎【%s】进入学员管理系统\n"
"up_homework 上传作业\n"
"show_homework 查看作业成绩\n"
"show_rank 查看班级排名"
"exit 退出管理系统\n\033[0m" %self.student_obj.stu_name)
user_func= input("\033[34;0m请输入进行操作的命令:\033[0m")ifhasattr(self, user_func):
getattr(self, user_func)()defauthentication(self):'''认证'''
whileTrue:
student_name= input("\033[34;0m请输入学生名:\033[0m").strip()
self.student_obj= self.session.query(Student).filter_by(stu_name=student_name).first()if notself.student_obj:print("\33[31;1m输入错误:请输入有效的学生名\33[0m")continue
else:#print(self.teacher_obj)
break
defup_homework(self):"上传作业"class_name= input("\033[34;0m请输入班级名:\033[0m")for class_obj inself.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")