sqlalchemy UnmappedColumnError when inserting into many-to-many records

Question

I have the following many to many relationship defined in my flask-sqlalchemy models:

class Course: 
    ...
    modules = db.relationship('Module', secondary=course_modules,
        primaryjoin=(course_modules.c.course_id == id),  
        secondaryjoin=(course_modules.c.module_id == id),
        backref=db.backref('courses', lazy='dynamic'), lazy='dynamic') 

and the module_course Table object defined as:

course_modules=db.Table('course_modules',
    db.Column('course_id', db.Integer, db.ForeignKey('course.id')),
    db.Column('module_id', db.Integer, db.ForeignKey('module.id'))
)

When doing the following to create a record in this table I get a sqlalchemy.orm.exc.UnmappedColumnError:

module = module_service.get_by_id(1) # returns a Module instance
course.modules = [module] # course being a Course instance
course.modules.all()

sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for  
source column 'course.id'; mapper 'Mapper|Module|module' does not map 
this column.  Try using an explicit `foreign_keys` collection which 
does not include destination column 'course_modules.module_id' 
(or use a viewonly=True relation).    

When I remove the primaryjoin and secondaryjoin lines from the db relationship and use this method I do not hit the same error and the data does get inserted.

Why is it that including primary and secondary join conditions causes insert to break? Is there an alternative method for inserting data into many-to-many relationships that I should be using?

Answer 1

In this code:

modules = db.relationship('Module', secondary=course_modules,
    primaryjoin=(course_modules.c.course_id == id),  
    secondaryjoin=(course_modules.c.module_id == id),

id in both primaryjoin and secondaryjoin refers due to scoping to Course.id. You should reference the correct id columns explicitly, e.g.:

modules = db.relationship('Module', secondary=course_modules,
    primaryjoin=(course_modules.c.course_id == Course.id),  
    secondaryjoin=(course_modules.c.module_id == Module.id),