Flask SQLAlchemy setup dynamic URI

Question

I have a Flask app served up under WSGI where the database URI changes over time. Every two hours the URI toggles to another database. I'm using that time to fill up one database while the other is serving up data for the app.

I'm having a hard time figuring out how best to configure the session so that when the switchover happens, the clients will get the correct (different) database on their next request. From my testing, if I initialize the database at the top level, when the switchover happens, the clients are still left pointing to the old database.

I thought about setting up the session inside the pages (index, etc) themselves, but what a pain, and then I worry about opening and closing too many database connections and leaving them lying around. I think I could probably make it work by initializing both sessions at startup, and then just choosing which to use at the switchover inside each page. It seems inefficient and I'm sure there is a better way.

Help?!

~~~~~~~~~

This a general idea of what I'm currently doing, but there is no way to change the URI between requests. The top level code is run only once, or every so often.

if now.hour % 2:
    db_name = 'db1'
else:
    db_name = 'db2'

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql://foo:poo@localhost:3306/%s" % db_name

def init_db(uri, **kwargs):
    engine = create_engine(uri, **kwargs)

    Base.metadata.create_all(bind=engine)

    global db_session
    db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
    Base.query = db_session.query_property()


init_db(app.config['SQLALCHEMY_DATABASE_URI'], pool_recycle=3600)

@app.teardown_request
def shutdown_session(exception=None):
    db_session.remove()

@app.route('/')
def index():
    ...etc...

Working example - Beautiful.

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql://foo:poo@localhost:3306/%s"

class SessionManager(object):
    def __init__(self, base_uri=None, **kwargs):
        self.session = None
        self.base_uri = base_uri
        self.kwargs = kwargs

    def get_session(self):
        if now.hour % 2:
            db_name = 'db1'
        else:
            db_name = 'db2'

        if self.session:
            if self.session.name == db_name:
                return self.session
            else:
                self.session.remove()
                self.session = None

        if not self.session:
            engine = create_engine(self.base_uri % db_name, **self.kwargs)
            db_session = scoped_session(sessionmaker(bind=engine))
            db_session.name = db_name
            self.session = db_session
        return db_session

session_manager = SessionManager(base_uri=app.config['SQLALCHEMY_DATABASE_URI'], pool_recycle=3600)

db_session = LocalProxy(session_manager.get_session)

Answer 1

You can make a custom builder for the session that will re-create the engine and scoped session when your rules dictate it. Something like

class SessionManager(object):

    def __init__(self):
        self.session = None

    def get_session(self):
        # return existing session or make a new engine and scoped_session

To make this class transparent, use Werkzeug's LocalProxy. The code that uses the sessions won't have to change at all.

session_manager = SessionManager()
db_session = LocalProxy(session_manager.get_session)