1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
|
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta charset="utf-8" />
<meta name="generator" content="Docutils 0.22: https://docutils.sourceforge.io/" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>DBUtils User's Guide</title>
<link rel="stylesheet" href="doc.css" type="text/css" />
</head>
<body class="with-toc">
<main id="dbutils-user-s-guide">
<h1 class="title">DBUtils User's Guide</h1>
<dl class="docinfo simple">
<dt class="version">Version<span class="colon">:</span></dt>
<dd class="version">3.1.2</dd>
<dt class="translations">Translations<span class="colon">:</span></dt>
<dd class="translations"><p>English | <a class="reference external" href="main.de.html">German</a></p>
</dd>
</dl>
<nav class="contents" id="contents" role="doc-toc">
<p class="topic-title">Contents</p>
<ul class="simple">
<li><p><a class="reference internal" href="#synopsis" id="toc-entry-1">Synopsis</a></p></li>
<li><p><a class="reference internal" href="#modules" id="toc-entry-2">Modules</a></p></li>
<li><p><a class="reference internal" href="#download" id="toc-entry-3">Download</a></p></li>
<li><p><a class="reference internal" href="#installation" id="toc-entry-4">Installation</a></p>
<ul>
<li><p><a class="reference internal" href="#installation-1" id="toc-entry-5">Installation</a></p></li>
</ul>
</li>
<li><p><a class="reference internal" href="#requirements" id="toc-entry-6">Requirements</a></p></li>
<li><p><a class="reference internal" href="#functionality" id="toc-entry-7">Functionality</a></p>
<ul>
<li><p><a class="reference internal" href="#simplepooleddb-simple-pooled-db" id="toc-entry-8">SimplePooledDB (simple_pooled_db)</a></p></li>
<li><p><a class="reference internal" href="#steadydbconnection-steady-db" id="toc-entry-9">SteadyDBConnection (steady_db)</a></p></li>
<li><p><a class="reference internal" href="#persistentdb-persistent-db" id="toc-entry-10">PersistentDB (persistent_db)</a></p></li>
<li><p><a class="reference internal" href="#pooleddb-pooled-db" id="toc-entry-11">PooledDB (pooled_db)</a></p></li>
<li><p><a class="reference internal" href="#which-one-to-use" id="toc-entry-12">Which one to use?</a></p></li>
</ul>
</li>
<li><p><a class="reference internal" href="#usage" id="toc-entry-13">Usage</a></p>
<ul>
<li><p><a class="reference internal" href="#persistentdb-persistent-db-1" id="toc-entry-14">PersistentDB (persistent_db)</a></p></li>
<li><p><a class="reference internal" href="#pooleddb-pooled-db-1" id="toc-entry-15">PooledDB (pooled_db)</a></p></li>
</ul>
</li>
<li><p><a class="reference internal" href="#advanced-usage" id="toc-entry-16">Advanced Usage</a></p></li>
<li><p><a class="reference internal" href="#notes" id="toc-entry-17">Notes</a></p></li>
<li><p><a class="reference internal" href="#future" id="toc-entry-18">Future</a></p></li>
<li><p><a class="reference internal" href="#bug-reports-and-feedback" id="toc-entry-19">Bug reports and feedback</a></p></li>
<li><p><a class="reference internal" href="#links" id="toc-entry-20">Links</a></p></li>
<li><p><a class="reference internal" href="#credits" id="toc-entry-21">Credits</a></p></li>
<li><p><a class="reference internal" href="#copyright-and-license" id="toc-entry-22">Copyright and License</a></p></li>
</ul>
</nav>
<section id="synopsis">
<h2>Synopsis</h2>
<p><a class="reference external" href="https://github.com/WebwareForPython/DBUtils">DBUtils</a> is a suite of Python modules allowing to connect in a safe and
efficient way between a threaded <a class="reference external" href="https://www.python.org">Python</a> application and a database.</p>
<p>DBUtils has been originally written particularly for <a class="reference external" href="https://webwareforpython.github.io/w4py/">Webware for Python</a> as
the application and <a class="reference external" href="https://www.pygresql.org/">PyGreSQL</a> as the adapter to a <a class="reference external" href="https://www.postgresql.org/">PostgreSQL</a> database, but it
can meanwhile be used for any other Python application and <a class="reference external" href="https://www.python.org/dev/peps/pep-0249/">DB-API 2</a>
conformant database adapter.</p>
</section>
<section id="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>
<table>
<thead>
<tr><th class="head" colspan="2"><p>Universal DB-API 2 variant</p></th>
</tr>
</thead>
<tbody>
<tr><td><p>steady_db</p></td>
<td><p>Hardened DB-API 2 connections</p></td>
</tr>
<tr><td><p>pooled_db</p></td>
<td><p>Pooling for DB-API 2 connections</p></td>
</tr>
<tr><td><p>persistent_db</p></td>
<td><p>Persistent DB-API 2 connections</p></td>
</tr>
<tr><td><p>simple_pooled_db</p></td>
<td><p>Simple pooling for DB-API 2</p></td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr><th class="head" colspan="2"><p>Classic PyGreSQL variant</p></th>
</tr>
</thead>
<tbody>
<tr><td><p>steady_pg</p></td>
<td><p>Hardened classic PyGreSQL connections</p></td>
</tr>
<tr><td><p>pooled_pg</p></td>
<td><p>Pooling for classic PyGreSQL connections</p></td>
</tr>
<tr><td><p>persistent_pg</p></td>
<td><p>Persistent classic PyGreSQL connections</p></td>
</tr>
<tr><td><p>simple_pooled_pg</p></td>
<td><p>Simple pooling for classic PyGreSQL</p></td>
</tr>
</tbody>
</table>
<p>The dependencies of the modules in the universal DB-API 2 variant
are as indicated in the following diagram:</p>
<img alt="dependencies_db.png" src="dependencies_db.png" />
<p>The dependencies of the modules in the classic PyGreSQL variant
are similar:</p>
<img alt="dependencies_pg.png" src="dependencies_pg.png" />
</section>
<section id="download">
<h2>Download</h2>
<p>You can download the actual version of DBUtils from
the Python Package Index at:</p>
<pre class="literal-block">https://pypi.python.org/pypi/DBUtils</pre>
<p>The source code repository can be found here on GitHub:</p>
<pre class="literal-block">https://github.com/WebwareForPython/DBUtils</pre>
</section>
<section id="installation">
<h2>Installation</h2>
<section id="installation-1">
<h3>Installation</h3>
<p>The package can be installed in the usual way:</p>
<pre class="literal-block">python setup.py install</pre>
<p>It is even easier to download and install the package in one go using <a class="reference external" href="https://pip.pypa.io/">pip</a>:</p>
<pre class="literal-block">pip install DBUtils</pre>
</section>
</section>
<section id="requirements">
<h2>Requirements</h2>
<p>DBUtils supports <a class="reference external" href="https://www.python.org">Python</a> versions 3.7 to 3.13.</p>
<p>The modules in the classic PyGreSQL variant need <a class="reference external" href="https://www.pygresql.org/">PyGreSQL</a> version 4.0
or above, while the modules in the universal DB-API 2 variant run with
any Python <a class="reference external" href="https://www.python.org/dev/peps/pep-0249/">DB-API 2</a> compliant database interface module.</p>
</section>
<section id="functionality">
<h2>Functionality</h2>
<p>This section will refer to the names in the DB-API 2 variant only,
but the same applies to the classic PyGreSQL variant.</p>
<p>DBUtils installs itself as a package <span class="docutils literal">dbutils</span> containing all the modules
that are described in this guide. Each of these modules contains essentially
one class with an analogous name that provides the corresponding functionality.
For instance, the module <span class="docutils literal">dbutils.pooled_db</span> contains the class <span class="docutils literal">PooledDB</span>.</p>
<section id="simplepooleddb-simple-pooled-db">
<h3>SimplePooledDB (simple_pooled_db)</h3>
<p>The class <span class="docutils literal">SimplePooledDB</span> in <span class="docutils literal">dbutils.simple_pooled_db</span> is a very basic
reference implementation of a pooled database connection. It is much less
sophisticated than the regular <span class="docutils literal">pooled_db</span> module and is particularly lacking
the failover functionality. <span class="docutils literal">dbutils.simple_pooled_db</span> is essentially the
same as the <span class="docutils literal">MiscUtils.DBPool</span> module that is part of Webware for Python.
You should consider it a demonstration of concept rather than something
that should go into production.</p>
</section>
<section id="steadydbconnection-steady-db">
<h3>SteadyDBConnection (steady_db)</h3>
<p>The class <span class="docutils literal">SteadyDBConnection</span> in the module <span class="docutils literal">dbutils.steady_db</span> 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.</p>
<p>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.</p>
<p>Usually, you will not use the <span class="docutils literal">steady_db</span> module directly; it merely serves
as a basis for the next two modules, <span class="docutils literal">persistent_db</span> and <span class="docutils literal">Pooled_db</span>.</p>
</section>
<section id="persistentdb-persistent-db">
<h3>PersistentDB (persistent_db)</h3>
<p>The class <span class="docutils literal">PersistentDB</span> in the module <span class="docutils literal">dbutils.persistent_db</span> 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.</p>
<p>The following diagram shows the connection layers involved when you
are using <span class="docutils literal">persistent_db</span> connections:</p>
<img alt="persistent.png" src="persistent.png" />
<p>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.</p>
<p>In short: <span class="docutils literal">persistent_db</span> 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.</p>
<p>Therefore, <span class="docutils literal">persistent_db</span> 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.</p>
</section>
<section id="pooleddb-pooled-db">
<h3>PooledDB (pooled_db)</h3>
<p>The class <span class="docutils literal">PooledDB</span> in the module <span class="docutils literal">dbutils.pooled_db</span> implements a pool
of steady, thread-safe cached connections to a database which are transparently
reused, using any DB-API 2 database module.</p>
<p>The following diagram shows the connection layers involved when you
are using <span class="docutils literal">pooled_db</span> connections:</p>
<img alt="pooled.png" src="pooled.png" />
<p>As the diagram indicates, <span class="docutils literal">pooled_db</span> 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 <span class="docutils literal">maxshared</span> 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 <span class="docutils literal">mincached</span> and at the most <span class="docutils literal">maxcached</span> 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.</p>
<p>If the underlying DB-API module is not thread-safe, thread locks will be
used to ensure that the <span class="docutils literal">pooled_db</span> 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.</p>
</section>
<section id="which-one-to-use">
<h3>Which one to use?</h3>
<p>Both <span class="docutils literal">persistent_db</span> and <span class="docutils literal">pooled_db</span> serve the same purpose to improve
the database access performance by recycling database connections, while
preserving stability even if database connection will be disrupted.</p>
<p>So which of these two modules should you use? From the above explanations
it is clear that <span class="docutils literal">persistent_db</span> 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 <span class="docutils literal">pooled_db</span>. The latter will also allow more
fine-tuning, particularly if you are using a thread-safe DB-API 2 module.</p>
<p>Since the interface of both modules is similar, you can easily switch from
one to the other and check which one will suit better.</p>
</section>
</section>
<section id="usage">
<h2>Usage</h2>
<p>The usage of all the modules is similar, but there are also some differences
in the initialization between the "Pooled" and "Persistent" variants and also
between the universal DB-API 2 and the classic PyGreSQL variants.</p>
<p>We will cover here only the <span class="docutils literal">persistent_db</span> module and the more complex
<span class="docutils literal">pooled_db</span> module. For the details of the other modules, have a look
at their module docstrings. Using the Python interpreter console, you can
display the documentation of the <span class="docutils literal">pooled_db</span> module as follows (this
works analogously for the other modules):</p>
<pre class="literal-block">help(pooled_db)</pre>
<section id="persistentdb-persistent-db-1">
<h3>PersistentDB (persistent_db)</h3>
<p>In order to make use of the <span class="docutils literal">persistent_db</span> module, you first need to set
up a generator for your kind of database connections by creating an instance
of <span class="docutils literal">persistent_db</span>, passing the following parameters:</p>
<ul>
<li><p><span class="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><span class="docutils literal">maxusage</span>: the maximum number of reuses of a single connection
(the default of <span class="docutils literal">0</span> or <span class="docutils literal">None</span> means unlimited reuse)</p>
<p>Whenever the limit is reached, the connection will be reset.</p>
</li>
<li><p><span class="docutils literal">setsession</span>: an optional list of SQL commands that may serve to
prepare the session, e.g. <span class="docutils literal">["set datestyle to german", <span class="pre">...]</span></span></p></li>
<li><p><span class="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><span class="docutils literal">ping</span>: an optional flag controlling when connections are checked
with the <span class="docutils literal">ping()</span> method if such a method is available
(<span class="docutils literal">0</span> = <span class="docutils literal">None</span> = never, <span class="docutils literal">1</span> = default = whenever it is requested,
<span class="docutils literal">2</span> = when a cursor is created, <span class="docutils literal">4</span> = when a query is executed,
<span class="docutils literal">7</span> = always, and all other bit combinations of these values)</p></li>
<li><p><span class="docutils literal">closeable</span>: if this is set to true, then closing connections will
be allowed, but by default this will be silently ignored</p></li>
<li><p><span class="docutils literal">threadlocal</span>: 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)</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 <span class="docutils literal">pgdb</span> as your DB-API 2 database module and
want every connection to your local database <span class="docutils literal">mydb</span> to be reused 1000 times:</p>
<pre class="literal-block">import pgdb # import used DB-API 2 module
from dbutils.persistent_db import PersistentDB
persist = PersistentDB(pgdb, 1000, database='mydb')</pre>
<p>Once you have set up the generator with these parameters, you can request
database connections of that kind:</p>
<pre class="literal-block">db = persist.connection()</pre>
<p>You can use these connections just as if they were ordinary DB-API 2
connections. Actually what you get is the hardened <span class="docutils literal">steady_db</span> version of
the underlying DB-API 2 connection.</p>
<p>Closing a persistent connection with <span class="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 <span class="docutils literal">closeable</span> parameter.</p>
<aside class="admonition warning">
<p class="admonition-title">Warning</p>
<p>Note that you need to explicitly start transactions by calling the
<span class="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 <span class="docutils literal">threadlocal</span> parameter to <span class="docutils literal">threading.local</span>, getting
connections may become a bit faster, but this may not work in all
environments (for instance, <span class="docutils literal">mod_wsgi</span> is known to cause problems
since it clears the <span class="docutils literal">threading.local</span> data between requests).</p>
</section>
<section id="pooleddb-pooled-db-1">
<h3>PooledDB (pooled_db)</h3>
<p>In order to make use of the <span class="docutils literal">pooled_db</span> module, you first need to set up the
database connection pool by creating an instance of <span class="docutils literal">pooled_db</span>, passing the
following parameters:</p>
<ul>
<li><p><span class="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><span class="docutils literal">mincached</span> : the initial number of idle connections in the pool
(the default of <span class="docutils literal">0</span> means no connections are made at startup)</p></li>
<li><p><span class="docutils literal">maxcached</span>: the maximum number of idle connections in the pool
(the default value of <span class="docutils literal">0</span> or <span class="docutils literal">None</span> means unlimited pool size)</p></li>
<li><p><span class="docutils literal">maxshared</span>: maximum number of shared connections allowed
(the default value of <span class="docutils literal">0</span> or <span class="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><span class="docutils literal">maxconnections</span>: maximum number of connections generally allowed
(the default value of <span class="docutils literal">0</span> or <span class="docutils literal">None</span> means any number of connections)</p></li>
<li><p><span class="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><span class="docutils literal">maxusage</span>: maximum number of reuses of a single connection
(the default of <span class="docutils literal">0</span> or <span class="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><span class="docutils literal">setsession</span>: an optional list of SQL commands that may serve to
prepare the session, e.g. <span class="docutils literal">["set datestyle to german", <span class="pre">...]</span></span></p></li>
<li><p><span class="docutils literal">reset</span>: how connections should be reset when returned to the pool
(<span class="docutils literal">False</span> or <span class="docutils literal">None</span> to rollback transactions started with <span class="docutils literal">begin()</span>,
the default value <span class="docutils literal">True</span> always issues a rollback for safety's sake)</p></li>
<li><p><span class="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><span class="docutils literal">ping</span>: an optional flag controlling when connections are checked
with the <span class="docutils literal">ping()</span> method if such a method is available
(<span class="docutils literal">0</span> = <span class="docutils literal">None</span> = never, <span class="docutils literal">1</span> = default = whenever fetched from the pool,
<span class="docutils literal">2</span> = when a cursor is created, <span class="docutils literal">4</span> = when a query is executed,
<span class="docutils literal">7</span> = always, and all other bit combinations of these values)</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 <span class="docutils literal">pgdb</span> as your DB-API 2 database module and
want a pool of at least five connections to your local database <span class="docutils literal">mydb</span>:</p>
<pre class="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
from that pool:</p>
<pre class="literal-block">db = pool.connection()</pre>
<p>You can use these connections just as if they were ordinary DB-API 2
connections. Actually what you get is the hardened <span class="docutils literal">steady_db</span> version of
the underlying DB-API 2 connection.</p>
<p>Please note that the connection may be shared with other threads by default
if you set a non-zero <span class="docutils literal">maxshared</span> parameter and the DB-API 2 module allows
this. If you want to have a dedicated connection, use:</p>
<pre class="literal-block">db = pool.connection(shareable=False)</pre>
<p>Instead of this, you can also get a dedicated connection as follows:</p>
<pre class="literal-block">db = pool.dedicated_connection()</pre>
<p>If you don't need it anymore, you should immediately return it to the
pool with <span class="docutils literal">db.close()</span>. You can get another connection in the same way.</p>
<p>⚠ Warning: In a threaded environment, never do the following:</p>
<pre class="literal-block">pool.connection().cursor().execute(...)</pre>
<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>
<pre class="literal-block">db = pool.connection()
cur = db.cursor()
cur.execute(...)
res = cur.fetchone()
cur.close() # or del cur
db.close() # or del db</pre>
<p>You can also use context managers for simpler code:</p>
<pre class="literal-block">with pool.connection() as db:
with db.cursor() as cur:
cur.execute(...)
res = cur.fetchone()</pre>
<aside class="admonition warning">
<p class="admonition-title">Warning</p>
<p>Note that you need to explicitly start transactions by calling the
<span class="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>
<section id="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, <span class="docutils literal">pyodbc</span> may require to configure connections by calling
the <span class="docutils literal">setencoding()</span> method of the connection. You can do this by passing
a modified <span class="docutils literal">connect()</span> function to <span class="docutils literal">PersistentDB</span> or <span class="docutils literal">PooledDB</span> as
<span class="docutils literal">creator</span> (the first argument), like this:</p>
<pre class="literal-block">from pyodbc import connect
from dbutils.pooled_db import PooledDB
def creator():
con = connect(...)
con.setdecoding(...)
return con
creator.dbapi = pyodbc
db_pool = PooledDB(creator, mincached=5)</pre>
</section>
<section id="notes">
<h2>Notes</h2>
<p>If you are using one of the popular object-relational mappers <a class="reference external" href="http://www.sqlobject.org/">SQLObject</a>
or <a class="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 <a class="reference external" href="http://modpython.org/">mod_python</a> or <a class="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 <span class="docutils literal">pooled_db</span> module, and several of these child processes are running,
you will have as much 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 <a class="reference external" href="https://www.pgpool.net/">pgpool</a>
or <a class="reference external" href="https://pgbouncer.github.io/">pgbouncer</a> for the PostgreSQL database.</p>
</section>
<section id="future">
<h2>Future</h2>
<p>Some ideas for future improvements:</p>
<ul class="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 <span class="docutils literal">monitor_db</span> and <span class="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>
<section id="bug-reports-and-feedback">
<h2>Bug reports and feedback</h2>
<p>You can transmit bug reports, patches and feedback by creating <a class="reference external" href="https://github.com/WebwareForPython/DBUtils/issues">issues</a> or
<a class="reference external" href="https://github.com/WebwareForPython/DBUtils/pulls">pull requests</a> on the GitHub project page for DBUtils.</p>
</section>
<section id="links">
<h2>Links</h2>
<p>Some links to related and alternative software:</p>
<ul class="simple">
<li><p><a class="reference external" href="https://github.com/WebwareForPython/DBUtils">DBUtils</a></p></li>
<li><p><a class="reference external" href="https://www.python.org">Python</a></p></li>
<li><p><a class="reference external" href="https://webwareforpython.github.io/w4py/">Webware for Python</a> framework</p></li>
<li><p>Python <a class="reference external" href="https://www.python.org/dev/peps/pep-0249/">DB-API 2</a></p></li>
<li><p><a class="reference external" href="https://www.postgresql.org/">PostgreSQL</a> database</p></li>
<li><p><a class="reference external" href="https://www.pygresql.org/">PyGreSQL</a> Python adapter for PostgreSQL</p></li>
<li><p><a class="reference external" href="https://www.pgpool.net/">pgpool</a> middleware for PostgreSQL connection pooling</p></li>
<li><p><a class="reference external" href="https://pgbouncer.github.io/">pgbouncer</a> lightweight PostgreSQL connection pooling</p></li>
<li><p><a class="reference external" href="http://www.sqlobject.org/">SQLObject</a> object-relational mapper</p></li>
<li><p><a class="reference external" href="https://www.sqlalchemy.org">SQLAlchemy</a> object-relational mapper</p></li>
</ul>
</section>
<section id="credits">
<h2>Credits</h2>
<dl class="field-list simple">
<dt>Author<span class="colon">:</span></dt>
<dd><p><a class="reference external" href="https://github.com/Cito">Christoph Zwerschke</a></p>
</dd>
<dt>Contributions<span class="colon">:</span></dt>
<dd><p>DBUtils uses code, input and suggestions made by
Ian Bicking, Chuck Esterbrook (Webware for Python), Dan Green (DBTools),
Jay Love, Michael Palmer, Tom Schwaller, Geoffrey Talvola,
Warren Smith (DbConnectionPool), Ezio Vernacotola, Jehiah Czebotar,
Matthew Harriger, Gregory Piñero and Josef van Eenbergen.</p>
</dd>
</dl>
</section>
<section id="copyright-and-license">
<h2>Copyright and License</h2>
<p>Copyright © 2005-2025 by Christoph Zwerschke.
All Rights Reserved.</p>
<p>DBUtils is free and open source software,
licensed under the <a class="reference external" href="https://opensource.org/licenses/MIT">MIT license</a>.</p>
</section>
</main>
</body>
</html>
|
