DBUtils User's Guide

SimplePooledDB (simple_pooled_db)

The class SimplePooledDB in dbutils.simple_pooled_db is a very basic reference implementation of a pooled database connection. It is much less sophisticated than the regular pooled_db module and is particularly lacking the failover functionality. dbutils.simple_pooled_db is essentially the same as the MiscUtils.DBPool module that is part of Webware for Python. You should consider it a demonstration of concept rather than something that should go into production.

SteadyDBConnection (steady_db)

The class SteadyDBConnection in the module dbutils.steady_db implements "hardened" connections to a database, based on ordinary connections made by any DB-API 2 database module. A "hardened" connection will transparently reopen upon access when it has been closed or the database connection has been lost or when it is used more often than an optional usage limit.

A typical example where this is needed is when the database has been restarted while your application is still running and has open connections to the database, or when your application accesses a remote database in a network that is separated by a firewall and the firewall has been restarted and lost its state.

Usually, you will not use the steady_db module directly; it merely serves as a basis for the next two modules, persistent_db and pooled_db.

PersistentDB (persistent_db)

The class PersistentDB in the module dbutils.persistent_db implements steady, thread-affine, persistent connections to a database, using any DB-API 2 database module. "Thread-affine" and "persistent" means that the individual database connections stay assigned to the respective threads and will not be closed during the lifetime of the threads.

The following diagram shows the connection layers involved when you are using persistent_db connections:

Whenever a thread opens a database connection for the first time, a new connection to the database will be opened that will be used from now on for this specific thread. When the thread closes the database connection, it will still be kept open so that the next time when a connection is requested by the same thread, this already opened connection can be used. The connection will be closed automatically when the thread dies.

In short: persistent_db tries to recycle database connections to increase the overall database access performance of your threaded application, but it makes sure that connections are never shared between threads.

Therefore, persistent_db will work perfectly even if the underlying DB-API module is not thread-safe at the connection level, and it will avoid problems when other threads change the database session or perform transactions spreading over more than one SQL command.

PooledDB (pooled_db)

The class PooledDB in the module dbutils.pooled_db implements a pool of steady, thread-safe cached connections to a database which are transparently reused, using any DB-API 2 database module.

The following diagram shows the connection layers involved when you are using pooled_db connections:

As the diagram indicates, pooled_db can share opened database connections between different threads. This will happen by default if you set up the connection pool with a positive value of maxshared and the underlying DB-API 2 is thread-safe at the connection level, but you can also request dedicated database connections that will not be shared between threads. Besides the pool of shared connections, you can also set up a pool of at least mincached and at most maxcached idle connections that will be used whenever a thread is requesting a dedicated database connection or the pool of shared connections is not yet full. When a thread closes a connection that is not shared anymore, it is returned back to the pool of idle connections so that it can be recycled again.

If the underlying DB-API module is not thread-safe, thread locks will be used to ensure that the pooled_db connections are thread-safe. So you don't need to worry about that, but you should be careful to use dedicated connections whenever you change the database session or perform transactions spreading over more than one SQL command.

Which one to use?

Both persistent_db and pooled_db serve the same purpose to improve the database access performance by recycling database connections, while preserving stability even if the database connection is disrupted.

So which of these two modules should you use? From the above explanations it is clear that persistent_db will make more sense if your application keeps a constant number of threads which frequently use the database. In this case, you will always have the same amount of open database connections. However, if your application frequently starts and ends threads, then it will be better to use pooled_db. The latter will also allow more fine-tuning, particularly if you are using a thread-safe DB-API 2 module.

Since the interface of both modules is similar, you can easily switch from one to the other and check which one will suit better.

PersistentDB (persistent_db)

In order to make use of the persistent_db module, you first need to set up a generator for your kind of database connections by creating an instance of PersistentDB, passing the following parameters:

• creator: either an arbitrary function returning new DB-API 2 connection objects or a DB-API 2 compliant database module

• maxusage: the maximum number of reuses of a single connection (the default of 0 or None means unlimited reuse)

  Whenever the limit is reached, the connection will be reset.

• setsession: an optional list of SQL commands that may serve to prepare the session, e.g. ["set datestyle to german", ...]

• failures: an optional exception class or a tuple of exception classes for which the connection failover mechanism shall be applied, if the default (OperationalError, InterfaceError, InternalError) is not adequate for the used database module

• isfatal: an optional callable that is passed the exception which has been raised and shall return whether the connection failover mechanism shall be applied (must be passed as a keyword argument, see Controlling the failover mechanism)

• ping: an optional flag controlling when connections are checked (0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always, and all other bit combinations of these values)

  By default, connections are checked with the ping() method if such a method is available. You can also pass an SQL statement or a callable that shall be used for the check instead, which is then made as with ping=1, or a tuple with the flag and one of the latter (see Checking connections).

• closeable: if this is set to true, then closing connections will be allowed, but by default this will be silently ignored

• threadlocal: an optional class for representing thread-local data that will be used instead of our Python implementation (threading.local is faster, but cannot be used in all cases)

• The creator function or the connect function of the DB-API 2 compliant database module specified as the creator will receive any additional parameters such as the host, database, user, password etc. You may choose some or all of these parameters in your own creator function, allowing for sophisticated failover and load-balancing mechanisms.

For instance, if you are using pgdb as your DB-API 2 database module and want every connection to your local database mydb to be reused 1000 times:

import pgdb  # import used DB-API 2 module
from dbutils.persistent_db import PersistentDB
persist = PersistentDB(pgdb, 1000, database='mydb')

Once you have set up the generator with these parameters, you can request database connections of that kind:

db = persist.connection()

You can use these connections just as if they were ordinary DB-API 2 connections. Actually what you get is the hardened steady_db version of the underlying DB-API 2 connection.

Closing a persistent connection with db.close() will be silently ignored since it would be reopened at the next usage anyway and contrary to the intent of having persistent connections. Instead, the connection will be automatically closed when the thread dies. You can change this behavior by setting the closeable parameter.

By setting the threadlocal parameter to threading.local, getting connections may become a bit faster, but this may not work in all environments (for instance, mod_wsgi is known to cause problems since it clears the threading.local data between requests).

PooledDB (pooled_db)

In order to make use of the pooled_db module, you first need to set up the database connection pool by creating an instance of PooledDB, passing the following parameters:

• creator: either an arbitrary function returning new DB-API 2 connection objects or a DB-API 2 compliant database module

• mincached: the initial number of idle connections in the pool (the default of 0 means no connections are made at startup)

• maxcached: the maximum number of idle connections in the pool (the default value of 0 or None means unlimited pool size)

• maxshared: maximum number of shared connections allowed (the default value of 0 or None means all connections are dedicated)

  When this maximum number is reached, connections are shared if they have been requested as shareable.

• maxconnections: maximum number of connections generally allowed (the default value of 0 or None means any number of connections)

• blocking: determines behavior when exceeding the maximum

  If this is set to true, block and wait until the number of connections decreases, but by default an error will be reported.

• maxusage: maximum number of reuses of a single connection (the default of 0 or None means unlimited reuse)

  When this maximum usage number of the connection is reached, the connection is automatically reset (closed and reopened).

• setsession: an optional list of SQL commands that may serve to prepare the session, e.g. ["set datestyle to german", ...]

• reset: how connections should be reset when returned to the pool (False or None to rollback transactions started with begin(), the default value True always issues a rollback for safety's sake)

• failures: an optional exception class or a tuple of exception classes for which the connection failover mechanism shall be applied, if the default (OperationalError, InterfaceError, InternalError) is not adequate for the used database module

• isfatal: an optional callable that is passed the exception which has been raised and shall return whether the connection failover mechanism shall be applied (must be passed as a keyword argument, see Controlling the failover mechanism)

• ping: an optional flag controlling when connections are checked (0 = None = never, 1 = default = whenever fetched from the pool, 2 = when a cursor is created, 4 = when a query is executed, 7 = always, and all other bit combinations of these values)

  By default, connections are checked with the ping() method if such a method is available. You can also pass an SQL statement or a callable that shall be used for the check instead, which is then made as with ping=1, or a tuple with the flag and one of the latter (see Checking connections).

• The creator function or the connect function of the DB-API 2 compliant database module specified as the creator will receive any additional parameters such as the host, database, user, password etc. You may choose some or all of these parameters in your own creator function, allowing for sophisticated failover and load-balancing mechanisms.

For instance, if you are using pgdb as your DB-API 2 database module and want a pool of at least five connections to your local database mydb:

import pgdb  # import used DB-API 2 module
from dbutils.pooled_db import PooledDB
pool = PooledDB(pgdb, 5, database='mydb')

Once you have set up the connection pool you can request database connections from that pool:

db = pool.connection()

You can use these connections just as if they were ordinary DB-API 2 connections. Actually what you get is the hardened steady_db version of the underlying DB-API 2 connection.

Please note that the connection may be shared with other threads by default if you set a non-zero maxshared parameter and the DB-API 2 module allows this. If you want to have a dedicated connection, use:

db = pool.connection(shareable=False)

Instead of this, you can also get a dedicated connection as follows:

db = pool.dedicated_connection()

If you don't need it anymore, you should immediately return it to the pool with db.close(). You can get another connection in the same way.

You can also use context managers for simpler code:

with pool.connection() as db:
    with db.cursor() as cur:
        cur.execute(...)
        res = cur.fetchone()

Controlling the failover mechanism

When a database operation fails with one of the exception classes listed in the failures parameter, DBUtils assumes that the connection has been lost and transparently retries the operation, first with a new cursor, and then with a newly opened connection. This is exactly what you want when the database server has been restarted, but not when the statement itself failed for a reason that has nothing to do with the state of the connection.

Unfortunately, many database modules report both kinds of problems with the same exception class. For instance, when MySQL aborts a statement because it exceeded a deliberately requested time limit:

cur.execute('SELECT /*+ MAX_EXECUTION_TIME(10000) */ ...')

then pymysql raises an OperationalError, just as it would when the connection has really been lost. Since the failures parameter can only specify exception classes, and Python cannot exclude subclasses from an except clause, you cannot express this distinction by narrowing the failures parameter. In the above example, the statement would be executed up to three times, and the query would take up to 30 instead of 10 seconds.

For such cases you can pass an isfatal callable, which is asked for every error matching failures whether that error is really fatal for the connection:

import pymysql
from dbutils.pooled_db import PooledDB

def no_timeout(error):
    # 3024 = "max execution time exceeded", the connection is still fine
    return not error.args or error.args[0] != 3024

pool = PooledDB(pymysql, 5, database='mydb', isfatal=no_timeout)

When the callable returns a false value, the error is not considered fatal, so it is raised to the application immediately, and the connection is neither replaced nor removed from the pool. Note that isfatal must be passed as a keyword argument, and that it must tolerate arbitrary exception instances of the classes given as failures.

If you only want to suppress the failover mechanism for a particular statement instead of configuring it for the whole pool, you can use the no_failover() context manager of the cursor:

with pool.connection() as db:
    with db.cursor() as cur:
        with cur.no_failover():
            cur.execute('SELECT /*+ MAX_EXECUTION_TIME(10000) */ ...')
        rows = cur.fetchall()

Everything else, particularly the ping check and the usage bookkeeping of the underlying steady connection, is left untouched inside that context.

As a last resort, you can also reach through to the underlying DB-API 2 objects with the dbapi_connection attribute of a connection and the dbapi_cursor attribute of a cursor. Note that operations executed on these objects are not covered by the failover mechanism at all, and that they are not counted towards the maxusage limit either.

Checking connections

Connections that are kept in the pool or bound to a thread may become stale when the database server is restarted or drops connections that have been idle for too long. In order to notice this before the connection is used, DBUtils can check it with the ping() method of the underlying DB-API 2 connection, and the ping parameter controls when such a check is made.

However, ping() is not part of the DB-API 2 specification, and many widely used database modules do not implement it. When DBUtils notices that the method is unavailable, it stops checking the connections altogether. In this case, you can pass an SQL statement as ping parameter that shall be executed for checking the connection instead:

import psycopg2
from dbutils.pooled_db import PooledDB

pool = PooledDB(psycopg2, 5, database='mydb', ping='select 1')

Instead of an SQL statement, you can also pass a callable that is passed the underlying DB-API 2 connection and shall return whether it is still alive:

def is_healthy(con):
    # psycopg2 knows whether the connection has been closed already
    return not con.closed

pool = PooledDB(psycopg2, 5, database='mydb', ping=is_healthy)

Returning None counts as alive and raising an exception counts as not alive, so a callable that simply carries out some database operation without catching errors can be used as well. When the check reports the connection as not alive, it is transparently replaced with a newly opened connection, just as if ping() had reported it as broken.

Such a check is made whenever a connection is fetched from the pool or requested by a thread, i.e. at the time corresponding to ping=1. If you want it to be made at the other times as well, pass a tuple with the usual integer value and the SQL statement or the callable:

pool = PooledDB(psycopg2, 5, database='mydb', ping=(4, 'select 1'))