"Israel Brewster" wrote in message
news:92d3c964-0323-46ee-b770-b89e7e7e6...@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
--
https://mail.python.org/mailman/listinfo/python-list
