Postgres: cursor.execute("COMMIT") vs. connection.commit()

Question

I am running a postgres 9.2 server and have a python client using psycopg 2.5.

I ran some tests because I experience a lot of WARNING: there is no transaction in progress entries in my log files.

I have some code that can be simplified to the following:

import psycopg2

connection = psycopg2.connect(...)
with connection.cursor() as cursor:
    # Multiple insert statements
    cursor.execute("INSERT INTO ...")

    cursor.execute("COMMIT")

What I found that if I do the following, as soon as the first COMMIT is run (I reuse the same connection, so the above code is run multiple times) every statement afterwards is instantly committed. However, if I instead run connection.commit() it works as expected (commit statements made so far, future statements will not be committed automatically).

Is this a bug or is there some fine distinction I have missed somewhere in how this works? Is this a postgres issue or something to do with the innards of psycopg2?

Thanks in advance!

Answer 1

Yes, there is a not so fine distinction (documented both in the DBAPI PEP and in psycopg documentation). All Python DBAPI-compliant adapters implicitly start a transaction when the first SQL statement is issued through the connection. Then you should not execute a rollback/commit directly but instead use the methods available on the connection object.

If you want to do your own transaction management just put the connection in autocommit mode and then send your own BEGIN, followed by other statements and ended by the usual COMMIT or ROLLBACK.