How to initialise a many-to-many class with SQLAlchemy using classical mapping

Question

I'm using SQLAlchemy and have a many-to-many relationship between Parents and Children:

tables.py

parentLinkTable = Table('parent_links', metadata,
    Column('parent_id', BigInteger, ForeignKey('parent.id'), primary_key=True),
    Column('child_id', BigInteger, ForeignKey('child.id'), primary_key=True)
)

parentTable = Table('parent', metadata,
    Column('id', BigInteger, primary_key=True),
    Column('notes', Text)
)

childTable = Table('child', metadata,
    Column('id', BigInteger, primary_key=True),
    Column('notes', Text)
)

entities.py

class Parent():

    children = sqlalchemy.orm.relationship('Child', secondary=tables.parentLinkTable, back_populates='parents')


    def __init__(self, children: list, **kwargs):

        self.children = children

        for key, value in kwargs.items():
            setattr(self, key, value)

class Child():

    parents = sqlalchemy.orm.relationship('Parent', secondary=tables.parentLinkTable, back_populates='children')

    def __init__(self, **kwargs):

        for key, value in kwargs.items():
            setattr(self, key, value)

orm.py

import tables
import entities

sqlalchemy.orm.mapper(entities.Child, tables.childTable)
sqlalchemy.orm.mapper(entities.Parent, tables.parentTable)

application.py

children = session.query(Child).all()
parent = Cluster(children)
session.add(parent)
session.commit()

This code runs with no errors. However, while a new Cluster is persisted, no entries are created on the clusterLinkTable. I think the problem lies with the mixture of classical and declarative mapping. I think I am meant to use properties in sqlalchemy.orm.mapper but I am not sure how.

Answer 1

You're not using classical mapping correctly. Please refer to the documentation for a complete example. In particular, you cannot declare any relationships or columns inside the class itself. The correct incantation is:

class Parent(object):
    def __init__(self, children: list, **kwargs):
        ...

class Child(object):
    def __init__(self, **kwargs):
        ...

sqlalchemy.orm.mapper(entities.Child, tables.childTable, properties={
    "parent": sqlalchemy.orm.relationship(entities.Parent, secondary=tables.parentLinkTable, back_populates='children')
})
sqlalchemy.orm.mapper(entities.Parent, tables.parentTable, properties={
    "children": sqlalchemy.orm.relationship(entities.Child, secondary=tables.parentLinkTable, back_populates='parents')
})