sqlalchemy 多对多

Student(Base): __tablename__ = ‘student‘ id = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=False) course_list = relationship(‘Course‘, secondary=‘student2course‘, backref=‘student_list‘) class Course(Base): __tablename__ = ‘course‘ id = Column(Integer, primary_key=True) title = Column(String(32), index=True, nullable=False) class Student2Course(Base): __tablename__ = ‘student2course‘ id = Column(Integer, primary_key=True, autoincrement=True) student_id = Column(Integer, ForeignKey(‘student.id‘)) course_id = Column(Integer, ForeignKey(‘course.id‘)) __table_args__ = ( UniqueConstraint(‘student_id‘, ‘course_id‘, name=‘uix_stu_cou‘), # 联合唯一索引 # Index(‘ix_id_name‘, ‘name‘, ‘extra‘), # 联合索引 )

注意：要自己创建第三张表

二、操作数据

# 1. 创建一个课程，创建2学生，两个学生选新创建的课程。
obj = Course(title=‘英语‘)
obj.student_list = [Student(name=‘b‘),Student(name=‘c‘)]

session.add(obj)
session.commit()

# 2. 三张表join
ret = session.query(Student2Course.id,Student.name,Course.title).join(Student,Student2Course.student_id==Student.id,isouter=True).join(Course,Student2Course.course_id==Course.id,isouter=True).order_by(Student2Course.id.asc())
for row in ret:
    print(row)
# 3. “a”选的所有课
# 原生
ret = session.query(Student2Course.id,Student.name,Course.title).join(Student,Student2Course.student_id==Student.id,isouter=True).join(Course,Student2Course.course_id==Course.id,isouter=True).filter(Student.name==‘a‘).order_by(Student2Course.id.asc()).all()
print(ret)
# relation
obj = session.query(Student).filter(Student.name==‘a‘).first()
for item in obj.course_list:
    print(item.title)

# 4. 选了“生物”的所有人
obj = session.query(Course).filter(Course.title==‘生物‘).first()
for item in obj.student_list:
    print(item.name)

 

sqlalchemy 多对多

标签：ref   关系   outer   创建   自己   数据   secondary   lse   操作