CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
Galil
Galil

Reputation: 869

Attach the same SQLAlchemy table to two models with different binds

I want to add two MySQL databases into my Flask app. Unfortunately, these database are almost identical. They have same table and column names, but different data.

I am using SQLALCHEMY_BINDS in my config.py

SQLALCHEMY_BINDS = {
    'old': 'mysql://[OLD_DB_HERE]',
    'new': 'mysql://[NEW_DB_HERE]'
}

And then in my models.py

class CallOld(db.Model):
    __bind_key__ = 'old'
    __table__ = db.Model.metadata.tables['ConferenceCall2']

class CallNew(db.Model):
    __bind_key__ = 'new'
    __table__ = db.Model.metadata.tables['ConferenceCall2']

The problem is that when I call a query for both tables I get the same results.

For example, both CallOld.query.with_entities(CallOld.TenantName.distinct()).all() and CallNew.query.with_entities(CallNew.TenantName.distinct()).all() return the same.

Interestingly, the output is always from the second of the two model classes. Apparently the second class (CallNew in that case) overwrites the first (CallOld).

How do I attach the same table definition to two models with different binds?

Upvotes: 0

Views: 1100

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 53017

You should use a mixin for this:

A common need when using declarative is to share some functionality, such as a set of common columns...

The reason why the output is always from the second (new) model's bound database is that as you manually define the __table__ for the two models Flask's declarative extensions work their black magic:

def __init__(self, name, bases, d):
    bind_key = d.pop('__bind_key__', None) or getattr(self, '__bind_key__', None)
    DeclarativeMeta.__init__(self, name, bases, d)
    if bind_key is not None and hasattr(self, '__table__'):
        self.__table__.info['bind_key'] = bind_key

As can be seen the __table__.info['bind_key'] is overwritten in each declarative class that the table is passed to.

Upvotes: 1

Related Questions