On Feb 20, 2011, at 3:31 PM, darkporter wrote: > I'm not sure what the "right" setting for autocommit is. I'd prefer to > leave it off, because database updates make more sense to me with > session.commit() calls. Modify anything locally you want to change, > then call commit(): > > some_model.name = "John" > some_model.zip = "11201" > session.commit()
"off" is closest to what would be considered "right", unless you had a real need for "on". > > The problem is with queries. If I have this code in multiple places: > > user = Session().query(User).get(userid) > > My understanding is each call leaves a database connection hanging > around forever. So I have bunch of this in my code: > > session = Session() > user = session.query(User).get(userid) > session.close() Why not open and close a session at a fixed boundary ? If this is a web app, open at request start, close at request end. If a short running script, open at script start, end at script end. If a long running script that runs some series of "jobs" or tasks, open at start of job, close at end of job. If a GUI app, that's the one place where opening and closing might be a little weird, I'd probably use a decorator around methods that handle GUI events - initial event, open session, dig into method and render GUI, close session. Basically, doing it the way you have above, opening and closing a session everywhere you need to query, is not sufficiently abstracted. > > I also understand (maybe I'm wrong?) that using autocommit=True would > fix that, so I could just query away and not have to close sessions. This is also true. But again, very few applications consist of a series of entirely unconnected locations that need to call exactly one query. As soon as you need two queries and not one, using autocommit=True begins to become slightly wasteful at best (two connection pool gets, rollbacks), and incorrect at worst (not maintaining tranasaction consistency along several related queries). > But with autocommit=True my first example would need a flush() instead > of a commit() to save. Or I could do a session.begin(), but for single > attribute updates that's a waste. Except the way DBAPI works, there is always a transaction. The BEGIN is happening no matter what. See "commit" in http://www.python.org/dev/peps/pep-0249/ which describes "any auto-commit provided by the DBAPI must be off initially". DBAPI-level autocommit features are scattered and inconsistent so SQLAlchemy doesn't use them. > > It's all so confusing, it's like there's two different sets of > documentation for autocommit being on or off. That doesn't strike me as a fair statement. The vast majority of SQLAlchemy ORM documentation assumes the default setting of autocommit=False. autocommit=True is generally for usage by applications that want to have explicit control over when Session.begin() is called, which means the usage for those systems also works 99% like an autocommit=False application. > > What would be ideal is if I could just query without having to close > sessions, perhaps with a class method on Session? This is asking SQLAlchemy to decide when transactions should begin and end. Which it can do - set autocommit=True, and use Session.flush(). You'll get no transactional consistency between successive operations, autoflush operations will commit new database data at any time (unless you turn it off). Though I would recommend in favor of your application having some structure which provides a predictable and consistent transaction boundary (for web applications, it should be this one: http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session ) > There isn't one > unfortunately (I'm not using scoped_session, just regular session.) > What if there was a: > > user = Session.query(User).get(userid) > > That cleans up after itself. Then I could have autocommit=False and > all would be well. How do you define "cleans up after itself" ? I would define that as "autocommit=True". Not really sure what else you'd have in mind. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
