On Jun 20, 2013, at 2:02 PM, Jonathan Vanasco <jvana...@gmail.com> wrote:
> Mike blew my mind at the "intro to sqlalchemy" presentation this week. I > learned at least 5 things that I had missed. I still can't believe that the > connection itself is basically "lazy-loaded" and SqlAlchemy doesn't even > connect to the DB until you do something. I wrote a bunch of code to memoize > connections ( duplicating this ) - expecting there to be some overhead > per-request (even unused) like other libraries. Thanks, Mike! > > So this brings up my question , which I couldn't find in the docs or FAQ > > Is there any best-practices you can recommend for using SqlAlchemy on web > projects in general ? fairly recently i wrote up as much as I could come up with on this, which you can see first in the Session FAQ: http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions and then regarding scoped_session in: http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications > > # Application Startup > > 1 Create a global registry for engines ( ie: I have separate read & write > connections for postgresql ) > 2 For each desired engine : > * create a SqlAlchemy engine ( via `sqlalchemy.engine_from_config` ) > * save it to the registry: > ** wrap it into an container object that has: > 1. the engine > 2. `sqlalchemy_orm.sessionmaker` > 3. `sqlalchemy_orm.scoped_session` > * setup the table metadata for the engine I don't generally bother trying to make a "container" for all these things, usually they're defined in some module like "myapp.meta". A module in Python is a namespaced object like any other, so there's no need for a MyApplication kind of class. Everyone seems to make those. Also the "sessionmaker" is an element of the scoped_session so I don't have it as it's own name; if I really want to make a new Session that isn't part of the usual scoped context I'll say my_scoped_session.session_factory(). > > # Application PageView > > 1. [optional] grab the engine's `scoped_session` and do stuff I have the ScopedSession as the facade to the database - the Engine itself is an element of the session and similarly I don't refer to it anywhere once it's been set up and assigned to a session. If I want it, I say my_scoped_session.bind. If I'm binding multiple engines to a single session maybe I'd have a lookup for those cases where i need it. My current app has two databases that I refer to individually, so I have MySessionOne and MySessionTwo (better names in the real app). > 2. cleanup routine - call `scoped_session.close()` on every engine in the > registry yes. Though I'll use remove() so that the current Session is discarded entirely. I use patterns where I attach extra things to a Session as the request proceeds. > > 1. Should I be creating a new `scoped_session` on every request ( or first > usage per request ) ? Just use the scoped_session as needed there's no setup to be done, the close() beforehand makes sure its clean for the next request. > 2. Should I be calling `scoped_session.close()` at the start of every request > as well no need, sessions should always be clean when a request ends, that's when that thread/process/wahtever is about to go dormant and you don't want any garbage lying around. > 3. Should there be anything in place to ensure that any work with a > transaction via sqlalchemy's core would be `new` ( or would calls to > `scoped_session.close` handle this as well ? I'm not using any sqlalchemy > core in page requests (though i do use it in maintenance scripts) -- this is > purely theoretical Core API should be through Session.execute() in any case so the Session would have you covered. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
