# Handling failures in DB transactions

This code is meant to retry the db operation when a DB connection breaks: OperationalError.

A transaction can fail for example when a db is being restarted and a commit fails. Or when a network error causes error in the DB transaction. (Variables-Names have been adjusted for demo).

My current method forcefully closes the current connection and re-establishes connections. Could there be a better way of doing this?

import psycopg2
from configs import mydb

class DBConnection:
"""connect to db to get data"""

def __init__(self):
self.establish_connections()

def establish_connections(self):
# connect to the database
self.connection = psycopg2.connect(
application_name="all_data",
dbname=mydb.dbname,
user=mydb.user,
host=mydb.host,
)
self.connection.autocommit = True
self.cursor = self.connection.cursor()
print("Connected to DBConnection")

def record_data(self, results):
sql_command = f"""INSERT INTO
msg_results(results)
VALUES ('{results}');"""
try:
self.cursor.execute(sql_command)
except psycopg2.OperationalError:
print("psycopg2.OperationalError: record_data")
# close old connection
try:
self.connection.close()
except:
print("Exception: in record_data while trying to close existing connection")
#restart connection
self.establish_connections()

# TRY THE TRANSACTION AGAIN
self.record_data(results)

# another error occurred, this has failed, go home
except Exception as e:
print("CALCResultsConnection.record_data", str(e))
$$$$
`
• Look into tenacity or retrying libraries (or other Open source libraries), which can ease out your retry logics for any domain. If you want me to give detailed example with your code as example, let me know, I will leave an answer – kushj May 16 '20 at 7:27
• Thanks @kushj. Yes please, I kindly request for a detailed answer – simic0de May 16 '20 at 10:53