I have a python application that opens a database connection that can hang online for 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.
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