Skip to content

Instantly share code, notes, and snippets.

@adhorn adhorn/ Secret
Created Oct 9, 2015

What would you like to do?
Flask-SQLAlchemy - separating reads and writes - Tested on AWS Aurora
from flask.ext.sqlalchemy import SQLAlchemy, get_state
import sqlalchemy.orm as orm
from functools import partial
from flask import current_app
class RoutingSession(orm.Session):
def __init__(self, db, autocommit=False, autoflush=False, **options): = db.get_app()
self.db = db
self._model_changes = {}
self, autocommit=autocommit, autoflush=autoflush,
binds=db.get_binds(, **options)
def get_bind(self, mapper=None, clause=None):
state = get_state(
except (AssertionError, AttributeError, TypeError) as err:
"cant get configuration. default bind. Error:" + err)
return orm.Session.get_bind(self, mapper, clause)
If there are no binds configured, connect using the default
if state is None or not['SQLALCHEMY_BINDS']:
if not"Connecting -> DEFAULT")
return orm.Session.get_bind(self, mapper, clause)
elif self._name:"Connecting -> {}".format(self._name))
return state.db.get_engine(, bind=self._name)
# Writes go to the master
elif self._flushing: # we who are about to write, salute you"Connecting -> MASTER")
return state.db.get_engine(, bind='master')
# Everything else goes to the slave
else:"Connecting -> SLAVE")
return state.db.get_engine(, bind='slave')
_name = None
def using_bind(self, name):
s = RoutingSession(self.db)
s._name = name
return s
class RouteSQLAlchemy(SQLAlchemy):
def __init__(self, *args, **kwargs):
SQLAlchemy.__init__(self, *args, **kwargs)
self.session.using_bind = lambda s: self.session().using_bind(s)
def create_scoped_session(self, options=None):
if options is None:
options = {}
scopefunc = options.pop('scopefunc', None)
return orm.scoped_session(
partial(RoutingSession, self, **options), scopefunc=scopefunc
db = RouteSQLAlchemy()
'master': 'mysql://foobardbdbuser:foobar123@',
'slave': 'mysql://foobardbdbuser:foobar123@'
# the read/write bind interface fails to intercept update function
# to be executed on the master - now it is forced upon the master.
FooModel).filter( == id).update(
FooModel.count: FooModel.count + 1,
FooModel.updated: _foobar_timestamp
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.