SQLAlchemy -使用关联表进行多对多查询

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

Base = declarative_base()

user_role_table = Table('user_role', Base.metadata,
    Column('user_id', Integer, ForeignKey('user.id')),
    Column('role_id', Integer, ForeignKey('role.id'))
)

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    roles = relationship('Role', secondary=user_role_table, backref='users')

class Role(Base):
    __tablename__ = 'role'
    id = Column(Integer, primary_key=True)
    name = Column(String)

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('数据库连接字符串')
Session = sessionmaker(bind=engine)
session = Session()

users_with_role = session.query(User).join(User.roles).filter(Role.name == 'admin').all()