Each Answer to this Q is separated by one/two green lines.
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
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
This question is really old, but still pops up on Google searches so I think it’s valuable to know that the
psycopg2.connection instance now has a
closed attribute that will be
0 when the connection is open, and greater than zero when the connection is closed. The following example should demonstrate:
import psycopg2 import subprocess connection = psycopg2.connect( dbname=database, user=username, password=password, host=host, port=port ) print connection.closed # 0 # restart the db externally subprocess.check_call("sudo /etc/init.d/postgresql restart", shell=True) # this query will fail because the db is no longer connected try: cur = connection.cursor() cur.execute('SELECT 1') except psycopg2.OperationalError: pass print connection.closed # 2
pg_connection_status is implemented using PQstatus. psycopg doesn’t expose that API, so the check is not available. The only two places psycopg calls PQstatus itself is when a new connection is made, and at the beginning of execute. So yes, you will need to issue a simple SQL statement to find out whether the connection is still there.
connection.closed does not reflect a connection closed/severed by the server. It only indicates a connection closed by the client using
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")
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