Psycopg2: connection not committing when exiting 'with' statement containing a try/except

Question

First I'd like to state I'm well aware that psycopg opens a transaction that requires to be committed for a database update to occur.

However here is a quote for the documentation regarding commit:

commit()

Commit any pending transaction to the database.

By default, Psycopg opens a transaction before executing the first command: if commit() is not called, the effect of any data manipulation will be lost.

The connection can be also set in “autocommit” mode: no transaction is automatically open, commands have immediate effect. See Transactions control for details.

Changed in version 2.5: if the connection is used in a with statement, the method is automatically called if no exception is raised in the with block.

And similarly for rollback :

rollback()

Roll back to the start of any pending transaction. Closing a connection without committing the changes first will cause an implicit rollback to be performed.

Changed in version 2.5: if the connection is used in a with statement, the method is automatically called if an exception is raised in the with block.

However this doesn't seem to work for me. Maybe I'm being picky and and should always take the habit of explicitly committing (but it would be nice to know that a rollback will occur if I forget it for example). I was wondering if someone came up with the same issue or if I'm using my with statement incorrectly.

Here is what my code looks like :

import psycopg2
from contextlib import closing

with closing(psycopg2.connect(
                               #conn params
                              ) as conn, \
     closing(conn.cursor()) as cur:

     try:
         sql = #query
         cur.execute(sql)

         output = cur.statusmessage
         print output

     except Exception, e :
         print "Error message : {}".format(e)
         raise

This doesn't work. There is no problem with the query itself since I get : UPDATE 842518 as output as expected. However the changes from the connection are clearly not committed since my db isn't updated. If I add

conn.commit()

in the try statement it works but the whole point is to avoid doing so.

My psycopg2 version is 2.6.

Link to doc

Answer 1

Wrapping the connection and cursor objects in closing is prolly messing with their context handling; they are context managers them selves. A closing wrapper will just call their close() methods on exit.

There's no need to manually call close() when using cursors in a with-block. The same does not apply to connections; only the transaction is ended on exit from a with-block. This is by design, so that you can use the same connection in multiple with-blocks:

Note that, unlike file objects or other resources, exiting the connection’s with block doesn’t close the connection but only the transaction associated with it: a connection can be used in more than a with statement and each with block is effectively wrapped in a separate transaction

You could for example wrap the connection in closing in an outermost with-block, and then use the same connection in inner with-blocks for transaction handling etc:

# This'll handle closing the connection
with closing(psycopg2.connect(...)) as conn:
    # This'll handle the transaction and closing the cursor
    with conn, conn.cursor() as cur:
        ...