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 ?
db is your connection object. As Federico wrote here, the meaning of
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