Skip to content

Instantly share code, notes, and snippets.

@beugley

beugley/postgres_execute_with_reconnect.py

Created August 26, 2019 14:24
Show Gist options
  • Save beugley/1a5b09cf0dbd2a0f4fc8f99697b6ffda to your computer and use it in GitHub Desktop.
Save beugley/1a5b09cf0dbd2a0f4fc8f99697b6ffda to your computer and use it in GitHub Desktop.
Download ZIP
Python script to execute Postgresql SQL command with automatic reconnect to database
Raw
postgres_execute_with_reconnect.py
def execute_sql(self, sqlcmd, arg_list=()):
''' Returns the cursor if the SQL statement is successful, else
returns None if the SQL statement fails.
Automatically reconnects if the database connection is broken and
retries the SQL up to 5 times.
'''
logging.debug("Executing SQL: '%s', '%s'" % (sqlcmd, arg_list))
for i in range(5):
try:
self.cursor.execute(sqlcmd, arg_list)
except psycopg2.Error as e:
# Trap all SQL errors, but don't raise.
for line in e.args[0].split('\n'):
if line != '':
logging.error(line)
results = None
if (re.match("^.*\nserver closed the connection unexpectedly",
e.args[0]) or
re.match("^.*server closed the connection unexpectedly",
e.args[0]) or
re.match("^.*could not receive data from server",
e.args[0]) or
re.match("^.*SSL SYSCALL error: EOF detected",
e.args[0])
):
# Lost the database connection. Try to re-establish
# before executing the query again. Wait 60 seconds
# before doing so. Hopefully the condition that caused
# the connection to be dropped has passed (maybe the
# database was restarted?).
self.conn.close()
logging.warn("Lost database connection, trying to "
"re-establish")
t.sleep(60)
self.connect()
else:
# Got some other SQL error. It's been logged, so move on.
# With Postgres, we must rollback after an error or else
# all subsequent SQL commands will be ignored.
self.conn.rollback()
break
else:
results = self.cursor
break
return results
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment