Object Pool Pattern for Database Connections in Python

import time
from contextlib import contextmanager
from collections import deque


class ConnectionPool:
    def __init__(self, size=3, max_idle=5):
        self._idle = deque(maxlen=max_idle)
        self._active = set()
        self.size = size

    def _create(self):
        return {"created_at": time.time(), "queries": 0}

    def acquire(self):
        if self._idle:
            conn = self._idle.popleft()
        elif len(self._active) < self.size:
            conn = self._create()
        else:
            raise RuntimeError("Pool exhausted")
        self._active.add(id(conn))
        return conn

    def release(self, conn):
        self._active.discard(id(conn))
        conn["queries"] += 1
        self._idle.append(conn)

    @contextmanager
    def connection(self):
        conn = self.acquire()
        try:
            yield conn
        finally:
            self.release(conn)


if __name__ == "__main__":
    pool = ConnectionPool(size=2)

    with pool.connection() as c1:
        print("Acquired:", c1)
        with pool.connection() as c2:
            print("Acquired:", c2)
            try:
                pool.acquire()
            except RuntimeError as e:
                print("Error:", e)

    with pool.connection() as c3:
        print("Reused idle:", c3)
    print("Idle queue size:", len(pool._idle))