Handling transactions in Python DBI module

[dieter]

Israel Brewster <israel at ravnalaska.net> writes:

> 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

All databases I have seen so far associate transaction control
with the connection, not with the cursor -- this is important, as
for some applications you need several independent cursors at the same time
which nevertheless must belong to the same transaction.

Your cursor api may give you "commit/rollback" -- but only as a
convenience; those operations operate on the "connection", 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?

When you open a connection, it is in an "inital mode".

I have seen as "initial mode":

  *  "no transaction mode": a transaction is automatically started when
     an SQL command is executed

  *  "auto commit mode": each SQL command is run in its own transaction;
     Use "BEGIN" to enter explicite transaction control.