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