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

Closed
stevenleeg opened this Issue Nov 16, 2012 · 9 comments

Comments

Projects
None yet
7 participants

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 commented Nov 22, 2012

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))

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

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)

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 commented Dec 12, 2014

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 commented Jul 29, 2015

@franklx this should be merged

Collaborator

immunda commented Jul 29, 2015

Right you are @FelikZ, in #249.

@immunda immunda closed this Jul 29, 2015

FelikZ commented Jul 29, 2015

@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?

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

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

@JamesGardiner JamesGardiner referenced this issue in ONSdigital/ras-secure-message Feb 13, 2018

Merged

Feature/support multiple internal users #140

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment