如何使用SQLAlchemy构建多对多关系：一个好例子

Question

我看过关于SQLAlchemy的文档和教程，学习如何建立多对多关系，但当关联表包含超过两个外键时，我还是搞不清楚该怎么做。

我有一个物品表，每个物品都有很多细节。细节可以在多个物品中是相同的，所以物品和细节之间是多对多的关系。

我有以下内容：

class Item(Base):
    __tablename__ = 'Item'
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    description = Column(Text)

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

我的关联表是（在代码中，它是在其他两个表之前定义的）：

class ItemDetail(Base):
    __tablename__ = 'ItemDetail'
    id = Column(Integer, primary_key=True)
    itemId = Column(Integer, ForeignKey('Item.id'))
    detailId = Column(Integer, ForeignKey('Detail.id'))
    endDate = Column(Date)

文档中提到我需要使用“关联对象”。但我不太明白该怎么正确使用，因为它把声明式和映射形式混在一起，而且示例似乎不够完整。我添加了这一行：

details = relation(ItemDetail)

作为Item类的一个成员，并且添加了这一行：

itemDetail = relation('Detail')

作为关联表的一个成员，正如文档中所描述的。

当我执行item = session.query(Item).first()时，item.details并不是Detail对象的列表，而是ItemDetail对象的列表。

我该如何才能在Item对象中正确获取细节，也就是说，item.details应该是Detail对象的列表？

Answer 1

之前的回答对我有用，但我使用了基于类的方法来处理表格 ItemDetail。这是示例代码：

class ItemDetail(Base):
    __tablename__ = 'ItemDetail'
    id = Column(Integer, primary_key=True, index=True)
    itemId = Column(Integer, ForeignKey('Item.id'))
    detailId = Column(Integer, ForeignKey('Detail.id'))
    endDate = Column(Date)

class Item(Base):
    __tablename__ = 'Item'
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    description = Column(Text)
    details = relationship('Detail', secondary=ItemDetail.__table__, backref='Item')

class Detail(Base):
    __tablename__ = 'Detail'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    value = Column(String)
    items = relationship('Item', secondary=ItemDetail.__table__, backref='Detail')

Answer 2

和Miguel一样，我也在用声明式的方法来处理我的连接表。不过，我遇到了一些错误，比如：

sqlalchemy.exc.ArgumentError: 传给relationship()的第二个参数<class 'main.ProjectUser'>必须是一个表对象或其他FROM子句；不能直接把映射类作为'二级'的行，因为这些行是独立于映射到同一表的类而持久化的。

经过一些调整，我终于找到了一个解决方案。（注意我的类和提问者的不同，但概念是一样的。）

示例

这是一个完整的工作示例：

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, Session

# Make the engine
engine = create_engine("sqlite+pysqlite:///:memory:", future=True, echo=False)

# Make the DeclarativeMeta
Base = declarative_base()


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String)
    projects = relationship('Project', secondary='project_users', back_populates='users')


class Project(Base):
    __tablename__ = "projects"

    id = Column(Integer, primary_key=True)
    name = Column(String)
    users = relationship('User', secondary='project_users', back_populates='projects')


class ProjectUser(Base):
    __tablename__ = "project_users"

    id = Column(Integer, primary_key=True)
    notes = Column(String, nullable=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    project_id = Column(Integer, ForeignKey('projects.id'))



# Create the tables in the database
Base.metadata.create_all(engine)

# Test it
with Session(bind=engine) as session:

    # add users
    usr1 = User(name="bob")
    session.add(usr1)

    usr2 = User(name="alice")
    session.add(usr2)

    session.commit()

    # add projects
    prj1 = Project(name="Project 1")
    session.add(prj1)

    prj2 = Project(name="Project 2")
    session.add(prj2)

    session.commit()

    # map users to projects
    prj1.users = [usr1, usr2]
    prj2.users = [usr2]

    session.commit()


with Session(bind=engine) as session:

    print(session.query(User).where(User.id == 1).one().projects)
    print(session.query(Project).where(Project.id == 1).one().users)

注意事项

1. 在secondary参数中引用表名时，应该用secondary='project_users'，而不是secondary=ProjectUser
2. 使用back_populates而不是backref

我对此做了详细的说明，可以在这里找到。

Answer 3

从评论中我看到你已经找到答案了。不过，SQLAlchemy的文档对新手来说确实有点复杂，我之前也在为同样的问题苦恼。所以为了以后能参考一下：

ItemDetail = Table('ItemDetail',
    Column('id', Integer, primary_key=True),
    Column('itemId', Integer, ForeignKey('Item.id')),
    Column('detailId', Integer, ForeignKey('Detail.id')),
    Column('endDate', Date))

class Item(Base):
    __tablename__ = 'Item'
    id = Column(Integer, primary_key=True)
    name = Column(String(255))
    description = Column(Text)
    details = relationship('Detail', secondary=ItemDetail, backref='Item')

class Detail(Base):
    __tablename__ = 'Detail'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    value = Column(String)
    items = relationship('Item', secondary=ItemDetail, backref='Detail')