如何使用 SQLAlchemy 建立多对多关系:一个很好的例子 [英] How to build many-to-many relations using SQLAlchemy: a good example

问题描述

我已经阅读了关于构建多对多关系的 SQLAlchemy 文档和教程，但是当关联表包含超过 2 个外键时，我无法弄清楚如何正确地做到这一点.

I have read the SQLAlchemy documentation and tutorial about building many-to-many relation but I could not figure out how to do it properly when the association table contains more than the 2 foreign keys.

我有一个项目表，每个项目都有很多细节.很多项目的细节可以相同，因此项目和细节之间存在多对多关系

I have a table of items and every item has many details. Details can be the same on many items, so there is a many-to-many relation between items and details

我有以下几点:

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)

我的关联表是(它在代码中的其他 2 个之前定义):

My association table is (It's defined before the other 2 in the code):

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)

在文档中，据说我需要使用关联对象".我不知道如何正确使用它，因为它混合了声明性和映射器表单，并且示例似乎不完整.我添加了这一行:

In the documentation, it's said that I need to use the "association object". I could not figure out how to use it properly, since it's mixed declarative with mapper forms and the examples seem not to be complete. I added the line:

details = relation(ItemDetail)

作为Item类和行的成员:

as a member of Item class and the line:

itemDetail = relation('Detail')

作为关联表的成员，如文档中所述.

as a member of the association table, as described in the documentation.

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

when I do item = session.query(Item).first(), the item.details is not a list of Detail objects, but a list of ItemDetail objects.

如何在 Item 对象中正确获取详细信息，即 item.details 应该是 Detail 对象的列表?

How can I get details properly in Item objects, i.e., item.details should be a list of Detail objects?

推荐答案

从评论中我看到您找到了答案.但是对于新用户"来说，SQLAlchemy 文档是相当繁重的，我也在为同样的问题苦苦挣扎.因此，以供将来参考:

From the comments I see you've found the answer. But the SQLAlchemy documentation is quite overwhelming for a 'new user' and I was struggling with the same question. So for future reference:

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')

这篇关于如何使用 SQLAlchemy 建立多对多关系:一个很好的例子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！