mysql系统开发_mysql开发简单的学员管理系统

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")