<li><p><aclass="reference internal" href="#copyright-and-license" id="toc-entry-23">Copyright and License</a></p></li>
</ul>
</nav>
<sectionid="synopsis">
<h2>Synopsis</h2>
<p><aclass="reference external" href="https://github.com/WebwareForPython/DBUtils">DBUtils</a> is a suite of Python modules that allows you to connect in a safe and
efficient way between a threaded <aclass="reference external" href="https://www.python.org">Python</a> application and a database.</p>
<p>DBUtils has been originally written particularly for <aclass="reference external" href="https://webwareforpython.github.io/w4py/">Webware for Python</a> as
the application and <aclass="reference external" href="https://www.pygresql.org/">PyGreSQL</a> as the adapter to a <aclass="reference external" href="https://www.postgresql.org/">PostgreSQL</a> database, but it
can meanwhile be used for any other Python application and <aclass="reference external" href="https://www.python.org/dev/peps/pep-0249/">DB-API 2</a>
conformant database adapter.</p>
</section>
<sectionid="modules">
<h2>Modules</h2>
<p>The DBUtils suite is realized as a Python package containing
two subsets of modules, one for use with arbitrary DB-API 2 modules,
the other one for use with the classic PyGreSQL module.</p>
<p>You can use these connections just as if they were ordinary DB-API 2
connections. Actually what you get is the hardened <spanclass="docutils literal">steady_db</span> version of
the underlying DB-API 2 connection.</p>
<p>Closing a persistent connection with <spanclass="docutils literal">db.close()</span> 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 <spanclass="docutils literal">closeable</span> parameter.</p>
<asideclass="admonition warning">
<pclass="admonition-title">Warning</p>
<p>Note that you need to explicitly start transactions by calling the
<spanclass="docutils literal">begin()</span> method. This ensures that the transparent reopening will be
suspended until the end of the transaction, and that the connection
will be rolled back before being reused by the same thread.</p>
</aside>
<p>By setting the <spanclass="docutils literal">threadlocal</span> parameter to <spanclass="docutils literal">threading.local</span>, getting
connections may become a bit faster, but this may not work in all
environments (for instance, <spanclass="docutils literal">mod_wsgi</span> is known to cause problems
since it clears the <spanclass="docutils literal">threading.local</span> data between requests).</p>
</section>
<sectionid="pooleddb-pooled-db-1">
<h3>PooledDB (pooled_db)</h3>
<p>In order to make use of the <spanclass="docutils literal">pooled_db</span> module, you first need to set up the
database connection pool by creating an instance of <spanclass="docutils literal">PooledDB</span>, passing the
following parameters:</p>
<ul>
<li><p><spanclass="docutils literal">creator</span>: either an arbitrary function returning new DB-API 2
connection objects or a DB-API 2 compliant database module</p></li>
<li><p><spanclass="docutils literal">mincached</span>: the initial number of idle connections in the pool
(the default of <spanclass="docutils literal">0</span> means no connections are made at startup)</p></li>
<li><p><spanclass="docutils literal">maxcached</span>: the maximum number of idle connections in the pool
(the default value of <spanclass="docutils literal">0</span> or <spanclass="docutils literal">None</span> means unlimited pool size)</p></li>
<li><p><spanclass="docutils literal">maxshared</span>: maximum number of shared connections allowed
(the default value of <spanclass="docutils literal">0</span> or <spanclass="docutils literal">None</span> means all connections are dedicated)</p>
<p>When this maximum number is reached, connections are shared if they
have been requested as shareable.</p>
</li>
<li><p><spanclass="docutils literal">maxconnections</span>: maximum number of connections generally allowed
(the default value of <spanclass="docutils literal">0</span> or <spanclass="docutils literal">None</span> means any number of connections)</p></li>
<li><p><spanclass="docutils literal">blocking</span>: determines behavior when exceeding the maximum</p>
<p>If this is set to true, block and wait until the number of
connections decreases, but by default an error will be reported.</p>
</li>
<li><p><spanclass="docutils literal">maxusage</span>: maximum number of reuses of a single connection
(the default of <spanclass="docutils literal">0</span> or <spanclass="docutils literal">None</span> means unlimited reuse)</p>
<p>When this maximum usage number of the connection is reached,
the connection is automatically reset (closed and reopened).</p>
</li>
<li><p><spanclass="docutils literal">setsession</span>: an optional list of SQL commands that may serve to
prepare the session, e.g. <spanclass="docutils literal">["set datestyle to german", <spanclass="pre">...]</span></span></p></li>
<li><p><spanclass="docutils literal">reset</span>: how connections should be reset when returned to the pool
(<spanclass="docutils literal">False</span> or <spanclass="docutils literal">None</span> to rollback transactions started with <spanclass="docutils literal">begin()</span>,
the default value <spanclass="docutils literal">True</span> always issues a rollback for safety's sake)</p></li>
<li><p><spanclass="docutils literal">failures</span>: 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</p></li>
<li><p><spanclass="docutils literal">isfatal</span>: 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
<aclass="reference internal" href="#controlling-the-failover-mechanism">Controlling the failover mechanism</a>)</p></li>
<li><p><spanclass="docutils literal">ping</span>: an optional flag controlling when connections are checked
(<spanclass="docutils literal">0</span> = <spanclass="docutils literal">None</span> = never, <spanclass="docutils literal">1</span> = default = whenever fetched from the pool,
<spanclass="docutils literal">2</span> = when a cursor is created, <spanclass="docutils literal">4</span> = when a query is executed,
<spanclass="docutils literal">7</span> = always, and all other bit combinations of these values)</p>
<p>By default, connections are checked with the <spanclass="docutils literal">ping()</span> 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 <spanclass="docutils literal">ping=1</span>,
or a tuple with the flag and one of the latter (see <aclass="reference internal" href="#checking-connections">Checking connections</a>).</p>
</li>
<li><p>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.</p></li>
</ul>
<p>For instance, if you are using <spanclass="docutils literal">pgdb</span> as your DB-API 2 database module and
want a pool of at least five connections to your local database <spanclass="docutils literal">mydb</span>:</p>
<preclass="literal-block">import pgdb # import used DB-API 2 module
from dbutils.pooled_db import PooledDB
pool = PooledDB(pgdb, 5, database='mydb')</pre>
<p>Once you have set up the connection pool you can request database connections
<p>This would release the connection too early for reuse which may be fatal
if the connections are not thread-safe. Make sure that the connection
object stays alive as long as you are using it, like that:</p>
<preclass="literal-block">db = pool.connection()
cur = db.cursor()
cur.execute(...)
res = cur.fetchone()
cur.close() # or del cur
db.close() # or del db</pre>
</aside>
<p>You can also use context managers for simpler code:</p>
<preclass="literal-block">with pool.connection() as db:
with db.cursor() as cur:
cur.execute(...)
res = cur.fetchone()</pre>
<asideclass="admonition warning">
<pclass="admonition-title">Warning</p>
<p>Note that you need to explicitly start transactions by calling the
<spanclass="docutils literal">begin()</span> method. This ensures that the connection will not be shared
with other threads, that the transparent reopening will be suspended
until the end of the transaction, and that the connection will be rolled
back before being given back to the connection pool.</p>
</aside>
</section>
</section>
<sectionid="advanced-usage">
<h2>Advanced Usage</h2>
<p>Sometimes you may want to prepare connections before they are used by
DBUtils, in ways that are not possible by just using the right parameters.
For instance, <spanclass="docutils literal">pyodbc</span> may require to configure connections by calling
the <spanclass="docutils literal">setencoding()</span> method of the connection. You can do this by passing
a modified <spanclass="docutils literal">connect()</span> function to <spanclass="docutils literal">PersistentDB</span> or <spanclass="docutils literal">PooledDB</span> as
<spanclass="docutils literal">creator</span> (the first argument), like this:</p>
<preclass="literal-block">import pyodbc
from dbutils.pooled_db import PooledDB
def creator():
con = pyodbc.connect(...)
con.setencoding(...)
return con
creator.dbapi = pyodbc
db_pool = PooledDB(creator, mincached=5)</pre>
<sectionid="controlling-the-failover-mechanism">
<h3>Controlling the failover mechanism</h3>
<p>When a database operation fails with one of the exception classes listed in
the <spanclass="docutils literal">failures</span> 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.</p>
<p>Unfortunately, many database modules report both kinds of problems with the
same exception class. For instance, when MySQL aborts a statement because it
<p>Every check of this kind requires an extra round trip to the database
server, so you should combine it with <spanclass="docutils literal">ping=4</span> or <spanclass="docutils literal">ping=7</span> only when
you really need it. Also keep in mind that an SQL statement executed for
checking a connection can start a transaction on the database server
when the connection does not run in autocommit mode.</p>
</aside>
</section>
</section>
<sectionid="notes">
<h2>Notes</h2>
<p>If you are using one of the popular object-relational mappers <aclass="reference external" href="http://www.sqlobject.org/">SQLObject</a>
or <aclass="reference external" href="https://www.sqlalchemy.org">SQLAlchemy</a>, you won't need DBUtils, since they come with their own
connection pools. SQLObject 2 (SQL-API) is actually borrowing some code
from DBUtils to split the pooling out into a separate layer.</p>
<p>Also note that when you are using a solution like the Apache webserver
with <aclass="reference external" href="http://modpython.org/">mod_python</a> or <aclass="reference external" href="https://github.com/GrahamDumpleton/mod_wsgi">mod_wsgi</a>, then your Python code will be usually run
in the context of the webserver's child processes. So if you are using
the <spanclass="docutils literal">pooled_db</span> module, and several of these child processes are running,
you will have as many database connection pools. If these processes are
running many threads, this may still be a reasonable approach, but if these
processes don't spawn more than one worker thread, as in the case of Apache's
"prefork" multi-processing module, this approach does not make sense.
If you're running such a configuration, you should resort to a middleware
for connection pooling that supports multi-processing, such as <aclass="reference external" href="https://www.pgpool.net/">pgpool</a>
or <aclass="reference external" href="https://pgbouncer.github.io/">pgbouncer</a> for the PostgreSQL database.</p>
</section>
<sectionid="future">
<h2>Future</h2>
<p>Some ideas for future improvements:</p>
<ulclass="simple">
<li><p>Alternatively to the maximum number of uses of a connection,
implement a maximum time to live for connections.</p></li>
<li><p>Create modules <spanclass="docutils literal">monitor_db</span> and <spanclass="docutils literal">monitor_pg</span> that will run in a separate
thread, monitoring the pool of the idle connections and maybe also the
shared connections respectively the thread-affine connections. If a
disrupted connection is detected, then it will be reestablished automatically
by the monitoring thread. This will be useful in a scenario where a database
powering a website is restarted during the night. Without the monitoring
thread, the users would experience a slight delay in the next morning,
because only then, the disrupted database connections will be detected and
the pool will be rebuilt. With the monitoring thread, this will already
happen during the night, shortly after the disruption.
The monitoring thread could also be configured to generally recreate
the connection pool every day shortly before the users arrive.</p></li>
<li><p>Optionally log usage, bad connections and exceeding of limits.</p></li>
</ul>
</section>
<sectionid="bug-reports-and-feedback">
<h2>Bug reports and feedback</h2>
<p>You can transmit bug reports, patches and feedback by creating <aclass="reference external" href="https://github.com/WebwareForPython/DBUtils/issues">issues</a> or
<aclass="reference external" href="https://github.com/WebwareForPython/DBUtils/pulls">pull requests</a> on the GitHub project page for DBUtils.</p>
</section>
<sectionid="links">
<h2>Links</h2>
<p>Some links to related and alternative software:</p>