Thread: Using context managers for connections/cursors - cursors are closed, connections are not?
Using context managers for connections/cursors - cursors are closed, connections are not?
From
Victor Hooi
Date:
Hi,
I'm trying to undersatnd
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):
logger = logging.getLogger(logger_name)
try:
conn = psycopg2.connect(database = database_config['dbname'],
user = database_config['user'],
host = database_config['host'],
port = database_config['port'],
password = database_config['password'],
connect_timeout = database_config['timeout'],
)
logger.debug('Successfully connected to database {host}:{port}/{dbname}'.format(**database_config))
return conn
except psycopg2.OperationalError as e:
logger.error('Error connecting to database {0[host]}:{0[port]}/{0[dbname]} - {1}'.format(database_config, e), exc_info=True)
# sys.exit(1) # We should handle this differently? Retry? Move onto next one?
except Exception as e:
logger.error('Error - {}'.format(e), exc_info=True)
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?
So cur.close() will be called when we leave the "with conn_cursor() as cur" right?
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?
So what would be the point of using a context manager for the connection object as well?
Cheers,
Victor
Re: Using context managers for connections/cursors - cursors are closed, connections are not?
From
Karsten Hilbert
Date:
On Wed, Nov 27, 2013 at 11:24:34AM +1100, Victor Hooi wrote:
Quite apart from your question but if you do this:
> def get_pg_connection(logger_name, database_config):
...
> except psycopg2.OperationalError as e:
> logger.error('Error connecting to database {0[host]}:{0[port]}/{0[dbname]} - {1}'.format(database_config, e),
exc_info=True)
...
> except Exception as e:
> logger.error('Error - {}'.format(e), exc_info=True)
you will see UnicodeDecodeError from the logger
infrastructure as soon as the PostgreSQL error message
contains characters which don't fit the ASCII subset.
This will happen as soon as PostgreSQL is set to
a locale other then C or en_*
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Re: Using context managers for connections/cursors - cursors are closed, connections are not?
From
Daniele Varrazzo
Date:
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