Using same name of tables with different binds in Flask

Question

I have two tables sharing the same name but located in different databases:

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

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

It seems SQLAlchemy doesn't like that. It says:

sqlalchemy.exc.InvalidRequestError: Table 'lcn' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

Is there a way to solve this without having to rename one of my tables?

Answer 1

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

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 really should allow you to create declarative bases per bind. The way the SQLAlchemy() class currently is designed limits it to just one such base; it proxies various SQLAlchemy metadata calls through the db.Model class it generates at the start. By creating two instances of flask_sqlalchemy.SQLAlchemy() you work around this issue.