Flask-SQLAlchemy - separating reads and writes

config.py

# This is not used unless SQLALCHEMY_BINDS is not present
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://{username}:{password}@{hostname}/{database}?charset=utf8'

SQLALCHEMY_BINDS = {
  'master': 'mysql+pymysql://{username}:{password}@{hostname}/{database}?charset=utf8',
  'slave': 'mysql+pymysql://{username}:{password}@{hostname}/{database}?charset=utf8'
}

routing.py

from flask.ext.sqlalchemy import  SQLAlchemy, get_state
import sqlalchemy.orm as orm
from functools import partial

import logging

log = logging.getLogger(__name__)

class AutoRouteSession(orm.Session):

  def __init__(self, db, autocommit=False, autoflush=False, **options):
    self.app = db.get_app()
    self._model_changes = {}
    orm.Session.__init__(self, autocommit=autocommit, autoflush=autoflush,
                     extension=db.session_extensions,
                     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:
      log.info("Unable to get Flask-SQLAlchemy configuration. Outputting default bind. Error:" + err)
      return orm.Session.get_bind(self, mapper, clause)

    # If there are no binds configured, connect using the default SQLALCHEMY_DATABASE_URI
    if state is None or not self.app.config['SQLALCHEMY_BINDS']:
      if not self.app.debug:
        log.debug("Connecting -> DEFAULT. Unable to get Flask-SQLAlchemy bind configuration. Outputting default bind." )
      return orm.Session.get_bind(self, mapper, clause)

    # Writes go to the master
    elif self._flushing: # we who are about to write, salute you
      log.debug("Connecting -> MASTER")
      return state.db.get_engine(self.app, bind='master')

    # Everything else goes to the slave
    else:
      log.debug("Connecting -> SLAVE")
      return state.db.get_engine(self.app, bind='slave')

class AutoRouteSQLAlchemy(SQLAlchemy):

    def create_scoped_session(self, options=None):
      """Helper factory method that creates a scoped session."""
      if options is None:
          options = {}
      scopefunc=options.pop('scopefunc', None)
      return orm.scoped_session(
          partial(AutoRouteSession, self, **options), scopefunc=scopefunc
      )

This script is working perfectly....thanks a lot...but i am facing a problem...My update queries are going to tha slave whereas they should go to the master...i dont know how to resolve this issue..please help me out how can i acheive this

Same issue here. Tried several ways but could not yet figure out how to know an update is coming. any progress @vjangra ?

@adhorn..not yet...still working out on that...please update here u find any solution..

@vjangra and @adhorn: did you guys find a solution to the update query going to the slave bind?

@trustrachel: did you have the same problem that @vjangra and @adhorn when using this code? :)

No, I think it worked OK for us. I don't work with SQLAlchemy anymore so I can't test this, but I would take a look at the self._flushing part.

I wonder if SQLAlchemy has changed something in the API since I wrote this?

Here's the code that this is based on: https://bitbucket.org/zzzeek/sqlalchemy/src/master/lib/sqlalchemy/orm/session.py?at=master&fileviewer=file-view-default

Here is what I used. Slight modification and adding a using_bind method. Works against AWS Aurora.

https://gist.github.com/adhorn/b84dc47175259992d406

I forgot to mention you might also ping zzzeek, I know Mike is active on Twitter and StackOverflow. Probably on IRC too.

pallets-eco/flask-sqlalchemy#139

This can be done by declaring two models with the same name implementing two different db models.
We can also implement two databases with one table of the same table name.
An example can be like this:

app = Flask(__name__)
app.config['SQLALCHEMY_BINDS'] = {'rw': 'rw', 'r': 'r'}
db = SQLAlchemy(app)
db.Model_RW = db.make_declarative_base()

class A(db.Model):
    __tablename__ = 'common'
    __bind_key__ = 'rw'

class B(db.Model_RW):
    __tablename__ = 'common'
    __bind_key__ = 'r'

@andyxning

Getting the error TypeError: make_declarative_base() takes at least 2 arguments (1 given) when I use that code.

How can I integrate this with a sessionmaker (currently i pass an engine to the session manager in the begging)?

engine = SessionManager.get_engine(uri)
session = sessionmaker(bind=engine)()

I tried to make the session object like this:

db = AutoRouteSQLAlchemy()
session = db.session

but i got:

AttributeError: 'scoped_session' object has no attribute '_autoflush'

later on in the code