read slave , read-write master setup

I have an example of how to do this on my blog at http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ . Basically you can enhance the Session so that it chooses from master or slave on a query-by-query basis. One potential glitch with that approach is that if you have one transaction that calls six queries, you might end up using both slaves in one request....but there we're just trying to imitate Django's feature :)

A slightly less magic approach that also establishes the scope of usage more explicitly I've used is a decorator on view callables (whatever they're called in Flask), like this:

@with_slavedef my_view(...):   # ...

with_slave would do something like this, assuming you have a Session and some engines set up:

master = create_engine("some DB")slave = create_engine("some other DB")Session = scoped_session(sessionmaker(bind=master))def with_slave(fn):    def go(*arg, **kw):        s = Session(bind=slave)        return fn(*arg, **kw)    return go

The idea is that calling Session(bind=slave) invokes the registry to get at the actual Session object for the current thread, creating it if it doesn't exist - however since we're passing an argument, scoped_session will assert that the Session we're making here is definitely brand new.

You point it at the "slave" for all subsequent SQL. Then, when the request is over, you'd ensure that your Flask app is calling Session.remove() to clear out the registry for that thread. When the registry is next used on the same thread, it will be a new Session bound back to the "master".

Or a variant, you want to use the "slave" just for that call, this is "safer" in that it restores any existing bind back to the Session:

def with_slave(fn):    def go(*arg, **kw):        s = Session()        oldbind = s.bind        s.bind = slave        try:            return fn(*arg, **kw)        finally:            s.bind = oldbind    return go

For each of these decorators you can reverse things, have the Session be bound to a "slave" where the decorator puts it on "master" for write operations. If you wanted a random slave in that case, if Flask had some kind of "request begin" event you could set it up at that point.

Or, we can try another way. Such as we can declare two different class with all the instance attributes the same but the __bind__ class attribute is different. Thus we can use rw class to do read/write and r class to do read only. :)

I think this way is more easy and reliable. :)

We declare two db models because we can have tables in two different db with the same names. This way we can also bypass the 'extend_existing' error when two models with the same __tablename__.

Here is an example:

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__ = 'r'class A(db.Model_RW):    __tablename__ = 'common'    __bind_key__ = 'rw'