DBUtils User's Guide及中文

秒速五厘米 2022-07-13 09:00 163阅读 0赞

# DBUtils User's Guide #

<table style="empty-cells:show; margin:2em 4em; color:rgb(0,0,0); font-family:Verdana,Arial,Helvetica,sans-serif; font-size:13.3333px">     
 <tbody> 
  <tr> 
   <th style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0px; padding-right:0.5em; vertical-align:top; text-align:left; white-space:nowrap"> Version:</th> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> 1.1</td> 
  </tr> 
  <tr> 
   <th style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0px; padding-right:0.5em; vertical-align:top; text-align:left; white-space:nowrap"> Released:</th> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> 08/14/11</td> 
  </tr> 
  <tr> 
   <th style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0px; padding-right:0.5em; vertical-align:top; text-align:left; white-space:nowrap"> Translations:</th> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> English&nbsp;<a href="https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.de.html" rel="nofollow">German</a></td> 
  </tr> 
 </tbody> 
</table>

Contents

*  [Synopsis][]
 *  [Modules][]
 *  [Download][]
 *  [Installation][]
    
     *  [Installation as a standalone (top-level) package][Installation as a standalone _top-level_ package]
     *  [Installation as a Webware for Python subpackage (plug-in)][Installation as a Webware for Python subpackage _plug-in]
 *  [Requirements][]
 *  [Functionality][]
    
     *  [SimplePooledDB][]
     *  [SteadyDB][]
     *  [PersistentDB][]
     *  [PooledDB][]
     *  [Which one to use?][Which one to use]
 *  [Usage][]
    
     *  [PersistentDB][PersistentDB 1]
     *  [PooledDB][PooledDB 1]
     *  [Usage in Webware for Python][]
 *  [Notes][]
 *  [Future][]
 *  [Bug reports and feedback][]
 *  [Links][]
 *  [Credits][]
 *  [Copyright and License][]

# Synopsis #

[DBUtils][] is a suite of Python modules allowing to connect in a safe and efficient way between a threaded [Python][] application and a database. DBUtils has been written in view of [Webware for Python][] as the application and [PyGreSQL][] as the adapter to a [PostgreSQL][] database, but it can be used for any other Python application and [DB-API 2][] conformant database adapter.

# Modules #

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.

<table style="empty-cells:show; margin-top:0.5em; margin-bottom:0.5em">     
 <thead> 
  <tr> 
   <th colspan="2" style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Universal DB-API 2 variant</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> SteadyDB.py</td> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Hardened DB-API 2 connections</td> 
  </tr> 
  <tr> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> PooledDB.py</td> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Pooling for DB-API 2 connections</td> 
  </tr> 
  <tr> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> PersistentDB.py</td> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Persistent DB-API 2 connections</td> 
  </tr> 
  <tr> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> SimplePooledDB.py</td> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Simple pooling for DB-API 2</td> 
  </tr> 
 </tbody> 
</table>

<table style="empty-cells:show; margin-top:0.5em; margin-bottom:0.5em">     
 <thead> 
  <tr> 
   <th colspan="2" style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Classic PyGreSQL variant</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> SteadyPg.py</td> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Hardened classic PyGreSQL connections</td> 
  </tr> 
  <tr> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> PooledPg.py</td> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Pooling for classic PyGreSQL connections</td> 
  </tr> 
  <tr> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> PersistentPg.py</td> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Persistent classic PyGreSQL connections</td> 
  </tr> 
  <tr> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> SimplePooledPg.py</td> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Simple pooling for classic PyGreSQL</td> 
  </tr> 
 </tbody> 
</table>

The dependencies of the modules in the universal DB-API 2 variant are as indicated in the following diagram:

![dbdep.gif][]

The dependencies of the modules in the classic PyGreSQL variant are similar:

![pgdep.gif][]

# Download #

You can download the actual version of DBUtils from the Webware for Python homepage at:

> [http://www.webwareforpython.org/downloads/DBUtils/][http_www.webwareforpython.org_downloads_DBUtils]

You can also download it from the Python Package Index (also known as the "Cheese Shop") at:

> [http://www.python.org/pypi/DBUtils/][http_www.python.org_pypi_DBUtils]

# Installation #

## Installation as a standalone (top-level) package ##

If you intend to use DBUtils from other applications than Webware for Python, it is recommended to install the package in the usual way:

python setup.py install

## Installation as a Webware for Python subpackage (plug-in) ##

If you want to use DBUtils as a supplement for the Webware for Python framework only, you should install it as a Webware plug-in:

python setup.py install --install-lib=/path/to/Webware

Replace /path/to/Webware with the path to the root directory of your Webware for Python installation. You will also need to run the Webware installer if this has not been done already or if you want to integrate the DBUtils documentation into the Webware documentation:

cd path/to/Webware
    python install.py

# Requirements #

DBUtils runs with [Python][] 2.3 or newer Python 2 versions. The modules in the classic PyGreSQL variant need [PyGreSQL][] version 3.4 or above, while the modules in the universal DB-API 2 variant run with any Python [DB-API 2][] compliant database interface module.

# Functionality #

This section will refer to the names in the DB-API 2 variant only, but the same applies to the classic PyGreSQL variant.

## SimplePooledDB ##

DBUtils.SimplePooledDB is a very basic reference implementation of a pooled database connection. It is much less sophisticated than the regular PooledDB module and is particularly lacking the failover functionality. DBUtils.SimplePooledDB 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.

## SteadyDB ##

DBUtils.SteadyDB is a module implementing "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 SteadyDB module directly; it merely serves as a basis for the next two modules, PersistentDB and PooledDB.

## PersistentDB ##

DBUtils.PersistentDB implements steady, thread-affine, persistent connections to a database, using any DB-API 2 database module.

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

![persist.gif][]

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 connecton will be closed automatically when the thread dies.

In short: PersistentDB 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, PersistentDB 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 ##

DBUtils.PooledDB 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 PooledDB connections:

![pool.gif][]

As the diagram indicates, PooledDB 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 the 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 any more, 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 PooledDB 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 PersistentDB and PooledDB serve the same purpose to improve the database access performance by recycling database connections, while preserving stability even if database connection will be disrupted.

So which of these two modules should you use? From the above explanations it is clear that PersistentDB 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 usePooledDB. 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.

# Usage #

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.

We will cover here only the PersistentDB module and the more complex PooledDB 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 PooledDB module as follows (this works analogously for the other modules):

help(PooledDB)

## PersistentDB ##

In order to make use of the PersistentDB 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, InternalError) is not adequate
 *  ping: an optional flag controlling when connections are checked with the ping() method if such a method is available (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)
 *  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.PersistentDB 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 SteadyDB 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 be setting the closeable parameter.

Note that you need to explicitly start transactions by calling the begin() 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.

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 ##

In order to make use of the PooledDB 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 transcations 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, InternalError) is not adequate
 *  ping: an optional flag controlling when connections are checked with the ping() method if such a method is available (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)
 *  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.PooledDB 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 SteadyDB 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 any more, you should immediately return it to the pool with db.close(). You can get another connection in the same way.

*Warning:* In a threaded environment, never do the following:

pool.connection().cursor().execute(...)

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:

db = pool.connection()
    cur = db.cursor()
    cur.execute(...)
    res = cur.fetchone()
    cur.close() # or del cur
    db.close() # or del db

Note that you need to explicitly start transactions by calling the begin() 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.

## Usage in Webware for Python ##

If you are using DBUtils in order to access a database from [Webware for Python][] servlets, you need to make sure that you set up your database connection generators only once when the application starts, and not every time a servlet instance is created. For this purpose, you can add the necessary code to the module or class initialization code of your base servlet class, or you can use thecontextInitialize() function in the \_\_init\_\_.py script of your application context.

The directory Examples that is part of the DButils distribution contains an example context for Webware for Python that uses a small demo database designed to track the attendees for a series of seminars (the idea for this example has been taken from the article "[The Python DB-API][]" by Andrew Kuchling).

The example context can be configured by either creating a config file Configs/Database.config or by directly changing the default parameters in the example servlet Examples/DBUtilsExample.py. This way you can set an appropriate database user and password, and you can choose the underlying database module (PyGreSQL classic or any DB-API 2 module). If the setting maxcached is present, then the example servlet will use the "Pooled" variant, otherwise it will use the "Persistent" variant.

# Notes #

If you are using one of the popular object-relational mappers [SQLObject][] or [SQLAlchemy][], 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.

Also note that when you are using a solution like the Apache webserver with [mod\_python][mod_python] or [mod\_wsgi][mod_wsgi], then your Python code will be usually run in the context of the webserver's child processes. So if you are using the PooledDB 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 [pgpool][] or [pgbouncer][] for the PostgreSQL database.

# Future #

Some ideas for future improvements:

*  Alternatively to the maximum number of uses of a connection, implement a maximum time to live for connections.
 *  Create modules MonitorDB and MonitorPg 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.
 *  Optionally log usage, bad connections and exceeding of limits.

# Bug reports and feedback #

Please send bug reports, patches and feedback directly to the author (using the email address given below).

If there are Webware related problems, these can also be discussed in the [Webware for Python mailing list][].

# Links #

Some links to related and alternative software:

*  [DBUtils][]
 *  [Python][]
 *  [Webware for Python][] framework
 *  Python [DB-API 2][]
 *  [PostgreSQL][] database
 *  [PyGreSQL][] Python adapter for PostgreSQL
 *  [pgpool][] middleware for PostgreSQL connection pooling
 *  [pgbouncer][] lightweight PostgreSQL connection pooling
 *  [SQLObject][] object-relational mapper
 *  [SQLAlchemy][] object-relational mapper

# Credits #

<table style="empty-cells:show; margin-top:0.5em; margin-bottom:0.5em">     
 <tbody> 
  <tr> 
   <th style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0px; padding-right:0.5em; vertical-align:top; text-align:left; white-space:nowrap"> Author:</th> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> Christoph Zwerschke &lt;<a href="mailto:cito@online.de" rel="nofollow">cito@online.de</a>&gt;</td> 
  </tr> 
  <tr> 
   <th style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0px; padding-right:0.5em; vertical-align:top; text-align:left; white-space:nowrap"> Contributions:</th> 
   <td style="font-family:Verdana,Arial,Helvetica,sans-serif; font-size:10pt; padding-left:0.5em; padding-right:0.5em; vertical-align:top"> 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.</td> 
  </tr> 
 </tbody> 
</table>

# Copyright and License #

DBUtils is free and open source software, licensed under the [Open Software License version 2.1][].

原文：https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html

中文：

# DBUtils 用户指南 #

*  [摘要][Link 1]
 *  [模块][Link 2]
 *  [下载][Link 3]
 *  [安装][Link 4]
    
     *  [安装为顶层模块][Link 5]
     *  [安装为Webware的子模块(插件)][Webware]
 *  [功能][Link 6]
    
     *  [SimplePooledDB][SimplePooledDB 1]
     *  [SteadyDB][SteadyDB 1]
     *  [PersistentDB][PersistentDB 2]
     *  [PooledDB][PooledDB 2]
     *  [该选择哪一个？][Link 7]
 *  [使用方法][Link 8]
    
     *  [PersistentDB][PersistentDB 3]
     *  [PooledDB][PooledDB 3]
     *  [在Webware中使用][Webware 1]
 *  [注意][Link 9]
 *  [未来功能][Link 10]
 *  [错误报告与回馈][Link 11]
 *  [链接][Link 12]
 *  [作者列表][Link 13]
 *  [版权与许可][Link 14]

# [摘要][Link 15] #

[DBUtils][] 是一套允许线程化 [Python][] 程序可以安全和有效的访问[数据库][Link 16]的模块。DBUtils已经作为 [Webware for Python][] 一部分用来结合 [PyGreSQL][] 访问 [PostgreSQL][] 数据库，当然他也可以用在其他[Python][Python 1]应用程序中来访问 [DB-API 2][] 兼容的数据库接口。

# [模块][Link 17] #

DBUtils实际上是一个包含两个子模块的Python包，一个用于连接DB-API 2模块，另一个用于连接典型的PyGreSQL模块。

<table>     
 <thead> 
  <tr> 
   <th colspan="2">全局的DB-API 2变量</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SteadyDB.py</td> 
   <td>用于稳定数据库连接</td> 
  </tr> 
  <tr> 
   <td>PooledDB.py</td> 
   <td>连接池</td> 
  </tr> 
  <tr> 
   <td>PersistentDB.py</td> 
   <td>维持持续的数据库连接</td> 
  </tr> 
  <tr> 
   <td>SimplePooledDB.py</td> 
   <td>简单连接池</td> 
  </tr> 
 </tbody> 
</table>

<table>     
 <thead> 
  <tr> 
   <th colspan="2">典型的 PyGreSQL 变量</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SteadyPg.py</td> 
   <td>稳定PyGreSQL连接</td> 
  </tr> 
  <tr> 
   <td>PooledPg.py</td> 
   <td>PyGreSQL连接池</td> 
  </tr> 
  <tr> 
   <td>PersistentPg.py</td> 
   <td>维持持续的PyGreSQL连接</td> 
  </tr> 
  <tr> 
   <td>SimplePooledPg.py</td> 
   <td>简单的PyGreSQL连接池</td> 
  </tr> 
 </tbody> 
</table>

对标准DB-API 2模块的依赖如下图所示：

![dbdep.gif][dbdep.gif 1]

对典型的PyGreSQL模块依赖如下图所示：

![pgdep.gif][pgdep.gif 1]

# [下载][Link 18] #

你可以从 Webware 的网站下载最新版本：

> [http://www.webwareforpython.org/downloads/DBUtils/][http_www.webwareforpython.org_downloads_DBUtils]

也可以从Python Package Index来下载：

> [http://www.python.org/pypi/DBUtils/][http_www.python.org_pypi_DBUtils]

# [安装][Link 19] #

## [安装为顶层模块][Link 20] ##

如果你打算在除了Webware之外的程序中使用，推荐安装为顶层模块:

python setup.py install

## [安装为Webware的子模块(插件)][Webware 2] ##

如果你只是打算在Webware中使用，则可以按照如下安装:

python setup.py install --install-lib=/path/to/Webware

替换 /path/to/Webware 为Webware安装的根路径。你还需要运行Webware的安装程序来同时包含DBUtils的文档:

cd path/to/Webware
    python install.py

# [功能][Link 21] #

这一节的主要例子面向DB-API 2，但是也适用于典型的PyGreSQL模块。

## [SimplePooledDB][SimplePooledDB 2] ##

DBUtils.SimplePooledDB 是一个非常简单的数据库连接池实现。他比完善的 PooledDB 模块缺少很多功能。DBUtils.SimplePooledDB 本质上类似于 MiscUtils.DBPool 这个Webware的组成部分。你可以把它看作一种演示程序。

## [SteadyDB][SteadyDB 2] ##

DBUtils.SteadyDB 是一个模块实现了"强硬"的数据库连接，基于DB-API 2建立的原始连接。一个"强硬"的连接意味着在连接关闭之后，或者使用次数操作限制时会重新连接。

一个典型的例子是数据库重启时，而你的程序仍然在运行并需要访问数据库，或者当你的程序连接了一个防火墙后面的远程数据库，而防火墙重启时丢失了状态时。

一般来说你不需要直接使用 SteadyDB 它只是给接下来的两个模块提供基本服务， PersistentDB 和 PooledDB。

## [PersistentDB][PersistentDB 4] ##

DBUtils.PersistentDB 实现了强硬的、线程安全的、顽固的数据库连接，使用DB-API 2模块。如下图展示了使用 PersistentDB 时的连接层步骤：

![persist.gif][persist.gif 1]

当一个线程首次打开一个数据库连接时，一个连接会打开并仅供这个线程使用。当线程关闭连接时，连接仍然持续打开供这个线程下次请求时使用这个已经打开的连接。连接在线程死亡时自动关闭。

简单的来说 PersistentDB 尝试重用数据库连接来提高线程化程序的数据库访问性能，并且他确保连接不会被线程之间共享。

因此， PersistentDB 可以在底层DB-API模块并非线程安全的时候同样工作的很好，并且他会在其他线程改变数据库会话或者使用多语句事务时同样避免问题的发生。

## [PooledDB][PooledDB 4] ##

DBUtils.PooledDB 实现了一个强硬的、线程安全的、有缓存的、可复用的数据库连接，使用任何DB-API 2模块。如下图展示了使用 PooledDB 时的工作流程：

![pool.gif][pool.gif 1]

如图所示 PooledDB 可以在不同线程之间共享打开的数据库连接。这在你连接并指定 maxshared 参数，并且底层的DB-API 2接口是线程安全才可以，但是你仍然可以使用专用数据库连接而不在线程之间共享连接。除了共享连接以外，还可以设立一个至少 mincached 的连接池，并且最多允许使用 maxcached 个连接，这可以同时用于专用和共享连接池。当一个线程关闭了一个非共享连接，则会返还到空闲连接池中等待下次使用。

如果底层DB-API模块是非线程安全的，线程锁会确保使用 PooledDB 是线程安全的。所以你并不需要为此担心，但是你在使用专用连接来改变数据库会话或执行多命令事务时必须小心。

## [该选择哪一个？][Link 22] ##

PersistentDB 和 PooledDB 都是为了重用数据库连接来提高性能，并保持数据库的稳定性。

所以选择何种模块，可以参考上面的解释。 PersistentDB 将会保持一定数量的连接供频繁使用。在这种情况下你总是保持固定数量的连接。如果你的程序频繁的启动和关闭线程，最好使用 PooledDB 。后面将会提到更好的调整，尤其在使用线程安全的DB-API 2模块时。

当然，这两个模块的接口是很相似的，你可以方便的在他们之间转换，并查看哪个更好一些。

# [使用方法][Link 23] #

所有模块的使用方法都很相似，但是在初始化 "Pooled" 和 "Persistent" 时还有有些不同，尤其是DB-API和PyGreSQL之间。

这里只讲解 PersistentDB 和更复杂的 PooledDB 模块。其他模块的细节请参与其文档。使用Python解释器控制台，你可以显示 PooledDB 的文档，如下:

help(PooledDB)

## [PersistentDB][PersistentDB 5] ##

为了使用 PersistentDB 你首先需要通过创建 PersistentDB 的实例来设置一个特定数据库连接的生成器，床底如下参数：

*  dbapi: 需要使用的DB-API 2兼容的数据库模块
 *  maxusage: 一个连接最大允许复用次数(缺省为 0 或 False 意味着无限制的重用)，当达到限制时，将会重新连接数据库
 *  setsession: 一个可选的SQL命令列表可以用于准备会话，如 \["set datestyle to german", ...\]
 *  其他的，你还可以传递用于传递给真实的DB-API 2模块的参数，例如主机名、数据库、用户名、密码等。

举个例子，如果你正在使用 pgdb 作为数据库模块并想要连接本机数据库 mydb ，允许重用1000次:

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

按照如上设置完成了连接生成器之后，你可以按照如下来请求一个连接:

db = persist.connection()

你可以使用这些连接就像使用原始的DB-API 2连接一样。实际上你得到的是一个通过SteadyDB得到的强硬的连接，基于DB-API 2。

关闭一个强硬的连接使用 db.close() ，这在内部实际上被忽略掉了，并且供下次使用。在线程关闭时，也会自动关闭数据库连接。你可以改变这个行为通过 persist.\_closeable 为 True 。

## [PooledDB][PooledDB 5] ##

为了使用 PooledDB 模块，你首先需要通过创建 PooledDB 来设置数据库连接池，传递如下参数：

*  dbapi: 需要使用的DB-API 2模块
 *  mincached : 启动时开启的空连接数量(缺省值 0 意味着开始时不创建连接)
 *  maxcached: 连接池使用的最多连接数量(缺省值 0 代表不限制连接池大小)
 *  maxshared: 最大允许的共享连接数量(缺省值 0 代表所有连接都是专用的)如果达到了最大数量，被请求为共享的连接将会被共享使用。
 *  maxconnections: 最大允许连接数量(缺省值 0 代表不限制)
 *  blocking: 设置在达到最大数量时的行为(缺省值 0 或 False 代表返回一个错误；其他代表阻塞直到连接数减少)
 *  maxusage: 单个连接的最大允许复用次数(缺省值 0 或 False 代表不限制的复用)。当达到最大数值时，连接会自动重新连接(关闭和重新打开)
 *  setsession: 一个可选的SQL命令列表用于准备每个会话，如 \["set datestyle to german", ...\]
 *  其他，你可以设置用于传递到真正的DB-API 2的参数，例如主机名、数据库、用户名、密码等。

举个例子，如果你正在使用 pgdb 作为DB-API模块，并希望连接池中至少有5个连接到数据库 mydb

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

一旦设置好了连接池，你就可以按照如下请求一个连接:

db = pool.connection()

你可以使用这些连接有如原始的DB-API 2一样。而实际使用的是SteadyDB版本的强硬连接。

请注意连接可以与其他线程共享，只要你设置 maxshared 参数为非零，并且DB-API 2模块也允许。如果你想要使用专用连接则使用:

db = pool.connection(0)

如果你不再需要这个连接了，则可以返回给连接池使用 db.close() 。你也可以使用相同的方法获取另一个连接。

*警告：* 在一个多线程环境，不要使用下面的方法:

pool.connection().cursor().execute(...)

这将会导致过早的释放连接以供复用，而且如果是非线程安全还会出错。确保连接对象在你的使用过程中是一直存在的，例如:

db = pool.connection()
    cur = db.cursor()
    cur.execute(...)
    res = cur.fetchone()
    cur.close() # or del cur
    db.close() # or del db

## [在Webware中使用][Webware 3] ##

如果你正在 [Webware for Python][] 的 servlets 中使用DBUtils来存取数据库，你要确保数据库连接生成器只被应用启动一次，而不是每个servlet启动时都创建一个。为了达到这个目的，你可以在模块或类的初始化代码中添加这些代码，或者使用 \_\_init\_\_.py 中的 contextInitialize() 函数。

目录 Examples 是DBUtils发行包的一部分，包含了一个使用示例数据库的Webware的例子，用来跟踪演讲会的出席者(这个例子的主意来自Andrew Kuchling的 "[The Python DB-API][]")。

例子的正文可以通过创建配置文件 Configs/Database.config 来配置，改变例子Examples/DBUtilsExample.py 的缺省参数。这种方式可以设置一个专用数据库的用户名和密码，你也可以选择底层的数据库模块。如果设置了 maxcached ，则例子会使用 "Pooled" 模块，否则会使用 "Persistent" 模块。

# [注意][Link 24] #

如果你正在使用流行的ORM [SQLObject][] ，你并不需要使用DBUtiils，因为他已经内含连接池了。 [SQLObject 2][] ([SQL-API][]) 事实上还从DBUtils这里借用了连接池分层的代码。

# [未来功能][Link 25] #

一些未来会使用的方法：

*  一个连接最大被使用的次数，或一个连接最大活动时间。
 *  创建模块 MonitorDB 和 MonitorPg 运行在单独的线程中，监控连接池中各个共享连接的状态。如果检测到一个损坏的连接，则会自动恢复这个连接。这在很多网站中是很实用的，因为晚上往往要重启数据库服务器。如果不使用监控线程，则用户要等到第二天早上才可以使用。正是因为如此，检测损坏的连接并自动恢复是很有用的。使用了监控线程之后，间断时间在晚上，而且很短。监控线程同样可以配置连接生成器的线程池，并且确保用户到达之前完成。
 *  可选的日志，记录损坏的连接和最大限制。

# [错误报告与回馈][Link 26] #

请将错误报告、补丁、回馈直接发送给作者(使用下面给出的邮件地址)。

如果有Webware相关的问题，可以到邮件列表讨论 [Webware for Python mailing list][] 。

# [链接][Link 27] #

一些相关软件的链接：

*  [DBUtils][]
 *  [Python][]
 *  [Webware for Python][] 框架
 *  Python [DB-API 2][]
 *  [PostgreSQL][] 数据库
 *  [PyGreSQL][] 接口
 *  [SQLObject][] 接口

# [作者列表][Link 28] #

<table>     
 <tbody> 
  <tr> 
   <th>作者:</th> 
   <td>Christoph Zwerschke &lt;<a href="mailto://cito@online.de/" style="color:rgb(51,102,153); text-decoration:none" rel="nofollow">cito@online.de</a>&gt;</td> 
  </tr> 
  <tr> 
   <th>贡献:</th> 
   <td>DBUtils收到了如下朋友的帮助和建议 Ian Bicking, Chuck Esterbrook (Webware for Python), Dan Green (DBTools), Jay Love, Michael Palmer, Tom Schwaller, Geoffrey Talvola and Warren Smith (DbConnectionPool).</td> 
  </tr> 
  <tr> 
   <th>翻译:</th> 
   <td>gashero &lt;<a href="mailto://harry%2Epython@gmail.com/" style="color:rgb(51,102,153); text-decoration:none" rel="nofollow">harry.python@gmail.com</a>&gt;</td> 
  </tr> 
 </tbody> 
</table>

# [版权与许可][Link 29] #

DBUtils是一个自由开源软件，使用 [Open Software License version 2.1][] 许可。

[Synopsis]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#synopsis
[Modules]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#modules
[Download]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#download
[Installation]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#installation
[Installation as a standalone _top-level_ package]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#installation-as-a-standalone-top-level-package
[Installation as a Webware for Python subpackage _plug-in]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#installation-as-a-webware-for-python-subpackage-plug-in
[Requirements]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#requirements
[Functionality]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#functionality
[SimplePooledDB]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#simplepooleddb
[SteadyDB]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#steadydb
[PersistentDB]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#persistentdb
[PooledDB]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#pooleddb
[Which one to use]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#which-one-to-use
[Usage]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#usage
[PersistentDB 1]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#id1
[PooledDB 1]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#id2
[Usage in Webware for Python]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#usage-in-webware-for-python
[Notes]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#notes
[Future]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#future
[Bug reports and feedback]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#bug-reports-and-feedback
[Links]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#links
[Credits]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#credits
[Copyright and License]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/UsersGuide.html#copyright-and-license
[DBUtils]: http://www.webwareforpython.org/DBUtils
[Python]: http://www.python.org/
[Webware for Python]: http://www.webwareforpython.org/
[PyGreSQL]: http://www.pygresql.org/
[PostgreSQL]: http://www.postgresql.org/
[DB-API 2]: http://www.python.org/dev/peps/pep-0249/
[dbdep.gif]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/dbdep.gif
[pgdep.gif]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/pgdep.gif
[http_www.webwareforpython.org_downloads_DBUtils]: http://www.webwareforpython.org/downloads/DBUtils/
[http_www.python.org_pypi_DBUtils]: http://www.python.org/pypi/DBUtils/
[persist.gif]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/persist.gif
[pool.gif]: https://cito.github.io/w4py-olde-docs/Webware/DBUtils/Docs/pool.gif
[The Python DB-API]: http://www.linuxjournal.com/article/2605
[SQLObject]: http://www.sqlobject.org/
[SQLAlchemy]: http://www.sqlalchemy.org/
[SQLObject 2]: http://www.sqlobject.org/2/
[SQL-API]: http://www.sqlobject.org/sqlapi/
[mod_python]: http://www.modpython.org/
[mod_wsgi]: http://code.google.com/p/modwsgi/
[pgpool]: http://pgpool.projects.postgresql.org/
[pgbouncer]: http://pgbouncer.projects.postgresql.org/
[Webware for Python mailing list]: https://lists.sourceforge.net/lists/listinfo/webware-discuss
[Open Software License version 2.1]: http://www.opensource.org/licenses/osl-2.1.php
[Link 1]: http://blog.csdn.net/gashero/article/details/1577187#id2
[Link 2]: http://blog.csdn.net/gashero/article/details/1577187#id3
[Link 3]: http://blog.csdn.net/gashero/article/details/1577187#id4
[Link 4]: http://blog.csdn.net/gashero/article/details/1577187#id5
[Link 5]: http://blog.csdn.net/gashero/article/details/1577187#id6
[Webware]: http://blog.csdn.net/gashero/article/details/1577187#webware
[Link 6]: http://blog.csdn.net/gashero/article/details/1577187#id7
[SimplePooledDB 1]: http://blog.csdn.net/gashero/article/details/1577187#simplepooleddb
[SteadyDB 1]: http://blog.csdn.net/gashero/article/details/1577187#steadydb
[PersistentDB 2]: http://blog.csdn.net/gashero/article/details/1577187#persistentdb
[PooledDB 2]: http://blog.csdn.net/gashero/article/details/1577187#pooleddb
[Link 7]: http://blog.csdn.net/gashero/article/details/1577187#id8
[Link 8]: http://blog.csdn.net/gashero/article/details/1577187#id9
[PersistentDB 3]: http://blog.csdn.net/gashero/article/details/1577187#id10
[PooledDB 3]: http://blog.csdn.net/gashero/article/details/1577187#id11
[Webware 1]: http://blog.csdn.net/gashero/article/details/1577187#id12
[Link 9]: http://blog.csdn.net/gashero/article/details/1577187#id13
[Link 10]: http://blog.csdn.net/gashero/article/details/1577187#id14
[Link 11]: http://blog.csdn.net/gashero/article/details/1577187#id15
[Link 12]: http://blog.csdn.net/gashero/article/details/1577187#id16
[Link 13]: http://blog.csdn.net/gashero/article/details/1577187#id18
[Link 14]: http://blog.csdn.net/gashero/article/details/1577187#id19
[Link 15]: http://blog.csdn.net/gashero/article/details/1577187#id21
[Link 16]: http://lib.csdn.net/base/mysql
[Python 1]: http://lib.csdn.net/base/python
[Link 17]: http://blog.csdn.net/gashero/article/details/1577187#id22
[dbdep.gif 1]: /images/20220713/ba97444979ec4c1f8d687fafb26f7ccb.png
[pgdep.gif 1]: /images/20220713/4d721dcffca546fa845f4321f1aa7cfd.png
[Link 18]: http://blog.csdn.net/gashero/article/details/1577187#id23
[Link 19]: http://blog.csdn.net/gashero/article/details/1577187#id24
[Link 20]: http://blog.csdn.net/gashero/article/details/1577187#id25
[Webware 2]: http://blog.csdn.net/gashero/article/details/1577187#id26
[Link 21]: http://blog.csdn.net/gashero/article/details/1577187#id27
[SimplePooledDB 2]: http://blog.csdn.net/gashero/article/details/1577187#id28
[SteadyDB 2]: http://blog.csdn.net/gashero/article/details/1577187#id29
[PersistentDB 4]: http://blog.csdn.net/gashero/article/details/1577187#id30
[persist.gif 1]: /images/20220713/f768286856b64bd1ad130fb8416585ae.png
[PooledDB 4]: http://blog.csdn.net/gashero/article/details/1577187#id31
[pool.gif 1]: /images/20220713/f80949270eb644468afe5e7dd562d0a3.png
[Link 22]: http://blog.csdn.net/gashero/article/details/1577187#id32
[Link 23]: http://blog.csdn.net/gashero/article/details/1577187#id33
[PersistentDB 5]: http://blog.csdn.net/gashero/article/details/1577187#id34
[PooledDB 5]: http://blog.csdn.net/gashero/article/details/1577187#id35
[Webware 3]: http://blog.csdn.net/gashero/article/details/1577187#id36
[Link 24]: http://blog.csdn.net/gashero/article/details/1577187#id37
[Link 25]: http://blog.csdn.net/gashero/article/details/1577187#id38
[Link 26]: http://blog.csdn.net/gashero/article/details/1577187#id39
[Link 27]: http://blog.csdn.net/gashero/article/details/1577187#id40
[Link 28]: http://blog.csdn.net/gashero/article/details/1577187#id41
[Link 29]: http://blog.csdn.net/gashero/article/details/1577187#id42