[Question] Creating M2M relationship to same table

[FalseDev]

Question

How do I create a many to many relationship from a table to itself in the 2.0 style.

Example

Code

from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import BigInteger, Integer

Base = declarative_base()


class ParentToChild(Base):
    __tablename__ = "parent_to_child"

    id = Column(Integer, primary_key=True)

    parent= Column(Integer(), ForeignKey("Person.id"), nullable=True)
    child= Column(Integer(), ForeignKey("Person.id"), nullable=True)

class Person(Base):
    __tablename__ = "person"

    id = Column(Integer, primary_key=True)

    parents = relationship(
        ParentToChild,
        back_populates="children",
        secondary=ParentToChild,
        primaryjoin=id==ParentToChild.parent,
        secondaryjoin=id==ParentToChild.child,
    )

    children = relationship(
        # Integer,
        ParentToChild,
        # ForeignKey,
        back_populates="parent",
        secondary=ParentToChild,
        primaryjoin=id==ParentToChild.child,
        secondaryjoin=id==ParentToChild.parent,
    )



engine = create_engine("sqlite:///:memory:")
Session = sessionmaker(Base)

if __name__ == "__main__":
    with Session() as session:
        session.add(Person(id=1))
        session.add(Person(id=1))
        session.commit()

Stacktrace

Traceback (most recent call last):
  File "/home/falsedev/sql/main.py", line 49, in <module>
    session.add(Person(id=1))
  File "<string>", line 4, in __init__
  File "/home/falsedev/.venv/dpy/lib/python3.9/site-packages/sqlalchemy/orm/state.py", line 431, in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
  File "/home/falsedev/.venv/dpy/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 320, in __call__
    fn(*args, **kw)
  File "/home/falsedev/.venv/dpy/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3569, in _event_on_init
    instrumenting_mapper._check_configure()
  File "/home/falsedev/.venv/dpy/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1874, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "/home/falsedev/.venv/dpy/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3384, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "/home/falsedev/.venv/dpy/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 3423, in _do_configure_registries
    mapper._post_configure_properties()
  File "/home/falsedev/.venv/dpy/lib/python3.9/site-packages/sqlalchemy/orm/mapper.py", line 1891, in _post_configure_properties
    prop.init()
  File "/home/falsedev/.venv/dpy/lib/python3.9/site-packages/sqlalchemy/orm/interfaces.py", line 228, in init
    self.do_init()
  File "/home/falsedev/.venv/dpy/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2135, in do_init
    self._process_dependent_arguments()
  File "/home/falsedev/.venv/dpy/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 2198, in _process_dependent_arguments
    raise sa_exc.ArgumentError(
sqlalchemy.exc.ArgumentError: secondary argument <class '__main__.ParentToChild'> passed to to relationship() Person.parents 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.

Versions

• OS: Manjaro
• Python: 3.9.2
• SQLAlchemy: 1.4.9
• Database: Sqlite3
• DBAPI: pysqlite

[CaselIT]

Hi,

You can pass table name to the secondary, so secondary='parent_to_child'. Note that usually the secondary table is a plain table, defined with Table that's not mapped to a class (but will work even with your configuration)

[FalseDev]

My code posted earlier had numerous rookie mistakes (I am indeed very to new sqlalchemy)

Working example

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Integer

Base = declarative_base()


class ParentToChild(Base):
    __tablename__ = "parent_to_child"

    id = Column(Integer, primary_key=True)

    parent = Column(Integer(), ForeignKey("person.id"), nullable=False)
    child = Column(Integer(), ForeignKey("person.id"), nullable=False)


class Person(Base):
    __tablename__ = "person"

    id = Column(Integer, primary_key=True)

    parents = relationship(
        "Person",
        secondary="parent_to_child",
        primaryjoin=id == ParentToChild.child,
        secondaryjoin=id == ParentToChild.parent,
        backref="children",
    )


engine = create_engine("sqlite:///:memory:")
Session = sessionmaker(bind=engine)
with engine.begin() as conn:
    Base.metadata.create_all(conn)

if __name__ == "__main__":
    with Session() as session:
        session.add(Person(id=1))
        session.add(Person(id=2))
        session.add(ParentToChild(parent=1, child=2))
        session.commit()

CaselIT, thank you very much for helping out quickly!

[CaselIT]

Glad you could figure it out.
BTW, backref is kinda advised against, on new code I would use back_populates that plays nice with ides and is cleared to read

[FalseDev]

Sure, thanks for the tip!

And from what I could tell back_populates requires creation of the other end in the code while backref adds it to the table but compromises on IDE support and type checking.

This is the field for the other end of the relationship I've written

children:List["Person"] = relationship( # type: ignore
    "Person",
    secondary="parent_to_child",
    primaryjoin=id == ParentToChild.parent,
    secondaryjoin=id == ParentToChild.child,
    back_populates="parents",
)

[CaselIT]

yes, back_populates requires an explicit definition of the other side of the relationship, but that's usually best for readability, IDE ect