pythonflasksqlalchemyflask-sqlalchemy

Using same name of tables with different binds in Flask

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?

Solution

  • Use separate declarative base classes for different databases with the same name, to prevent sharing of SQLAlchemy metadata.

    If you are using a Flask-SQLAlchemy version older than 3.0.0 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 < 3.0.0, it really should allow you to create declarative bases per bind. The way the SQLAlchemy() class in older versions 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.

    Version 3.0.0 allows you to use separate bases per bind key and your classes work out of the box. See the documentation on using binds.