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 ?

score 36 · Answer 1 · edited Mar 17 '20 at 23:42

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

Nice. Does it default to autocommit ? When setting n=1,2 or 3 what starts a transaction ? Creating a new cursor, or just every operation on the db since the last commit/rollback ? — Leeeroy, Aug 02 '09 at 17:54
Alex added more stuff after I asked. And it says READ_COMMITED is the default for psycopg2 — Leeeroy, Aug 02 '09 at 18:04
What starts a transaction is the commit or rollback of the previous one. — Alex Martelli, Aug 02 '09 at 22:57

score 19 · Answer 2 · edited Sep 25 '15 at 20:32

19

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.

edited Sep 25 '15 at 20:32

Eugene Yarmash

142,882
41
325
378

answered Aug 12 '09 at 11:03

Ferran

14,563
2
21
12

2

Is that true? I believe after a call of commit() or rollback() there is _not_ send another BEGIN immediately -- it is send implicit with the next execute(). After a commit()/rollback() the connection is in state 'idle' rather than 'idle in transaction'. – Tebas Nov 10 '11 at 14:59
After a commit()/rollback() the connection is in state 'idle'. The Python DB API will send another BEGIN only after another execute() so you don't create a deadlock if your program never ends. To summarize once you call execute() you should commit() or rollback() if not you program will be "idle in transaction". – praveenak Nov 10 '16 at 00:10

score 10 · Answer 3 · answered Aug 04 '09 at 10:18

10

I prefer to explicitly see where my transactions are :

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

answered Aug 04 '09 at 10:18

bobflux

11,123
3
27
27

3

Is that with autocommit on or off? Will it confuse Psycopg2, or other database modules? ODBC's transaction management uses a similar approach to the python DB API, and I've seen explicit warnings *not* to use SQL transaction management commands that subvert the ODBC interface (e.g. http://msdn.microsoft.com/en-us/library/ms131281.aspx). – Todd Owen Feb 20 '12 at 04:33
1

Please don't do that. You'll be fighting against the autocommit functionality, there's no guarantee the outcome will be pretty. – Brad Koch May 21 '14 at 00:28
2

It's with autocommit OFF. – bobflux Aug 31 '14 at 09:53
The autocommit functionality scares me too, so I'm wondering if we should turn it off and do it this way. I'd like to if I can. – sudo Dec 31 '15 at 03:55
1

The reason to use autocommit is if you use an external pool like pgbouncer, the connections are saved until commit meaning if you do not properly manage transactions (and actually fully close the cursors) on a web server you will have leftover "idle in transaction" connections. With autocommit on, cursors won't automatically be taking up server resources, yet you will have still saved time with the tcp socket setup to the pool. – parity3 Jul 12 '19 at 23:50

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

3 Answers3

Linked