python - How to build many-to-many relations using SQLAlchemy: a good example

Question

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

I have the following:

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)

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)

as a member of Item class and the line:

itemDetail = relation('Detail')

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

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

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

Answer 1

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