How to specify PostgreSQL schema in SQLAlchemy / foreign key mixin column?
The existing PostgreSQL database has tables organized in different "schemas" to partition a large database (both for scalability and for fine-tuned server-level security). Likewise, declarative_base descriptions are organized into different files in the package - one file for each schema:
package
__init__.py
tables_in_schema1.py
tables_in_schema2.py
The metadata and engine object goes into each file from the top of the package as db.Model
. For example table_in_schema1.py will have (ignoring the required ORM imports and then the required ones for backreferences) an alpha table :
from package import db
class TableAlpha(db.Model, object):
__tablename__ = "alpha"
__table_args__ = ({"schema": "schema1"})
id_alpha = Column(INTEGER, Sequence("pk_alpha", 1, 1), primary_key=True)
class MixinAlphaRelation(object):
@declared_attr
def id_alpha(cls):
return Column(INTEGER, ForeignKey("schema1.alpha.id_alpha"))
There are now two tables defined in tables_in_schema2.py. One is a separate table called beta and the other is a junction table for a one-to-many relationship between alpha and beta to call the rho table :
from package import db
from package.tables_in_schema1 import MixinAlphaRelation
class TableBeta(db.Model, object):
__tablename__ = "beta"
__table_args__ = ({"schema": "schema2"})
id_beta = Column(INTEGER, Sequence("pk_beta", 1, 1), primary_key=True)
class MixinBetaRelation(object):
@declared_attr
def id_beta(cls):
return Column(INTEGER, ForeignKey("schema2.beta.id_beta"))
class TableRho(db.Model, MixinAlphaRelation, MixinBetaRelation):
__tablename__ = "rho"
__table_args__ = (
UniqueConstraint("id_alpha", "id_beta", name="uq_rho_alpha-beta"),
{"schema": "schema2"})
id_row = Column(INTEGER, Sequence("pk_rho", 1, 1), primary_key=True)
The goal of inheriting the rho table of both mixins is to create a table with three rows (and reuse the mixin for other tables that also reference either alpha or beta):
CREATE TABLE schema2.rho (
id_row INTEGER PRIMARY KEY,
id_alpha INTEGER REFERENCES schema1.alpha(id_alpha),
id_beta INTEGER REFERENCES schema2.beta(id_beta)
);
CREATE INDEX uq_rho_alpha-beta ON schema2.rho(id_alpha, id_beta);
However, trying to recreate all these tables by calling db.create_all()
SQLAlchemy will throw an error:
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'rho.id_alpha' could not find table 'schema2.alpha' which generates foreign key for target column "id_alpha"
It looks like instead of finding the alpha table in schema1 as indicated in the imported mixin, SQLAlchemy seems to look for it in schema2 .
How can this be solved? Is there a way to pass / force the correct schema for the mixin? Thank.
source to share
Finally, I found an error - a typo or an extra underscore in the beta declaration strong>: instead of the correct __tablename__
one I had __table_name__
. Without a specified class member, __tablename__
SQLAlchemy will create a table on the server using the class name as the table name (default behavior). In my case, it created a TableBeta table instead of the supposed beta strong>. This caused foreign key-mix not to find the table.
The approach I have taken (detailed in my question) is the correct way to use mixins and specify the schema in SQLAlchemy to use (tables in different schemas and table class declarations in different model files). The schema name is specified in table declarations using the class member __table_args__
passed in with the dictionary keyword {"schema": "schema_name"}
. The schema name is qualified in mixin / column declarations on the form "schema_name.table_name.column_name"
.
source to share