DBUtils_PooledDB

DBUtils是一套用于管理数据库连接池的包，为高频度高并发的数据库访问提供更好的性能，可以自动管理连接对象的创建和释放。最常用的两个外部接口是PersistentDB 和 PooledDB，前者提供了单个线程专用的数据库连接池，后者则是进程内所有线程共享的数据库连接池。

安装所需包：

easy_install -U MySQL-pythoneasy_install -U DBUtils

 

测试代码：

import sysimport threadingimport MySQLdbimport DBUtils.PooledDBconnargs = { "host":"localhost", "user":"user1", "passwd":"123456", "db":"test" };def test(conn): try: cursor = conn.cursor() count = cursor.execute("select * from users") rows = cursor.fetchall() for r in rows: pass finally: conn.close()def testloop(): print "testloop" for i in range(1000): conn = MySQLdb.connect(**connargs) test(conn)def testpool(): print "testpool" pooled = DBUtils.PooledDB.PooledDB(MySQLdb, **connargs) for i in range(1000): conn = pooled.connection() test(conn)def main(): t = testloop if len(sys.argv) == 1 else testpool for i in range(10): threading.Thread(target = t).start()if __name__ == "__main__": main()

 

看看 10 线程的测试结果。

$ time ./main.pytestlooptestlooptestlooptestlooptestlooptestlooptestlooptestlooptestlooptestloopreal 0m4.471suser 0m0.570ssys 0m4.670s$ time ./main.py -ltestpooltestpooltestpooltestpooltestpooltestpooltestpooltestpooltestpooltestpoolreal 0m2.637suser 0m0.320ssys 0m2.750s

 

虽然测试方式不是很严谨，但从测试结果还是能感受到 DBUtils 带来的性能提升。当然，我们我们也可以在 testloop() 中一直重复使用一个不关闭的 Connection，但这却不适合实际开发时的情形。

DBUtils 提供了几个参数，便于我们更好地调整资源利用。

DBUtils.PooledDB.PooledDB(self, creator, mincached=0, maxcached=0, maxshared=0, maxconnections=0, blocking=False, maxusage=None, setsession=None, failures=None, *args, **kwargs)Docstring: Set up the DB-API 2 connection pool. creator: either an arbitrary function returning new DB-API 2 connection objects or a DB-API 2 compliant database module mincached: initial number of idle connections in the pool (0 means no connections are made at startup) maxcached: maximum number of idle connections in the pool (0 or None means unlimited pool size) maxshared: maximum number of shared connections (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 (0 or None means an arbitrary 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, otherwise an error will be reported) maxusage: maximum number of reuses of a single connection (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: optional list of SQL commands that may serve to prepare the session, e.g. ["set datestyle to ...", "set time zone ..."] 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 args, kwargs: the parameters that shall be passed to the creator function or the connection constructor of the DB-API 2 module

 

DBUtils 仅提供给了连接池管理，实际的数据库操作依然是由符合 DB-API 2 标准的目标数据库模块完成的。

相关参考资料：

《DBUtils User's Guide》
《Python Database API Specification v2.0》
《DBUtils超快速入门指南》
《在基于Pylons的服务器上测试使用DBUtils前后的性能对比》