Re: Using context managers for connections/cursors - cursors are closed, connections are not?

On Wed, Nov 27, 2013 at 12:24 AM, Victor Hooi <victorhooi@yahoo.com> wrote:
> Hi,
>
> I'm trying to undersatnd
>
> http://initd.org/psycopg/articles/2013/04/07/psycopg-25-released/
>
> From
>
> So for example, say I have a function, get_pg_connection(), which returns a
> psycopg2 connection object:
>
>> def get_pg_connection(logger_name, database_config):
>[...]
>
> I then call it like so:
>
>> conn = get_pg_connection(logger_name, database_config)
>>     with conn.cursor() as cur:
>>         cur.execute("""SOME SQL STATEMENT""")
>>         conn.commit()
>>     conn.close()
>
>
> Is the above the correct way of doing things?

Apart from the indentation error, this is a way of doing it, but
probably not the most idiomatic. Because one of the point of using
"with" is avoiding to forget transactions open, I think it would be
better:

    conn = get_pg_connection(logger_name, database_config)
    with conn:
        with conn.cursor() as cur:
            cur.execute("""SOME SQL STATEMENT""")
    conn.close()

This will commit the transaction leaving the outer block. Because you
assigned the connection outside the block you can reuse it again
entering further with blocks, either in further code downstream or in
a loop etc. If you have a queue consumer you can do something like:

    conn = get_pg_connection(...)
    while 1:
        item = queue.get()
        with conn:
            with conn.cursor() as cur:
                cur.execute("something", [item])


> So cur.close() will be called when we leave the "with conn_cursor() as cur"
> right?

Yes.

> Can I use the conn object that get_pg_connection() returns in a context
> handler as well?
>
>     with get_pg_connection(logger_name, database_config) as conn:
>         with conn.cursor() as cur:
>             cur.execute("""SOME SQL STATEMENT""")
>             conn.commit()
>
> However, if I'm reading the release notes correctly, the connection *won't*
> be closed after we leave the "with get_pg_connection(logger_name,
> database_config) as conn" with clause?

Yes, you can do so. You won't call close() on the connection but it's
not important: the statement will be committed, which hopefully makes
the program correct; furthermore the database connection will be
interrupted in "idle" state, not in "idle in transaction", in which
case for instance a connection pooling middleware may decide to
discard the connection. If you want to use the connection only once
this is a perfectly reasonable way to go.

Actually, close() on the connection doesn't do much. Calling commit()
or rollback() at the right time and state is way more important for
the program correctness and the database health.


> So what would be the point of using a context manager for the connection
> object as well?

Transactions management: bringing the connection from "idle in
transaction" or "idle in transaction (aborted)" to "idle" in the least
time, making sure at syntactical level that this happens (no forgotten
idle intrans), and giving a visual clue of the mapping between the
section of the code and the state of the transaction. IMO the
connection context manager is actually much more important than the
cursor's one (close(), at least for regular client-side cursor, is an
almost no-op too).


-- Daniele