The db_pool module is useful for managing database connections. It provides three primary benefits: cooperative yielding during database operations, concurrency limiting to a database host, and connection reuse. db_pool is intended to be database-agnostic, compatible with any DB-API 2.0 database module.
It has currently been tested and used with both MySQLdb and psycopg2.
A ConnectionPool object represents a pool of connections open to a particular database. The arguments to the constructor include the database-software-specific module, the host name, and the credentials required for authentication. After construction, the ConnectionPool object decides when to create and sever connections with the target database.
>>> import MySQLdb
>>> cp = ConnectionPool(MySQLdb, host='localhost', user='root', passwd='')
Once you have this pool object, you connect to the database by calling get() on it:
>>> conn = cp.get()
This call may either create a new connection, or reuse an existing open connection, depending on whether it has one open already or not. You can then use the connection object as normal. When done, you must return the connection to the pool:
>>> conn = cp.get()
>>> try:
... result = conn.cursor().execute('SELECT NOW()')
... finally:
... cp.put(conn)
After you’ve returned a connection object to the pool, it becomes useless and will raise exceptions if any of its methods are called.
In addition to the database credentials, there are a bunch of keyword constructor arguments to the ConnectionPool that are useful.
If you want to connect to multiple databases easily (and who doesn’t), the DatabaseConnector is for you. It’s a pool of pools, containing a ConnectionPool for every host you connect to.
The constructor arguments are:
>>> dc = DatabaseConnector(MySQLdb,
... {'db.internal.example.com': {'user': 'internal', 'passwd': 's33kr1t'},
... 'localhost': {'user': 'root', 'passwd': ''}})
If the credentials contain a host named ‘default’, then the value for ‘default’ is used whenever trying to connect to a host that has no explicit entry in the database. This is useful if there is some pool of hosts that share arguments.
The rest of the arguments to the DatabaseConnector constructor are passed on to the ConnectionPool.
Caveat: The DatabaseConnector is a bit unfinished, it only suits a subset of use cases.