Handling transactions in Python DBI module

[Israel Brewster]

I am working on implementing a Python DB API module, and am hoping I can get some help with figuring out the workflow of handling transactions. In my experience (primarily with psycopg2) the workflow goes like this:

- When you open a connection (or is it when you get a cursor? I *think* it is on opening a connection), a new transaction is started
- When you close a connection, an implicit ROLLBACK is performed
- After issuing SQL statements that modify the database, you call commit() on the CONNECTION object, not the cursor.

My primary confusion is that at least for the DB I am working on, to start/rollback/commit a transaction, you execute the appropriate SQL statement (the c library I'm using doesn't have any transactional commands, not that it should). However, to execute the statement, you need a cursor. So how is this *typically* handled? Does the connection object keep an internal cursor that it uses to manage transactions?

I'm assuming, since it is called on the connection, not the cursor, that any COMMIT/ROLLBACK commands called affect all cursors on that connection. Is that correct? Or is this DB specific?

Finally, how do other DB API modules, like psycopg2, ensure that ROLLBACK is called if the user never explicitly calls close()?

Thanks for any assistance that can be provided.
-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------