Making sure that psycopg2 database connection alive
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 psycopg2import subprocessconnection = psycopg2.connect( dbname=database, user=username, password=password, host=host, port=port)print connection.closed # 0# restart the db externallysubprocess.check_call("sudo /etc/init.d/postgresql restart", shell=True)# this query will fail because the db is no longer connectedtry: cur = connection.cursor() cur.execute('SELECT 1')except psycopg2.OperationalError: passprint 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 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 psycopg2dsn = "dbname=postgres"conn = psycopg2.connect(dsn)# ... some time elapses, e.g. connection within a connection pooltry: connection.isolation_levelexcept OperationalError as oe: conn = psycopg2.connect(dsn)c = conn.cursor()c.execute("SELECT 1")