What's the preferred way to handle transactions in pyscopg2?

Question

According to psycopg2 documentation we should set autocommit to get the default PostgreSQL behaviour. This even seems to be the preferred approach according to some people. My question is, if this is the best approach in these circumstances, how do I start a transaction explicitly? Using cursor.execute("BEGIN")?

On another note, how do the context managers provided by psycopg2 work with this into account? Do they automatically start a transaction in the beginning of the block and commit on success?

score 1 · Answer 1 · answered Sep 11 '15 at 15:43

Personally I never use autocommit and almost always use the with syntax.

with psycopg2.connect(DSN) as conn:
    with conn.cursor() as curs:
        curs.execute(SQL)

Which will get committed as soon as you exit the context manager.

Likewise,

with psycopg2.connect(DSN) as conn:
    with conn.cursor() as curs:
        curs.execute(SQL)
        raise Exception("I changed my mind")

will cause the SQL statement to be safely rolled back.

with is very useful in Python for the few occasions where you really do want tight control of object lifetime, for example when object creation and destruction is tied to transactional behaviour.

score 0 · Answer 2 · answered Sep 08 '15 at 00:23

psycopg2's transaction model is a bit like that of JDBC.

To open a transaction, set autocommit off and just start doing work. No explicit BEGIN is required.

To commit, use conn.commit().

Autocommit is fine if each statement is a single transaction naturally. There's no need for explicit transaction management then.

What's the preferred way to handle transactions in pyscopg2?

2 Answers2