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 (badly formatted, but just "view page source" and it's quite readable -- or, download the page and run it through any ReStructuredText formatter), 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

I prefer to explicitly see where my transactions are :

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

Answer 3

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 shoulw 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.