How do I do database transactions with psycopg2/python db api?

Question

Im fiddling with psycopg2 , and while there's a .commit() and .rollback() there's no .begin() or similar to start a transaction , or so it seems ? I'd expect to be able to do

db.begin() # possible even set the isolation level here
curs = db.cursor()
cursor.execute('select etc... for update')
...
cursor.execute('update ... etc.')
db.commit();

So, how do transactions work with psycopg2 ? How would I set/change the isolation level ?

Answer 1

Use db.set_isolation_level(n), assuming db is your connection object. As Federico wrote here, the meaning of n is:

0 -> autocommit
1 -> read committed
2 -> serialized (but not officially supported by pg)
3 -> serialized

As documented here, psycopg2.extensions gives you symbolic constants for the purpose:

Setting transaction isolation levels
====================================

psycopg2 connection objects hold informations about the PostgreSQL `transaction
isolation level`_.  The current transaction level can be read from the
`.isolation_level` attribute.  The default isolation level is ``READ
COMMITTED``.  A different isolation level con be set through the
`.set_isolation_level()` method.  The level can be set to one of the following
constants, defined in `psycopg2.extensions`:

`ISOLATION_LEVEL_AUTOCOMMIT`
    No transaction is started when command are issued and no
    `.commit()`/`.rollback()` is required.  Some PostgreSQL command such as
    ``CREATE DATABASE`` can't run into a transaction: to run such command use
    `.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)`.

`ISOLATION_LEVEL_READ_COMMITTED`
    This is the default value.  A new transaction is started at the first
    `.execute()` command on a cursor and at each new `.execute()` after a
    `.commit()` or a `.rollback()`.  The transaction runs in the PostgreSQL
    ``READ COMMITTED`` isolation level.

`ISOLATION_LEVEL_SERIALIZABLE`
    Transactions are run at a ``SERIALIZABLE`` isolation level.


.. _transaction isolation level: 
   http://www.postgresql.org/docs/8.1/static/transaction-iso.html

Answer 2

The BEGIN with python standard DB API is always implicit. When you start working with the database the driver issues a BEGIN and after any COMMIT or ROLLBACK another BEGIN is issued. A python DB API compliant with the specification should always work this way (not only the postgresql).

You can change this setting the isolation level to autocommit with db.set_isolation_level(n) as pointed by Alex Martelli.

As Tebas said the begin is implicit but not executed until an SQL is executed, so if you don't execute any SQL, the session is not in a transaction.

Answer 3

I prefer to explicitly see where my transactions are :

• cursor.execute("BEGIN")
• cursor.execute("COMMIT")