2
\$\begingroup\$

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,
            password=mydb.password,
            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))
```
\$\endgroup\$
2
  • 2
    \$\begingroup\$ 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 \$\endgroup\$ – kushj May 16 '20 at 7:27
  • \$\begingroup\$ Thanks @kushj. Yes please, I kindly request for a detailed answer \$\endgroup\$ – simic0de May 16 '20 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.