4
\$\begingroup\$

I am currently using a SimpleConnectionPool from psycopg2 to lease transactions to a PostgreSQL database and would like a review of my current implementation.

Code

from contextlib import contextmanager
from dataclasses import dataclass

from psycopg2.pool import SimpleConnectionPool


@dataclass
class PostgreSQLSimplePool:

    pool: SimpleConnectionPool

    @contextmanager
    def transact_session(self, commit: bool = False):
        conn = self.pool.getconn()
        try:
            yield conn
            if commit:
                conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            self.pool.putconn(conn)

Config

simple_pool = psycopg2.pool.SimpleConnectionPool(
        # testing purposes
        1, 20,
        user=POSTGRES_USER,
        password=POSTGRES_PASS,
        host=POSTGRES_HOST,
        port=POSTGRES_PORT,
        dbname=POSTGRES_DB_NAME,
        sslmode='require'
    )

repo = PostgreSQLSimplePool(pool=simple_pool)

Usage

with repo.transact_session() as connection:
    with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
        # do some stuff with the cursor... 
\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.