So i have a flask app, that connects to a Postgres12 DB using a user that just has select privileges. Often times i see my apis return 400 error, and api does this when the sql query executed returns None.
I have built a small wrapper class over execute and executemany function for some error handling.
import time
from functools import wraps
import psycopg
from psycopg import InterfaceError, OperationalError
logger = logging.getLogger(__name__)
def retry(fn):
@wraps(fn)
def wrapper(*args, **kw):
cls = args[0]
exec = None
for x in range(cls._reconnectTries):
try:
return fn(*args, **kw)
except (InterfaceError, OperationalError) as e:
logger.warning(f"Database Connection {e} exception type: {type(e)}")
logger.info(f"Idle for {cls._reconnectIdle} seconds")
time.sleep(cls._reconnectIdle)
cls._connect()
exec = e
import sys
logger.exception(f"Exiting the system, {exec} ")
sys.exit(exec)
return wrapper
class Connection:
_reconnectTries = 5
_reconnectIdle = 2
def __init__(self, conn_string):
self._conn_string = conn_string
self.conn = None
self.cursor = None
self._connect()
def _connect(self):
self.conn = psycopg.connect(self._conn_string)
self.conn.autocommit = True
self.cursor = self.conn.cursor()
@retry
def execute(self, **kwargs):
# self.conn.commit()
if "query" in kwargs:
"""
this is done to ensure postgres logs multi line queries sent by client in single line for easier
log collection and debugging.
"""
kwargs["query"] = kwargs["query"].replace("\n", " ")
kwargs["query"] = " ".join(kwargs["query"].split())
return self.cursor.execute(**kwargs)
@retry
def executemany(self, **kwargs):
# self.conn.commit()
return self.cursor.executemany(**kwargs)
For the sake of simplicity, the query code looks somewhat like
store_detail_by_link_query = """
SELECT
json_build_object('id', store.id, 'uuid', store.uuid)
FROM
optimus_store store
WHERE
store.link= %(store_link_or_domain)s and store.is_active = TRUE and store.is_deleted = FALSE;
"""
optimus_connection = Connection(conn_string=CONN_STRING)
params = {
"store_link_or_domain": "dipen28",
}
row = optimus_connection.execute(
query=store_detail_by_link_query,
params=params,
).fetchone()
The problem is, in the same api call if i just do a check that result is None, then rerun the query, at that time the result comes.
I know the data is there in database, this is coming to us in our production system and i am unable to reproduce it at local.
Any help is much appreciated.
