在SQLAlchemy中查询多对多关系

from sqlalchemy import create_engine, Column, Integer, String, Table
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建数据库引擎
engine = create_engine('sqlite:///example.db')

# 创建会话工厂
Session = sessionmaker(bind=engine)
session = Session()

# 创建基类
Base = declarative_base()

# 创建中间表
association_table = Table('association', Base.metadata,
    Column('student_id', Integer, ForeignKey('students.id')),
    Column('course_id', Integer, ForeignKey('courses.id'))
)

# 创建学生类
class Student(Base):
    __tablename__ = 'students'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    courses = relationship('Course', secondary=association_table, back_populates='students')

# 创建课程类
class Course(Base):
    __tablename__ = 'courses'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    students = relationship('Student', secondary=association_table, back_populates='courses')

# 查询多对多关系
student = session.query(Student).filter(Student.name == 'John').first()
courses = student.courses

for course in courses:
    print(course.name)