DBUtils User's Guide及中文

最新推荐文章于 2021-08-04 10:34:37 发布
最新推荐文章于 2021-08-04 10:34:37 发布
阅读量2.3k 收藏 2
点赞数 1
CC 4.0 BY-SA版权
分类专栏： python 文章标签： DBUtils
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/zhaihaifei/article/details/53996866

DBUtils User's Guide

Version: 1.1
Released: 08/14/11
Translations: English German

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.

Universal DB-API 2 variant
SteadyDB.py Hardened DB-API 2 connections
PooledDB.py Pooling for DB-API 2 connections
PersistentDB.py Persistent DB-API 2 connections
SimplePooledDB.py Simple pooling for DB-API 2
Classic PyGreSQL variant
SteadyPg.py Hardened classic PyGreSQL connections
PooledPg.py Pooling for classic PyGreSQL connections
PersistentPg.py Persistent classic PyGreSQL connections
SimplePooledPg.py Simple pooling for classic PyGreSQL

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/

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

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 or 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.

Credits

Author: Christoph Zwerschke <cito@online.de>
Contributions: 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.
219-6-3Google浏览器书签备份
qq_43046057的博客
06-03 3889
WAF 创建时间 2019-03-11 12:45:27 最后修改 2019-04-03 14:02:50>> 书签名称 链接 添加时间 WEB漏洞测试（二）——HTML注入 & XSS攻击 - 优快云博客 http://www.secsky.cn/216.html 2018-05-15 10:45:24 ModSecurity介绍 - 优快云博客...
Apache Software Foundation Index: Project Listing
H.B.Hooper
03-18 4142
http://projects.apache.org/indexes/quick.html HomeIndexes AlphabeticalCategoriesLanguagePMCProject ListingReleasesStandards FeedsDOAP Files GuidelinesCreate a DOAP FileDO
DBUtils文档+源码
09-25
DButils的文档和源代码都有了，不会的查文档，关联源代码等。
Apache Commons DbUtils使用手册
chszs的专栏
01-23 7975
Apache Commons DbUtils使用手册作者：chszs，转载需注明。博客主页：http://blog.youkuaiyun.com/chszs一、介绍DBUtils是个小巧的JDBC轻量级封装的工具包，其最核心的特性是在JDBC的基础上做了一层封装，主要是对结果集的封装，可以直接将查询出来的结果集封装成JavaBean，旨在简化JDBC代码混乱与重复。JDBC代码开发，存在很多难点：1）操作过程
DbUtils技术文档.pdf
09-24
　　DbUtils是一个小类库，旨在使工作的JDBC更加容易。 JDBC的资源释放代码是琐碎的、容易出错的工作，使用这些类可以让所有的善后清理工作从您的代码分离，而把你真正想用JDBC做的事与摆在首位：查询和更新数据。
DBUtils 用户指南
在水一方
06-19 1240
版本: 0.9.4 发行版: 07/07/07 翻译: English German Chinese
apache dbutils中文说明
12-04
apache dbutils中文说明
Commons-DbUtils与Spring整合：一站式数据库操作解决方案，轻松搞定项目集成
[Commons-DbUtils与Spring整合：一站式数据库操作解决方案，轻松搞定项目集成](https://s3.amazonaws.com/stackabuse/media/guide-spring-data-jpa-2.png) # 1. 基础概念与搭建环境 在当今的IT行业中，数据操作是...
总结Android各类常用三方框架
whj9073的专栏
11-29 2190
1.下拉刷新 Android-Ptr-Comparison-Android 下拉刷新开源库对比，非常nice！！ Android-PullToRefresh- 最经典、最多人用的下拉刷新、加载更多。 PullDownListView- 一个下拉刷新的控件，实现了仿微信下拉中眼睛动画的效果。 DragTopLayout- 实现整个layout下拉刷新。 ZrcListView- 一个顺滑又...
Vue基本案例使用以及Vue+ElementUI+cp30连接池实现学生管理系统的增删改查
qq_42662411的博客
10-09 1392
Vue基本案例使用以及Vue+ElementUI+cp30连接池实现学生管理系统的增删改查 1-Vue基本用法 1.1 V-model进行双向事件的绑定 <!--1-v-model用来渲染 进行事件的交互--> <input type="text" v-model="message"> {{message}} 1.2 V-on:事件名称 进行事件的绑定 简化成@click=“方法名称()” <!-
commons-dbutils-1.7-API文档-中文版.zip
07-07
赠送jar包：commons-dbutils-1.7.jar； 赠送原API文档：commons-dbutils-1.7-javadoc.jar； 赠送源代码：commons-dbutils-1.7-sources.jar； 赠送Maven依赖信息文件：commons-dbutils-1.7.pom； 包含翻译后的API文档：commons-dbutils-1.7-javadoc-API文档-中文(简体)版.zip； Maven坐标：commons-dbutils:commons-dbutils:1.7； 标签：commons、dbutils、中文文档、jar包、java； 使用方法：解压翻译后的API文档，用浏览器打开“index.html”文件，即可纵览文档内容。 人性化翻译，文档中的代码和结构保持不变，注释和说明精准翻译，请放心使用。
DBUtils工具包（Jar包及帮助文档）
05-08
Commons DbUtils 是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化 JDBC应用程序的开发，同时也不会影响程序的性能。亲测可用，放心使用！
commons-dbutils-1.7.jar中文-英文对照文档.zip
03-08
注：下文中的 *** 代表文件名中的组件名称。 # 包含： 中文-英文对照文档：【***-javadoc-API文档-中文(简体)-英语-对照版.zip】 jar包下载地址：【***.jar下载地址(官方地址+国内镜像地址).txt】 Maven依赖：【***.jar Maven依赖信息(可用于项目pom.xml).txt】 Gradle依赖：【***.jar Gradle依赖信息(可用于项目build.gradle).txt】 源代码下载地址：【***-sources.jar下载地址(官方地址+国内镜像地址).txt】 # 本文件关键字： 中文-英文对照文档,中英对照文档,java,jar包,Maven,第三方jar包,组件,开源组件,第三方组件,Gradle,中文API文档,手册,开发手册,使用手册,参考手册 # 使用方法： 解压 【***.jar中文文档.zip】，再解压其中的 【***-javadoc-API文档-中文(简体)版.zip】，双击 【index.html】 文件，即可用浏览器打开、进行查看。 # 特殊说明： ·本文档为人性化翻译，精心制作，请放心使用。 ·本文档为双语同时展示，一行原文、一行译文，可逐行对照，避免了原文/译文来回切换的麻烦； ·有原文可参照，不再担心翻译偏差误导； ·边学技术、边学英语。 ·只翻译了该翻译的内容，如：注释、说明、描述、用法讲解 等； ·不该翻译的内容保持原样，如：类名、方法名、包名、类型、关键字、代码 等。 # 温馨提示： （1）为了防止解压后路径太长导致浏览器无法打开，推荐在解压时选择“解压到当前文件夹”（放心，自带文件夹，文件不会散落一地）； （2）有时，一套Java组件会有多个jar，所以在下载前，请仔细阅读本篇描述，以确保这就是你需要的文件；
_dbutils使用说明.doc
05-12
_dbutils使用说明,适合初学者学习dbutils。
dbutils文档和源码及jar包
04-30
Common Dbutils是操作数据库的组件，对传统操作数据库的类进行二次封装，可以把结果集转化成List。 补充一下，传统操作数据库的类指的是JDBC（java database connection：java数据库连接，java的数据库操作的基础API。）。 DBUtils是java编程中的数据库操作实用工具，小巧简单实用，
dbutil的中文问题
epeaktop的专栏
11-20 300
必须在中文的后面加上划红线的参数
java dbutils 中文api_Java精选笔记_DBUtils工具
weixin_42519488的博客
02-28 184
DBUtils工具API介绍为了更加简单地使用JDBC，Apache组织提供了一个工具类库commons-dbutils组件。该组件实现了对JDBC的简单封装，可以在不影响性能的情况下极大简化JDBC的编码工作量。commons-dbutils的核心是两个类和一个接口：DBUtils类、QueryRunner类、ResultSetHandler接口DBUtils类主要为如关闭连接、装载JDBC驱动...
DButil（增，删，改，查）整理
Singeular的博客
05-05 448
新手入门 API关键字： QueryRunner insert(String sql,ResultSetHandle,Object… args):添加操作 query(String sql,ResultSetHandle,Object… args):查询操作 update(String sql,Object… args) ResultSetHandler：把ResultSet转对象 BeanH...
python dbutils连接池查询中文是字节类型转换正常类型 问题记录
it_is_arlon的博客
08-04 364
#!/usr/bin/env python # -*- coding:utf-8 -*- """ @author: arlon @date: 2021/8/2 @time: 18:03 @desc: """ import sys sys.path.append("./") import pymysql from dbutils.pooled_db import PooledDB from utility import db_config as config # 数据库连接编码 DB_CHARSET =
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值