Python & SQLAlchemy many-to-many relation error after specify schema name

Question

Here the code:

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

AvailableModules = db.Table('available_modules',
    db.Column('customer_id', db.Integer, db.ForeignKey('customers.id')),
    db.Column('module_id', db.Integer, db.ForeignKey('modules.id')),
    schema='tcloud_admin')

class Customer(db.Model):
    __tablename__ = 'customers'
    __table_args__ = {"schema":"tcloud_admin"}
    id = db.Column(db.Integer, primary_key=True)
    ....
    modules = db.relationship('Module', secondary=AvailableModules)

class Module(db.Model):
    __tablename__ = 'modules'
    __table_args__ = {"schema":"tcloud_admin"}
    id = db.Column(db.Integer, primary_key=True)
    ....

Trying the above script gives me this error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'available_modules.customer_id' could not find table 'customers' with which to generate a foreign key to target column 'id'

Without specifying the schema name everything works fine.

Answer 1

When using a custom schema name all foreign keys that reference a table or column in that schema must explicitly contain that schema name too.

Your ForeignKey('customers.id') references a different table customers which is not inside your custom schema. As this table does not exist at all you get the corresponding error.

To fix the error change the foreign keys:

db.ForeignKey('tcloud_admin.customers.id')
db.ForeignKey('tcloud_admin.modules.id')

This is also explained in the docs