How to use multiple Sessions in a pylons app?

Question

I've read "Multiple database connections with Python + Pylons + SQLAlchemy" and I get how to create multiple engines using that technique, but now I'm looking for advice on how to handle the creation of Sessions for these engines. Right now, the Session in my project is defined as per Pylons convention: myapp.model.meta.Session = scoped_session(sessionmaker()) and imported as myapp.model.Session. This works fine if there's only one engine.

What's a good, maintainable approach to defining this for multiple engines? The set of engines may change at runtime, for what it's worth, so I'd need the ability to create new Sessions on the fly, without hard coding them into the model.

Answer 1

Use the bind parameter to sessionmaker to bind the session to a specific connection.

See http://www.sqlalchemy.org/docs/05/reference/orm/sessions.html#sqlalchemy.orm.sessionmaker for more information.

Answer 2

If you want choose the database backend per one request, a good option is to call meta.Session(bind=get_engine_for_this_request()) as the first thing. That will create the session with the specified parameters. You can stick that into the BaseController if it makes sense in your case.

For multiple backends per one request the best approach depends on your specific usecase. If all the backends have the same schema, it's probably best to create multiple ScopedSessions, one for each backend. When they hold different schemas, you can create multiple MetaData objects (or Base classes in case of declarative) and bind them to the engines.