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

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

from functools import partialfrom sqlalchemy import orm
from flask import current_app
from flask_sqlalchemy import SQLAlchemy, get_stateclass RoutingSession(orm.Session):def __init__(self, db, autocommit=False, autoflush=True, **options):self.app = db.get_app()self.db = dbself._bind_name = Noneorm.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_URIif not state or not self.app.config['SQLALCHEMY_BINDS']:return orm.Session.get_bind(self, mapper, clause)# if want to user exact bindif self._bind_name:return state.db.get_engine(self.app, bind=self._bind_name)else:# if no bind is used connect to defaultreturn 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 = namereturn bind_sessionclass 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 SQLAlchemyapp = 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()