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 ? For this purpose , l'll just define web projects as an application that has: 1. a startup phase 2. a unit-of-work phase that corresponds ( at most ) to a single page viewing (request/generation/whatever). right now, my implementation is gleaned from what other webapps are doing... but there doesn't seem to be anything official in the SqlAlchemy docs/FAQ saying what people *should* do. # 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 # Application PageView 1. [optional] grab the engine's `scoped_session` and do stuff 2. cleanup routine - call `scoped_session.close()` on every engine in the registry # Concerns This seems to work fine for 2 reasons ( all thanks to SqlAlchemy ): 1. SqlAlchemy is smart - the cleanup call to `scoped_session.close()` only does work when we actually used that session. i can call close on 4 sessions, and we're never hitting the db 2. my call to `scoped_session.close()` in cleanup clears out the sessions for the new request. yay. I kind of feel like this pattern works, but it "just barely works" and largely by luck. The concerns that I had are: 1. Should I be creating a new `scoped_session` on every request ( or first usage per request ) ? 2. Should I be calling `scoped_session.close()` at the start of every request as well [ `new` appears to mark the work as new, but I want to clear out the entire unit-of-work ] ? 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 A few other things popped in my head, but these seem to be the most pressing. -- 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.