Making sure that psycopg2 database connection alive

user_17860 0
Viewed 63199

I have a python application that opens a database connection that can hang online for an hours, but sometimes the database server reboots and while python still have the connection it won't work with OperationalError exception.

So I'm looking for any reliable method to "ping" the database and know that connection is alive. I've checked a psycopg2 documentation but can't find anything like that. Sure I can issue some simple SQL statement like SELECT 1 and catch the exception, but I hope there is a native method, something like PHP pg_connection_status

Thanks.

psycopg2 connection python database
4 Answers

connection.closed does not reflect a connection closed/severed by the server. It only indicates a connection closed by the client using connection.close()

In order to make sure a connection is still valid, read the property connection.isolation_level. This will raise an OperationalError with pgcode == "57P01" in case the connection is dead.

This adds a bit of latency for a roundtrip to the database but should be preferable to a SELECT 1 or similar.

import psycopg2
dsn = "dbname=postgres"
conn = psycopg2.connect(dsn)

# ... some time elapses, e.g. connection within a connection pool

try:
    connection.isolation_level
except OperationalError as oe:
    conn = psycopg2.connect(dsn)

c = conn.cursor()
c.execute("SELECT 1")
user_24725190

howto check if connection closed:

  • conn.closed is 1 if closed else 0

  • if closed it raises except psycopg2.InterfaceError as exc: not only on query but in context manager: with conn: is sufficient for raise.

  • you then need to reestablish the connection. eg read out the pw and put into .connect(..)

user_49330530
Related