Handling transactions in Python DBI module

[Frank Millman]

"Israel Brewster"  wrote in message 
news:92D3C964-0323-46EE-B770-B89E7E7E6D36 at ravnalaska.net...

> 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()?

Rather than try to answer your questions point-by-point, I will describe the 
results of some investigations I carried out into this subject a while ago.

I currently support 3 databases, so I use 3 DB API modules - 
PostgreSQL/psycopg2, Sql Server/pyodbc, and sqlite3/sqlite3. The following 
applies specifically to psycopg2, but I applied the lessons learned to the 
other 2 as well, and have had no issues.

A connection has 2 possible states - 'in transaction', or 'not in 
transaction'. When you create the connection it starts off as 'not'.

When you call cur.execute(), it checks to see what state it is in. If the 
state is 'not', it silently issues a 'BEGIN TRANSACTION' before executing 
your statement. This applies for SELECT as well as other statements.

All subsequent statements form part of the transaction, until you issue 
either conn.commit() or conn.rollback(). This performs the required action, 
and resets the state to 'not'.

I learned the hard way that it is important to use conn.commit() and not 
cur.execute('commit'). Both succeed in committing, but the second does not 
reset the state, therefore the next statement does not trigger a 'BEGIN', 
with possible unfortunate side-effects.

HTH

Frank Millman