CODEWITHSUNDEEP
pythonflask-sqlalchemy
Vanojx1
Vanojx1

Reputation: 5574

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

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.

Upvotes: 1

Views: 491

Answers (1)

Wombatz
Wombatz

Reputation: 5449

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

Upvotes: 3

Related Questions