How to separate Master Slave (DB read / writes) in Flask Sqlalchemy

Question

I'm trying to separate the Read and write DB operations via Flask Sqlalchemy. I'm using binds to connect to the mysql databases. I would want to perform the write operation in Master and Reads from slaves. There does not seem to be an in built way to handle this.

I'm new to python and was surprised that a much needed functionality like this is not pre-built into flask-sqlalchemy already. Any help is appreciated. Thanks

Corie

The author of SQL-Alchemy has written a post on how to achieve a master-slave setup here techspot.zzzeek.org/2012/01/11/…. – Priceless

Answer 1

There is no official support, but you can customize Flask-SQLalchemy session to use master slave connects

from functools import partial

from sqlalchemy import orm
from flask import current_app
from flask_sqlalchemy import SQLAlchemy, get_state


class RoutingSession(orm.Session):
    def __init__(self, db, autocommit=False, autoflush=True, **options):
        self.app = db.get_app()
        self.db = db
        self._bind_name = None
        orm.Session.__init__(
            self, autocommit=autocommit, autoflush=autoflush,
            bind=db.engine,
            binds=db.get_binds(self.app),
            **options,
        )

    def get_bind(self, mapper=None, clause=None):
        try:
            state = get_state(self.app)
        except (AssertionError, AttributeError, TypeError) as err:
            current_app.logger.info(
                'cant get configuration. default bind. Error:' + err)
            return orm.Session.get_bind(self, mapper, clause)

        # If there are no binds configured, use default SQLALCHEMY_DATABASE_URI
        if not state or not self.app.config['SQLALCHEMY_BINDS']:
            return orm.Session.get_bind(self, mapper, clause)

        # if want to user exact bind
        if self._bind_name:
            return state.db.get_engine(self.app, bind=self._bind_name)
        else:
            # if no bind is used connect to default
            return orm.Session.get_bind(self, mapper, clause)

    def using_bind(self, name):
        bind_session = RoutingSession(self.db)
        vars(bind_session).update(vars(self))
        bind_session._bind_name = name
        return bind_session


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

Than the default session will be master, when you want to select from slave you can call it directly, here the examples:

In your app:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql:///master'
app.config['SQLALCHEMY_BINDS'] = {
    'slave': 'postgresql:///slave'
}

db = RouteSQLAlchemy(app)

Select from master

session.query(User).filter_by(id=1).first() 

Select from slave

session.using_bind('slave').query(User).filter_by(id=1).first() 

Collettecolletti

Really nice approach guys! just one suggestion, I would remove autocommit=False, autoflush=False from the RoutingSession.__init__() method as they define different default values than the orm.Session.__init__() method (at least on the latest FlaskAlchemy release). It took me some time to find out why things were not working as expected on my app, and that was the reason. – Sublimity

@Sublimity thank you for your suggestion, fixed autoflush default value – Collettecolletti

Answer 2

Here is the documentation http://packages.python.org/Flask-SQLAlchemy/binds.html

Celestyn

This is simple binds. There is no way I can mention to flash-sqlalchemy ORM to use separate bind for reads and writes. I will probably have to write a wrapper for Model class and handle it there. – Corie

@Corie Is this resolved for you ? Facing the same problem. – Vizard