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

Previous Answer worked for me, but I used a Class base approach for the table ItemDetail. This is the Sample code:

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

Like Miguel, I'm also using a Declarative approach for my junction table. However, I kept running into errors like

sqlalchemy.exc.ArgumentError: secondary argument <class 'main.ProjectUser'> passed to to relationship() User.projects must be a Table object or other FROM clause; can't send a mapped class directly as rows in 'secondary' are persisted independently of a class that is mapped to that same table.

With some fiddling, I was able to come up with the following. (Note my classes are different than OP's but the concept is the same.)

Example

Here's a full working example

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)

Notes

1. reference the table name in the secondary argument like secondary='project_users' as opposed to secondary=ProjectUser
2. use back_populates instead of backref

I made a detailed writeup about this here.

Answer 3

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