flask-sqlalchemy icon indicating copy to clipboard operation
flask-sqlalchemy copied to clipboard

Change the __bind_key__ dynamically on model before querying?

Open mxdev88 opened this issue 3 years ago • 2 comments

I need to retrieve data from several databases, same data model, different data.

class SomeModel(db.Model):
    id = db.Column(db.Integer, primary_key=True, nullable=False)
    site = db.Column(db.String)
    other = db.Column(db.String)
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/main.db'
app.config['SQLALCHEMY_BINDS'] = {
    'site1':'sqlite:////tmp/site1.db'',
    'site2':'sqlite:////tmp/site2.db'',
 }
def get_data(site):
   model = SomeModel()
   model.__bind_key__ = site
   return model.query.all()

It does not work but is there a way to achieve this?

mxdev88 avatar Aug 04 '21 19:08 mxdev88

This has been asked before in #887, it looks like the suggested solution was sharding. There was also some much earlier discussion around ShardedSession in #130.

This all seems doable, but it's a little hard to see how to fit all the pieces together. Would be awesome if anyone had an example of this!

wbobeirne avatar Oct 04 '21 20:10 wbobeirne

I think this can be closed with #456 if it gets merged, as a custom Session class would allow for the use of a ShardedSession or a custom get_bind, which depending on how exactly site is set, would fit the bill.

Zomgnomes avatar Oct 22 '21 14:10 Zomgnomes

A model's or table's bind key cannot be changed after it is defined. #1087 adds a metadata per bind, which means tables in different binds can have the same name. It also made it possible to customize the session class. If you need custom behavior such as multi-tennant, you'll need to implement your own session class to handle that.

davidism avatar Sep 18 '22 17:09 davidism