"""Small PostgreSQL helpers.""" import time import psycopg2 from rag_chatbot.shared.settings import secret, settings # The local PostgreSQL of docker-compose, or Neon when `[postgres] use_cloud = true` # (the full address with the password is `[secrets] POSTGRES_CLOUD_URL`). DATABASE_URL = ( secret("POSTGRES_CLOUD_URL") if settings.postgres.use_cloud else f"postgresql://{secret('POSTGRES_USER')}:{secret('POSTGRES_PASSWORD')}@" f"{settings.postgres.host}:{settings.postgres.port}/{settings.postgres.database}" ) def _run(operation): """Run `operation(cursor)` on a fresh connection that is always closed. A cloud database drops idle connections, so a lost connection is retried a few times.""" for attempt in range(4): try: connection = psycopg2.connect(DATABASE_URL) try: with connection, connection.cursor() as cursor: return operation(cursor) finally: connection.close() except (psycopg2.OperationalError, psycopg2.InterfaceError): if attempt == 3: raise time.sleep(2**attempt) def execute(query: str, parameters: tuple = ()) -> int: """Run a statement and return the number of affected rows.""" def operation(cursor): cursor.execute(query, parameters) return cursor.rowcount return _run(operation) def fetch_one(query: str, parameters: tuple = ()) -> tuple | None: def operation(cursor): cursor.execute(query, parameters) return cursor.fetchone() return _run(operation) def fetch_all(query: str, parameters: tuple = ()) -> list[tuple]: def operation(cursor): cursor.execute(query, parameters) return cursor.fetchall() return _run(operation)