simple_pooled_pg.py

"""SimplePooledPg - a very simple classic PyGreSQL connection pool.

Implements a pool of threadsafe cached connections
to a PostgreSQL database which are transparently reused,
using the classic (not DB-API 2 compliant) PyGreSQL pg API.

This should result in a speedup for persistent applications
such as the "Webware for Python" AppServer.

For more information on PostgreSQL, see:
    https://www.postgresql.org/
For more information on PyGreSQL, see:
    http://www.pygresql.org
For more information on Webware for Python, see:
    https://webwareforpython.github.io/w4py/

Measures are taken to make the pool of connections threadsafe
regardless of the fact that the PyGreSQL pg module itself is
not threadsafe at the connection level.  Connections will never be
shared between threads, so you can safely use transactions.

Usage:

The idea behind SimplePooledPg is that it's completely transparent.
After you have established your connection pool, stating the
number of connections to be cached in the pool and the
connection parameters, e.g.

    from dbutils.simple_pooled_pg import PooledPg
    dbpool = PooledPg(5, host=..., database=..., user=..., ...)

you can demand database connections from that pool,

    db = dbpool.connection()

and use them just as if they were ordinary PyGreSQL pg API
connections.  It's really just a proxy class.

db.close() will return the connection to the pool, it will not
actually close it.  This is so your existing code works nicely.

Ideas for improvement:

* Do not create the maximum number of connections on startup
already, but only a certain number and the rest on demand.
* Detect and transparently reset "bad" connections.  The PyGreSQL
pg API provides a status attribute and a reset() method for that.
* Connections should have some sort of "maximum usage limit"
after which they should be automatically closed and reopened.
* Prefer or enforce thread affinity for the connections.

Please note that these and other ideas have been already
implemented in in PooledPg, a more sophisticated version
of SimplePooledPg.  You might also consider using PersistentPg
instead for thread-affine persistent PyGreSQL connections.
SimplePooledPg may still serve as a very simple reference
and example implementation for developers.


Copyright, credits and license:

* Contributed as supplement for Webware for Python and PyGreSQL
  by Christoph Zwerschke in September 2005
* Based on the code of DBPool, contributed to Webware for Python
  by Dan Green in December 2000

Licensed under the MIT license.
"""

from pg import DB as PgConnection  # noqa: N811

from . import __version__


class PooledPgConnection:
    """A proxy class for pooled PostgreSQL connections.

    You don't normally deal with this class directly,
    but use PooledPg to get new connections.
    """

    def __init__(self, pool, con):
        """Initialize pooled connection."""
        self._con = con
        self._pool = pool

    def close(self):
        """Close the pooled connection."""
        # Instead of actually closing the connection,
        # return it to the pool so that it can be reused.
        if self._con is not None:
            self._pool.cache(self._con)
            self._con = None

    def __getattr__(self, name):
        """Get the attribute with the given name."""
        # All other attributes are the same.
        return getattr(self._con, name)

    def __del__(self):
        """Delete the pooled connection."""
        self.close()


class PooledPg:
    """A very simple PostgreSQL connection pool.

    After you have created the connection pool,
    you can get connections using getConnection().
    """

    version = __version__

    def __init__(self, maxconnections, *args, **kwargs):
        """Set up the PostgreSQL connection pool.

        maxconnections: the number of connections cached in the pool
        args, kwargs: the parameters that shall be used to establish
            the PostgreSQL connections using pg.connect()
        """
        # Since there is no connection level safety, we
        # build the pool using the synchronized queue class
        # that implements all the required locking semantics.
        from queue import Queue
        self._queue = Queue(maxconnections)
        # Establish all database connections (it would be better to
        # only establish a part of them now, and the rest on demand).
        for _i in range(maxconnections):
            self.cache(PgConnection(*args, **kwargs))

    def cache(self, con):
        """Add or return a connection to the pool."""
        self._queue.put(con)

    def connection(self):
        """Get a connection from the pool."""
        return PooledPgConnection(self, self._queue.get())