There seems to be a lot of discussion about best practices on how to create connections to SQLAlchemy or Elixir engines from within Pylons. The best solution I have found is here:
from elixir import metadata, objectstore from pylons.database import make_session def connect(): session = make_session() objectstore.context.current = session metadata.connect(session.bind_to)
Bearing in mind that Elixir provides its own Session Context through objectstore.
Then, in my BaseController:
class BaseController(WSGIController): def __call__(self, environ, start_response): model.connect() return WSGIController.__call__(self, environ, start_response)
This seems to work fine. The bonus is that I can call model.connect() from anywhere in my Pylons app: in websetup.py, paster shell, even unit tests, and it automagically picks up the sqlalchemy.dburi setting in the correct configuration file (development.ini or test.ini, for example).
I gather that the above code should also take care of connection pooling, although I don’t really know for sure. If any SQLAlchemy experts have a take on this, please let me know.
Update: looking at the Pylons code, it seems that you don’t need to connect the engine to the metadata with each request. Pylons caches engine instances, so the engine is created once per application. We just need to ensure that a new session is started with each new request thread. Here is the full recipe.