Python中SQLAlchemy多对多关联表如何查询额外的字段？

在SQLAlchemy里处理多对多关联表的额外字段，得用关联对象（association object）模式。直接上代码示例：

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

Base = declarative_base()

# 关联表变成正式的模型类
class UserGroupAssociation(Base):
    __tablename__ = 'user_group_association'
    
    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    group_id = Column(Integer, ForeignKey('groups.id'), primary_key=True)
    # 这是你的额外字段
    role = Column(String(50))
    
    # 关联到两个主表
    user = relationship("User", back_populates="group_associations")
    group = relationship("Group", back_populates="user_associations")

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    
    # 通过关联表连接到Group
    group_associations = relationship("UserGroupAssociation", back_populates="user")
    groups = relationship("Group", secondary="user_group_association", viewonly=True)

class Group(Base):
    __tablename__ = 'groups'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    
    user_associations = relationship("UserGroupAssociation", back_populates="group")
    users = relationship("User", secondary="user_group_association", viewonly=True)

# 使用示例
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# 创建数据
user1 = User(name="张三")
group1 = Group(name="开发组")

# 添加关联并设置额外字段
association = UserGroupAssociation(user=user1, group=group1, role="管理员")
session.add_all([user1, group1, association])
session.commit()

# 查询：获取用户在所有群组中的角色
user = session.query(User).filter_by(name="张三").first()
for assoc in user.group_associations:
    print(f"用户 {user.name} 在群组 {assoc.group.name} 中的角色是: {assoc.role}")

# 查询：获取群组中的所有用户及其角色
group = session.query(Group).filter_by(name="开发组").first()
for assoc in group.user_associations:
    print(f"群组 {group.name} 中的用户 {assoc.user.name} 角色是: {assoc.role}")

核心就两点：1）把纯关联表升级成带额外字段的模型类；2）用relationship的secondary参数指向这个关联表，同时设置viewonly=True来避免直接操作。查询时通过关联对象访问额外字段。

总结：用关联对象模式处理多对多额外字段。