Sqlalchemy: Many to Many relationship error

Question

Dear everyone, I am following the Many to many relationship described on http://www.sqlalchemy.org/docs/mappers.html#many-to-many

#This is actually a VIEW
tb_mapping_uGroups_uProducts = Table( 'mapping_uGroups_uProducts', metadata,
    Column('upID', Integer, ForeignKey('uProductsInfo.upID')),
    Column('ugID', Integer, ForeignKey('uGroupsInfo.ugID'))
)

tb_uProducts = Table( 'uProductsInfo', metadata, 
    Column('upID', Integer, primary_key=True)
)
mapper( UnifiedProduct, tb_uProducts)

tb_uGroupsInfo = Table( 'uGroupsInfo', metadata, 
    Column('ugID', Integer, primary_key=True)
)
mapper( UnifiedGroup, tb_uGroupsInfo, properties={
    'unifiedProducts': relation(UnifiedProduct, secondary=tb_mapping_uGroups_uProducts, backref="unifiedGroups")
})

where the relationship between uProduct and uGroup are N:M.

When I run the following

sess.query(UnifiedProduct).join(UnifiedGroup).distinct()[:10]

I am getting the error:

sqlalchemy.exc.ArgumentError: Can't find any foreign key relationships between 'uProductsInfo' and 'uGroupsInfo'

What am I doing wrong?

EDIT: I am on MyISAM which doesn't support forigen keys

Answer 1

Existence of foreign key definitions in SQLAlchemy schema is enough, they are not mandatory in actual table. There is no direct foreign relation between your models, so SQLAlchemy fails to find them. Specify the relation to join on explicitly:

sess.query(UnifiedProduct).join(UnifiedProduct.unifiedGroups).distinct()[:10]