SqlAlchemy many to many relation with mm table

Question

I am still a beginner in Python and I am stuck with the following relation.

Three tables:

• tx_bdproductsdb_domain_model_product
• sys_category
• sys_category_record_mm

sys_category class looks like this:

class Category(Base):
    __tablename__ = "sys_category"

    uid = Column(
        Integer,
        ForeignKey("sys_category_record_mm.uid_local"),
        primary_key=True,
        autoincrement=True,
    )
    title = Column(String)
    products = relationship(
        "Product",
        uselist=False,
        secondary="sys_category_record_mm",
        back_populates="categories",
        foreign_keys=[uid],
    )

Products looks like this:

class Product(Base):
    __tablename__ = "tx_bdproductsdb_domain_model_product"

    uid = Column(
        Integer,
        ForeignKey(SysCategoryMMProduct.uid_foreign),
        primary_key=True,
        autoincrement=True,
    )
    
    category = Column(Integer)
    categories = relationship(
        Category,
        secondary=SysCategoryMMProduct,
        back_populates="products",
        foreign_keys=[uid],
    )

And here is the mm table class that should link the two.

class SysCategoryMMProduct(Base):
    __tablename__ = "sys_category_record_mm"

    uid_local = Column(Integer, ForeignKey(Category.uid), primary_key=True)
    uid_foreign = Column(
        Integer, ForeignKey("tx_bdproductsdb_domain_model_product.uid")
    )
    fieldname = Column(String)

I'm currently stuck, does anyone have any ideas? I get the following messages in the console:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Category.products - there are no foreign keys linking these tables via secondary table 'sys_category_record_mm'.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' expressions.
root@booba:/var/pythonWorks/crawler/develop/releases/current# python3 Scraper2.py 
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/relationships.py", line 2739, in _determine_joins
    self.secondaryjoin = join_condition(
  File "<string>", line 2, in join_condition
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/selectable.py", line 1229, in _join_condition
    raise exc.NoForeignKeysError(
sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'tx_bdproductsdb_domain_model_product' and 'sys_category_record_mm'.

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Category.products - there are no foreign keys linking these tables via secondary table 'sys_category_record_mm'.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' expressions.

Thank you :)

Answer 1

When using an association class you should reference the association directly. You need this instead of secondary because you have data associated with the link (ie. fieldname). I changed some of your naming schema to make it more clear.

There is a pretty good explanation of the association pattern in the sqlalchemy docs. There is a big red warning at the end of that section about mixing the use of the secondary and the Association pattern.

I use backref="related_categories" to automatically create the property related_categories on Product. This is a list of association objects, and not actual categories.

from sqlalchemy import (
    create_engine,
    Integer,
    String,
    ForeignKey,

)
from sqlalchemy.schema import (
    Column,
)
from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy.orm import Session


Base = declarative_base()

# This connection string is made up
engine = create_engine(
    'postgresql+psycopg2://user:pw@/db',
    echo=False)


class Category(Base):
    __tablename__ = "categories"

    uid = Column(
        Integer,
        primary_key=True,
        autoincrement=True,
    )
    title = Column(String)


class Product(Base):
    __tablename__ = "products"

    uid = Column(
        Integer,
        primary_key=True,
        autoincrement=True,
    )

    title = Column(String)


class SysCategoryMMProduct(Base):
    __tablename__ = "categories_products"
    uid = Column(Integer, primary_key=True)
    category_uid = Column(Integer, ForeignKey("categories.uid"))
    product_uid = Column(Integer, ForeignKey("products.uid"))
    fieldname = Column(String)

    product = relationship(
        "Product",
        backref="related_categories",
    )

    category = relationship(
        "Category",
        backref="related_products",
    )


Base.metadata.create_all(engine)

with Session(engine) as session:
    category = Category(title="kitchen")
    session.add(category)
    product = Product(title="spoon")
    session.add(product)
    association = SysCategoryMMProduct(
        product=product,
        category=category,
        fieldname="Extra metadata")
    session.add(association)
    session.commit()

    category = session.query(Category).first()
    assert len(category.related_products) == 1
    assert category.related_products[0].product.related_categories[0].category == category

    q = session.query(Category).join(Category.related_products).join(SysCategoryMMProduct.product).filter(Product.title == "spoon")
    print (q)
    assert q.first() == category

The last query looks like:

SELECT categories.uid AS categories_uid, categories.title AS categories_title 
FROM categories JOIN categories_products ON categories.uid = categories_products.category_uid JOIN products ON products.uid = categories_products.product_uid 
WHERE products.title = 'spoon'