Making sure that psycopg2 database connection alive

Question

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.

Answer 1

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