Making sure that psycopg2 database connection alive

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")

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.

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