A StackOverflow question/answer (sqlalchemy: create relations but without foreign key constraint in db?) looked interesting, but what if you have two databases and join through a secondary table?
FKs can't map to external databases by design, making this a more realistic case if you can't load the remote table in your database.
Below is a setup where if we didn't set the __bind_key__ and the tables shared a database, it would work. However, we do want the multiple database setup. To keep things easier both databases have a schema called api where tables are stored.
Default Database
class Store(db.Model):
'''api.stores'''
schema = 'api'
__tablename__ = 'stores'
__table_args__ = { 'schema': schema }
id = db.Column(db.Integer, primary_key=True)
product = db.relationship('Product'
secondary=Inventory.__table__,
back_populates='stores'
)
Bind Database (second_db)
class Product(db.Model):
'''second_db.api.products'''
schema = 'api'
__bind_key__ = 'second_db'
__tablename__ = 'products'
__table_args__ = { 'schema': schema }
id = db.Column(db.Integer, primary_key=True)
stores = db.relationship('Store',
secondary=Inventory.__table__,
back_populates='products'
)
class Inventory(db.Model):
'''second_db.api.inventory'''
schema = 'api'
__bind_key__ = 'second_db'
__tablename__ = 'inventory'
__table_args__ = { 'schema': schema }
product_id = db.Column(db.Integer, db.ForeignKey(f'{schema}.products.id'), primary_key=True)
# below won't work with multiple DBs as a FK cannot reference external databases
store_id = db.Column(db.Integer, db.ForeignKey(f'{schema}.stores.id'), primary_key=True)
Per the accepted answer it seems primaryjoin may be used to build out the association and its answer doesn't use a secondary, so I'm not quite sure where something like this would live in the example above.
a = relationship('A',
foreign_keys=[a_id],
primaryjoin='A.a_id == C.a_id'
)
Another answer (https://stackoverflow.com/a/56683671/10408280) demonstrates how one might reference the bind database, however what is the key to the default app database/engine set by SQLALCHEMY_DATABASE_URI?
id = db.Column(db.Integer, primary_key=True)
people_id = db.Column(db.Integer, db.ForeignKey('db1.people.id'))
dogs_id = db.Column(db.Integer, db.ForeignKey('db2.dogs.id'))