Auto reconnect postgresq database

Question

I have the following code:

import psycopg2
conn = psycopg2.connect(database="****", user="postgres", password="*****", host="localhost", port="5432")
print ("Opened database successfully")
cur = conn.cursor()
cur.execute('''select * from xyz''')
print ("Table created successfully")
conn.commit()
conn.close()

Like this i have some 50 -60 complex queries, but problem is some times the postgre sql Database throws the below error

Traceback (most recent call last):
  File "C:/Users/ruw2/Desktop/SQL.py", line 87, in <module>
    cur.execute('''select * from xyz;''')
psycopg2.DatabaseError: server conn crashed?
server closed the connection unexpectedly. 
This probably means the server terminated abnormally before or while processing the request.

Looks like the connection is lost, how can i auto connect the Postgre database Appreciate your help

Answer 1

I would rely on decorators - retry and reconnect:

import psycopg2
from tenacity import retry, wait_exponential, stop_after_attempt
from typing import Callable

def reconnect(f: Callable):
    def wrapper(storage: DbStorage, *args, **kwargs):
        if not storage.connected():
            storage.connect()

        try:
            return f(storage, *args, **kwargs)
        except psycopg2.Error:
            storage.close()
            raise

    return wrapper


class DbStorage:

    def __init__(self, conn: string):
        self._conn: string = conn
        self._connection = None

    def connected(self) -> bool:
        return self._connection and self._connection.closed == 0

    def connect(self):
        self.close()
        self._connection = psycopg2.connect(self._conn)

    def close(self):
        if self.connected():
            # noinspection PyBroadException
            try:
                self._connection.close()
            except Exception:
                pass

        self._connection = None

    @retry(stop=stop_after_attempt(3), wait=wait_exponential())
    @reconnect
    def get_data(self): # pass here required params to get data from DB
        # ..
        cur = self._connection.cursor()
        cur.execute('''select * from xyz''')
        # ..