Flask SQLAlchemy dynamic connection to different databases

Question

I am using Python 3.6 with Flask and Flask-SQLAlchemy, and I have a huge data repository that I have decided to partition along countries. Each database holds data for one country only - however, the data schema is the same, and all the queries etc, are the same.

I need to decide, at the URL endpoint, which database to run the query on. At the moment, there appears to be no clean cut way of doing this, and the BIND functionality provided by Flask-SQLAlchemy, does not seem to fit the bill, so I have decided (rather grudgingly, to "roll my own").

This is what I have done so far:

version info

Flask==1.1.2
Flask-Migrate==2.5.3
Flask-SQLAlchemy==2.4.4 SQLAlchemy==1.3.19

code

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

my_pool = {}

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username


@app.route('/foo/<id_param>')
def check_this_out(id_param):
    the_engine_options = grok(foo)
    if the_engine_options not in my_pool:
        the_engine = create_engine(the_engine_options) 
        my_pool[the_engine_options] = the_engine

    db.engine = my_pool[the_engine_options]

    return f"{User.query(id=id_param)}"

There is the obvious problem of "leakage", since I am not freeing the connections - is there an equivalent of a "destructor" for the Flask class, where I can free the connections created?

My main question is, can I dynamically change the SQL engine at runtime, without any horrible gotchas/side effects?

the next question is how to cleanly free the created engines in the engine pool, so I don't leak resources.

[[ Clarification about the FSQLA BIND ]]

The reason why the FSQLA BIND functionality doesn't work in my use case scenario:

From the documentation:

Example Configuration The following configuration declares three database connections. The special default one as well as two others named users (for the users) and appmeta (which connects to a sqlite database for read only access to some data the application provides internally):

SQLALCHEMY_DATABASE_URI = 'postgres://localhost/main' SQLALCHEMY_BINDS = { 'users': 'mysqldb://localhost/users', 'appmeta': 'sqlite:////path/to/appmeta.db' }

One reason why this doesn't work for me is that I have no "default" database - each database is equally important.

The main reason however, is in the model declaration/definition below:

class User(db.Model):
    __bind_key__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)

The bind_key is defined as:

Optionally declares the bind to use. None refers to the default bind. For more information see Multiple Databases with Binds.

This means that if the __bind_key__ is not specified, the default database will be used. However, if is specified, then it is a static binding to a specific database.

In my use case, it would have me repeating the same code 154 times (154 countries that I'm monitoring) - definitely not DRY, and prone to errors - if even I automate the code generation.

This is not what I want. I want to be able to use the same codebase, and dynamically be able to switch databases depending on query parameters.

and the BIND functionality provided by Flask-SQLAlchemy, does not seem to fit the bill -- Why? I haven't used it, but from what I gather BIND is exactly what you need? — Kraay89
– Kraay89, Commented Sep 10, 2020 at 10:05
@Kraay89 I have added a section that addresses your question about the FSQLA BIND functionality. — Homunculus Reticulli
– Homunculus Reticulli, Commented Sep 10, 2020 at 10:24
I evidently hadn't thought through my comment. Thanks for the elaboration though. It's way above my paygrade to be of any help to you i'm afraid. — Kraay89
– Kraay89, Commented Sep 10, 2020 at 11:06
Does this answer your question? SQLAlchemy with multiple binds - Dynamically choose bind to query — aaron
– aaron, Commented Jan 31, 2022 at 18:44

Zach · Accepted Answer · 2022-01-31 16:34:37Z

This can be done by overriding Flask-SQLAlchemys SignallingSession to use a different bind_key, depending on your request:

from flask import Flask, request
from flask_sqlalchemy import SQLAlchemy, SignallingSession, get_state


class DynamicSession(SignallingSession):
    def get_bind(self, mapper=None, clause=None):
        # note that this will fail, if used outside of a request context
        bind = request.args.get('country')

        if bind in self.app.config['SQLALCHEMY_BINDS']:
            # copied from SignallingSession.get_bind
            state = get_state(self.app)
            return state.db.get_engine(self.app, bind=bind)
        return super().get_bind(mapper, clause)


class DynamicSQLAlchemy(SQLAlchemy):
    def create_session(self, options):
        return orm.sessionmaker(class_=DynamicSession, db=self, **options)


app = Flask(__name__)
# this would be the default database
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
# create a pool for each of your databases
app.config['SQLALCHEMY_POOLS'] = {
    'england': 'sqlite:////tmp/england.db', 
    'germany': 'sqlite:////tmp/germany.db'
}
db = DynamicSQLAlchemy(app)

Collectives™ on Stack Overflow

Flask SQLAlchemy dynamic connection to different databases

version info

code

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

version info

code

1 Answer 1

Comments

Linked

Related