On Thu, Dec 15, 2016 at 2:07 AM, Matt <[email protected]> wrote: > > > On Wednesday, December 14, 2016 at 3:58:19 PM UTC+1, Mike Bayer wrote: >> >> >> When you want to switch between "writer" and "reader", the "reader" must >> be fine with using a totally clean transaction and that means totally >> new objects and everything inside that Session as well. For a web >> application this usually means "reader" has to be selected at the start >> of the request, and the whole request consults "reader" only. You can't >> switch between two different DB transactions mid-request, one of which >> has been writing new rows, and expect to see the same rows between those >> two transactions, the transactions will have some degree of isolation >> from each other. >> >> Short answer, you can't direct writer/reader at the "query" level, >> unless you put the Session into autocommit mode and run COMMIT after >> every statement. Normally it has to be at the transaction level. >> > > It would be acceptable to select the read server at the start of a web > request. I am still a little lost on how to get to that behavior, though. > Any insights there? How do I bind the read server at the transaction level? >
There would be an easy answer if you were creating your own sessions explicitly on each request; you would simply pass the appropriate engine when you create the session. Since you're using Flask-SQLAlchemy, you're slightly at the mercy of whatever it supports. To complicate things further, Flask-SQLAlchemy uses scoped sessions, so the actual session creation is buried even deeper. (Another potential complication from scoped sessions is that the Session instances themselves may be reused from one connection to the next, although it looks like Flask-SQLAlchemy calls Session.remove(), so that may not be a problem in this case) If you're going to stick with Flask-SQLAlchemy, I think you need to find a way to customize the session factory that is passed to the scoped session. Your custom factory would return a session bound to either the read-only or read-write engine as appropriate. Here's where Flask-SQLAlchemy creates the scoped session: https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/__init__.py#L747 It looks like you should be able to override the create_session method (which actually returns a session *factory*) to return your custom factory instead. Hope that helps, Simon -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
