Using the same table name with different relationships in Flask

Question

I have two tables that have the same name but are in different databases:

class Lcn(db.Model): __tablename__ = 'lcn' class LcnRemote(db.Model): __bind_key__ = 'remote' __tablename__ = 'lcn' 

SQLAlchemy doesn't seem to like this. It says:

sqlalchemy.exc.InvalidRequestError: table 'lcn' is already defined for this MetaData instance. Specify extend_existing = True to override the parameters and columns in the existing Table object.

Is there a way to solve this problem without renaming one of my tables?

Answer 1

Use separate declarative base classes for different databases with the same name to prevent SQLAlchemy metadata sharing. You will need to create two instances of flask_sqlalchemy.SQLAlchemy() :

 app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/database1.db' app.config['SQLALCHEMY_BINDS'] = {'remote': 'sqlite:////tmp/database1.db'} db1 = SQLAlchemy(app) class Lcn(db1.Model): __tablename__ = 'lcn' db2 = SQLAlchemy(app) class LcnRemote(db2.Model): __bind_key__ = 'remote' __tablename__ = 'lcn' 

This is a limitation of Flask-SQLAlchemy, it should really allow you to create declarative databases for each binding. The way the SQLAlchemy() class is currently being developed is limiting it to only one such base; it proxies various SQLAlchemy metadata calls through the db.Model class, which it generates from the very beginning. By creating two instances of flask_sqlalchemy.SQLAlchemy() , you will get around this problem.