python - 自动连接postgresql数据库

Question

我有以下代码:

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()

像这样我有一些 50 -60 个复杂的查询，但问题是有时 postgre sql 数据库会抛出以下错误

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.

看起来连接丢失了，我该如何自动连接 Postgre 数据库感谢您的帮助

Answer 1

我会依赖装饰器 - retry并重新连接:

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''')
        # ..