On Jan 9, 2009, at 10:20 PM, Randall Smith wrote:
> > After thinking about it some more, I think that the main issue is a > lack > of auto-commit in the DBAPI. Some drivers implement it and some > don't. > Though it's usually a good idea to use transactions, there are some > times for real auto-commit. theres a wide degree of variability in the presence and behavior of transactions. if you use MySQL MyISAM, theres no transaction at all. If you're on Oracle, there's always a transaction. Though I agree a DBAPI level autocommit setting would have been nice. > For example, I'm working on a thick client query tool in which a > connection might be held open for days. There is an auto-commit > mode as > well as a transaction mode. In auto-commit mode, changes in the > database from any session should be visible immediately. You just need to ensure you clear any transactional state on the connection before using it, or after you're done with it. the SQLA connection pool accomplishes this nicely. > The issue is really just when using SA as a high level DBAPI. Because > transactions require explicit calls (e.g. trans = conn.begin()), I > think > that most people would imply that no transaction is in effect without > this explicit call and would be surprised to discover there is one. Its an interesting idea that the Connection object would be always "transactional" in nature, we wouldn't have the Transaction interface as it stands now and you'd just be saying Connection.commit()/ rollback() the same way as a DBAPI connection. At the time I created it i wanted to restore the concept of a "begin()" to the interface which DBAPI had removed. But really all begin() says is "turn the autocommit mode off". So we did decide to change the semantics of a DBAPI connection a bit here, but with regards to the original design, i.e. there was no Connection, just Engine which returned connections to the pool after each use, it worked out fairly well. So I guess there's some history to this. Explicit connection usage is not very common in any case since theres usually not much need for it, unless you want to control the transaction explicitly. Rethinking the interface of Connection at this level is always an option but we'd need a seriously good reason to do so since the transactional story of the library is one of the most critical things an application is constructed around...I'd worry that a change in that story would potentially throw away a lot of the smooth sailing we've had for a couple of years. > > Maybe you didn't intend for it to be used like I'm using it, but I > must > say that for the most part, it does a great job of creating a standard > interface for the various drivers. Also, I like ResultProxy, > reflecttable and other goodies. well I guess the "intent" part here is that you're keeping a connection checked out of the pool for a long time. that is OK as long as you do a rollback() on it after each period of usage. Seems like it would be a lot easier to just use the pool though, "open" the connection when you need it, "close" it when youre done. you also get the benefit of pool_recycle if thats needed. > Curious. Why would you choose not to use the driver's autocommit > functionality if available? For example, with psycopg2, you could do: > > con.set_isolation_level(0) if we started supporting that, it actually adds more code to SQLA since our current autocommit mechanism has to become conditional, we'd also need extra communication with the connection to "turn autocommit off" when returned to the pool, etc. The current autocommit is generic and behaves consistently across all backends, with almost no backend-specific code. if we start using the autocommit functionality of those drivers which provide it (i think sqlite has one too), it would potentially introduce behavioral variability into the equation, such as what kinds of statements COMMIT is issued for, issues which may be specific to certain versions of the DBAPI, etc. Again this is a question of what benefit we would get. If DBAPI did provide autocommit() at the generic level, that would make it easier since we could code to that feature exclusively. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
