Create an easy way of selecting bindings for db.session()

[stevenleeg]

As far as I'm aware, there is no easy way to switch between bindings for executing raw SQL statements:

from flask.ext.sqlalchemy import SQLAlchemy

# ...

# Set up bindings
app['SQLALCHEMY_BINDS'] = {
    "first": "...",
    "second": "..."
}

# Create the database object
db = SQLAlchemy(app)

# But which binding will this run on?
db.session.execute("SELECT * FROM table")

It's certainly possible that I'm not finding this functionality. However if it doesn't already exist, I believe some way to switch/select bindings for sessions is a pretty necessary feature:

session_first = db.get_session(bind = "first")
session_second = db.get_session(bind = "second")

or

db.session.execute(querystr, bind = "first")

Any thoughts?

[marijns]

You could use the bind or mapper argument (in case your class/mapper has a certain bind).

I'm not sure what all the different strategies are though, but what works for me is this:
db.session.execute(some_query, mapper=class_mapper(MyModel))

[fogleman]

👍 @mitsuhiko What's the correct way to do this?

[fogleman]

This works but is pretty crappy:

rows = db.session.execute(query, params, bind=db.get_engine(app, 'bind_name'))

This would be nice:

rows = db.get_session('bind_name').execute(query, params)

[fogleman]

Here's a little workaround I'm using...

class Bind(object):
    def __init__(self, bind_key):
        self.bind = db.get_engine(app, bind_key)
    def execute(self, query, params=None):
        return db.session.execute(query, params, bind=self.bind)

db.first = Bind('first')
db.second = Bind('second')

Then you can do this:

db.first.execute(...)

[franklx]

My approach was much like @fogleman 's but.
The big issue is that if the database connection is dropped (due to timeout or engine restart) the additional session got stuck (while the main .session worked).
I've written a small (hackish) class that automatically create additional sessions that behaves exactly like the main session:

import flask.ext.sqlalchemy
class SQLAlchemySessions(flask.ext.sqlalchemy.SQLAlchemy):

    def init_app(self, app):
        ret = flask.ext.sqlalchemy.SQLAlchemy.init_app(self, app)
        options = {'scopefunc': flask.ext.sqlalchemy.connection_stack.__ident_func__}
        self._sessions_ = {}
        for bn in app.config.get('SQLALCHEMY_BINDS',{}).keys():
            options['bind_key'] = bn
            self._sessions_[bn] = self.create_scoped_session(options)
            setattr(self, 's_'+bn, self._sessions_[bn])

        # Straight from flask-sqlalchemy 2.0
        if hasattr(app, 'teardown_appcontext'):
            teardown = app.teardown_appcontext
        elif hasattr(app, 'teardown_request'):
            teardown = app.teardown_request
        else:
            if app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN']:
                raise RuntimeError("Commit on teardown requires Flask >= 0.7")
            teardown = app.after_request

        @teardown
        def shutdown_sessions(response_or_exc):
            if app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN']:
                if response_or_exc is None:
                    for s in self._sessions_.values():
                        s.commit()
            for s in self._sessions_.values():
                s.remove()
            return response_or_exc

        return ret

    def create_session(self, options):
        bind_key = options.pop('bind_key', None)
        if bind_key:
            options['bind'] = self.get_engine(self.get_app(), bind_key)
        return flask.ext.sqlalchemy.SignallingSession(self, **options)

The class creates automatically s_ sessions on the object.

[FelikZ]

@franklx this should be merged

[immunda]

Right you are @FelikZ, in #249.

[FelikZ]

@immunda great, but how I can use specific bind session?

Using a class by @franklx I can do:

db.s_connection1.query(User)
db.s_connection2.query(User)

How this merge will help me to achieve the same effect?

[Cheaterman]

I just used db.get_engine(app, 'my_bind').execute(...) with great success, for anyone interested.

So, I guess we're good? :-)

[shizidushu]

See https://github.com/pallets/flask-sqlalchemy/blob/master/flask_sqlalchemy/__init__.py#L735

In the definition of SQLAlchemy class , self.session = self.create_scoped_session(session_options). We can use session = db.create_scoped_session(options = {'bind': 'my_bind'}).

If the above is the right way to get session for a specified bind key, it should be documented.

[neevai]

The suggested solutions did not work for me.
This is what I did:

import flask_sqlalchemy
.
.
.
app = flask.Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = service_a_database_uri
app.config['SQLALCHEMY_BINDS'] = { 'service_b': service_b_database_uri, 'service_c': service_c_database_uri }
.
.
.
flask_sqlalchemy.SQLAlchemy(app)
.
.
.
def create_scoped_session(app, bind_key=None):
if not bind_key:
return app.extensions["sqlalchemy"].db.create_scoped_session()
else:
return app.extensions["sqlalchemy"].db.create_scoped_session(
options={'bind': app.extensions["sqlalchemy"].connectors[bind_key]._engine})

service_a_session = create_scoped_session(app)
service_b_session = create_scoped_session(app, 'service_b')
service_c_session = create_scoped_session(app, 'service_c')