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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
