Using Elixir with Pylons

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

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):
        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, 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.


2 responses to “Using Elixir with Pylons

  1. I’ve noticed that people aren’t always aware of the relationship between sessions, engines, and pooled connections. Well, the relation between sessions and engines part is slightly non-straightforward only because there is more than one way to associate a session with an engine (and both can be mixed with a single session): you can bind the engine directly to the session (using create_session(bind_to=someengine)), so that all operations route through that engine; or, you can bind the engine to Tables (via BoundMetaData), which are bound to Mappers, which then get used by the Session as it operates upon individual mapped classes. The former is how Pylons does it by default; its session_context variable creates a Session which binds to an engine thats pulled from a registry within pylons.database.

    The relationship between engines and pooled connections, OTOH, is easy: the answer is, “yes”. I’ve recently clarified the docs a little more on this, i.e., the engine gets its connections from a connection pool, always ! Of course, the connection pool itself in a highly customized scenario can be a “null” pool which isnt actually pooling, but by default its an object that expects lots of concurrent users of connections (an instance of QueuePool in most cases).

    So basically, its pretty much impossible to use an SA session or engine without using pooled connections unless you very specifically stuck a non-standard pool class in there. The only thing you want to keep track of is that you are only using a single engine per database URI. Not like you can’t use more than one, but that would be wasteful since one engine is already going to pool its connections.

  2. zzzeek: thanks for clearing up that point. If anyone is interested, here’s the documentation on connection pooling:
    You can do things like set the connection timeout and size of your pool.
    Another point is that if you want to manage sessions inside your requests, middleware might be a better way to go than putting the code in your BaseController:
    WSGI middleware BTW is a really fascinating concept, I think there’s an article’s worth about it….

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s