Python实现Mysql数据库连接池
Python数据库连接池实例——PooledDB
python的数据库连接池实现----DBUtils
DBUtils 属于WebWare项目的数据库连接池实现模块，用于对数据库连接线程化，使可以安全和有效的访问数据库的模块
DBUtils实际上是一个包含两个子模块的Python包，一个用于连接DB-API 2模块，另一个用于连接典型的PyGreSQL模块。
全局的DB-API 2变量 | |
SteadyDB.py | 用于稳定数据库连接 |
PooledDB.py | 连接池 |
PersistentDB.py | 维持持续的数据库连接 |
SimplePooledDB.py | 简单连接池 |
典型的 PyGreSQL 变量 | |
SteadyPg.py | 稳定PyGreSQL连接 |
PooledPg.py | PyGreSQL连接池 |
PersistentPg.py | 维持持续的PyGreSQL连接 |
SimplePooledPg.py | 简单的PyGreSQL连接池 |
对标准DB-API 2模块的依赖如下图所示：
对典型的PyGreSQL模块依赖如下图所示：
SimplePooledDB
DBUtils.SimplePooledDB 是一个非常简单的数据库连接池实现。他比完善的 PooledDB 模块缺少很多功能。 DBUtils.SimplePooledDB 本质上类似于 MiscUtils.DBPool 这个Webware的组成部分。你可以把它看作一种演示程序。
SteadyDB
DBUtils.SteadyDB 是一个模块实现了"强硬"的数据库连接，基于DB-API 2建立的原始连接。一个"强硬"的连接意味着在连接关闭之后，或者使用次数操作限制时会重新连接。
一个典型的例子是数据库重启时，而你的程序仍然在运行并需要访问数据库，或者当你的程序连接了一个防火墙后面的远程数据库，而防火墙重启时丢失了状态时。
一般来说你不需要直接使用 SteadyDB 它只是给接下来的两个模块提供基本服务， PersistentDB 和 PooledDB 。
PersistentDB
DBUtils.PersistentDB 实现了强硬的、线程安全的、顽固的数据库连接，使用DB-API 2模块。如下图展示了使用 PersistentDB 时的连接层步骤：
当一个线程首次打开一个数据库连接时，一个连接会打开并仅供这个线程使用。当线程关闭连接时，连接仍然持续打开供这个线程下次请求时使用这个已经打开的连接。连接在线程死亡时自动关闭。
简单的来说 PersistentDB 尝试重用数据库连接来提高线程化程序的数据库访问性能，并且他确保连接不会被线程之间共享。
因此， PersistentDB 可以在底层DB-API模块并非线程安全的时候同样工作的很好，并且他会在其他线程改变数据库会话或者使用多语句事务时同样避免问题的发生。
PooledDB
DBUtils.PooledDB 实现了一个强硬的、线程安全的、有缓存的、可复用的数据库连接，使用任何DB-API 2模块。如下图展示了使用 PooledDB 时的工作流程：
如图所示 PooledDB 可以在不同线程之间共享打开的数据库连接。这在你连接并指定 maxshared 参数，并且底层的DB-API 2接口是线程安全才可以，但是你仍然可以使用专用数据库连接而不在线程之间共享连接。除了共享连接以外，还可以设立一个至少 mincached 的连接池，并且最多允许使用 maxcached 个连接，这可以同时用于专用和共享连接池。当一个线程关闭了一个非共享连接，则会返还到空闲连接池中等待下次使用。
如果底层DB-API模块是非线程安全的，线程锁会确保使用 PooledDB 是线程安全的。所以你并不需要为此担心，但是你在使用专用连接来改变数据库会话或执行多命令事务时必须小心。
该选择哪一个？
PersistentDB 和 PooledDB 都是为了重用数据库连接来提高性能，并保持数据库的稳定性。
所以选择何种模块，可以参考上面的解释。 PersistentDB 将会保持一定数量的连接供频繁使用。在这种情况下你总是保持固定数量的连接。如果你的程序频繁的启动和关闭线程，最好使用 PooledDB 。后面将会提到更好的调整，尤其在使用线程安全的DB-API 2模块时。
当然，这两个模块的接口是很相似的，你可以方便的在他们之间转换，并查看哪个更好一些。
使用方法
所有模块的使用方法都很相似，但是在初始化 "Pooled" 和 "Persistent" 时还有有些不同，尤其是DB-API和PyGreSQL之间。
这里只讲解 PersistentDB 和更复杂的 PooledDB 模块。其他模块的细节请参与其文档。使用Python解释器控制台，你可以显示 PooledDB 的文档，如下:
help(PooledDB)
PersistentDB
为了使用 PersistentDB 你首先需要通过创建 PersistentDB 的实例来设置一个特定数据库连接的生成器，床底如下参数：
- creator : 可以使用任意返回 DB-API 2 连接对象的函数活 DB-API 2 兼容的数据库模块。
- maxusage : 一个连接最大允许复用次数(缺省为 0 或 False 意味着无限制的重用)，当达到限制时，将会重新连接数据库
- setsession : 一个可选的SQL命令列表可以用于准备会话，如 ["set datestyle to german", ...]
- creator 函数或生成 DB-API 2 连接的函数，可以接受这里的附加参数，比如主机名、数据库、用户名、密码等等。你也可以选择传递给 creator 的其他参数，并允许提供失败重连和负载均衡。
举个例子，如果你正在使用 pgdb 作为数据库模块并想要连接本机数据库 mydb ，允许重用1000次:
import pgdb # import used DB-API 2 module from DBUtils.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 模块，你首先需要通过创建 PooledDB 来设置数据库连接池，传递如下参数：
- creator : 可以生成 DB-API 2 连接的任何函数或 DB-API 2 兼容的数据库连接模块。
- mincached : 启动时开启的空连接数量(缺省值 0 意味着开始时不创建连接)
- maxcached : 连接池使用的最多连接数量(缺省值 0 代表不限制连接池大小)
- maxshared : 最大允许的共享连接数量(缺省值 0 代表所有连接都是专用的)如果达到了最大数量，被请求为共享的连接将会被共享使用。
- maxconnections : 最大允许连接数量(缺省值 0 代表不限制)
- blocking : 设置在达到最大数量时的行为(缺省值 0 或 False 代表返回一个错误；其他代表阻塞直到连接数减少)
- maxusage : 单个连接的最大允许复用次数(缺省值 0 或 False 代表不限制的复用)。当达到最大数值时，连接会自动重新连接(关闭和重新打开)
- setsession : 一个可选的SQL命令列表用于准备每个会话，如 ["set datestyle to german", ...]
- creator 函数或可以生成连接的函数可以接受这里传入的其他参数，例如主机名、数据库、用户名、密码等。你还可以选择传入creator函数的其他参数，允许失败重连和负载均衡。
举个例子，如果你正在使用 pgdb 作为DB-API模块，并希望连接池中至少有5个连接到数据库 mydb
import pgdb # import used DB-API 2 module from DBUtils.PooledDB import PooledDB pool = PooledDB(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
python连接Mysql数据库：
Python编程中可以使用MySQLdb进行数据库的连接及诸如查询/插入/更新等操作，但是每次连接MySQL数据库请求时，都是独立的去请求访问，相当浪费资源，而且访问数量达到一定数量时，对mysql的性能会产生较大的影响。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。
python的数据库连接池包 DBUtils：
DBUtils是一套python数据库连接池包，并允许对非线程安全的数据库接口进行线程安全包装。DBUtils来自Webware for Python。
DBUtils提供两种外部接口：
- * PersistentDB ：提供线程专用的数据库连接，并自动管理连接。
- * PooledDB ：提供线程间可共享的数据库连接，并自动管理连接。
下载地址：DBUtils 下载解压后，使用python setup.py install 命令进行安装
下面利用MySQLdb和DBUtils建立自己的mysql数据库连接池工具包
在工程目录下新建package命名为:dbConnecttion，并新建module命名为MySqlConn，下面是MySqlConn.py，该模块创建Mysql的连接池对象，并创建了如查询/插入等通用的操作方法。该部分代码实现如下：
# -*- coding: UTF-8 -*-
"""
Created on 2016年5月7日
@author: baocheng
1、执行带参数的ＳＱＬ时，请先用sql语句指定需要输入的条件列表，然后再用tuple/list进行条件批配
２、在格式ＳＱＬ中不需要使用引号指定数据类型，系统会根据输入参数自动识别
３、在输入的值中不需要使用转意函数，系统会自动处理
"""
import MySQLdb
from MySQLdb.cursors import DictCursor
from DBUtils.PooledDB import PooledDB
#from PooledDB import PooledDB
import Config
"""
Config是一些数据库的配置文件
"""
class Mysql(object):
"""
MYSQL数据库对象，负责产生数据库连接 , 此类中的连接采用连接池实现获取连接对象：conn = Mysql.getConn()
释放连接对象;conn.close()或del conn
"""
#连接池对象
__pool = None
def __init__(self):
#数据库构造函数，从连接池中取出连接，并生成操作游标
self._conn = Mysql.__getConn()
self._cursor = self._conn.cursor()
@staticmethod
def __getConn():
"""
@summary: 静态方法，从连接池中取出连接
@return MySQLdb.connection
"""
if Mysql.__pool is None:
__pool = PooledDB(creator=MySQLdb, mincached=1 , maxcached=20 ,
host=Config.DBHOST , port=Config.DBPORT , user=Config.DBUSER , passwd=Config.DBPWD ,
db=Config.DBNAME,use_unicode=False,charset=Config.DBCHAR,cursorclass=DictCursor)
return __pool.connection()
def getAll(self,sql,param=None):
"""
@summary: 执行查询，并取出所有结果集
@param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
@param param: 可选参数，条件列表值（元组/列表）
@return: result list(字典对象)/boolean 查询到的结果集
"""
if param is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql,param)
if count>0:
result = self._cursor.fetchall()
else:
result = False
return result
def getOne(self,sql,param=None):
"""
@summary: 执行查询，并取出第一条
@param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
@param param: 可选参数，条件列表值（元组/列表）
@return: result list/boolean 查询到的结果集
"""
if param is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql,param)
if count>0:
result = self._cursor.fetchone()
else:
result = False
return result
def getMany(self,sql,num,param=None):
"""
@summary: 执行查询，并取出num条结果
@param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
@param num:取得的结果条数
@param param: 可选参数，条件列表值（元组/列表）
@return: result list/boolean 查询到的结果集
"""
if param is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql,param)
if count>0:
result = self._cursor.fetchmany(num)
else:
result = False
return result
def insertOne(self,sql,value):
"""
@summary: 向数据表插入一条记录
@param sql:要插入的ＳＱＬ格式
@param value:要插入的记录数据tuple/list
@return: insertId 受影响的行数
"""
self._cursor.execute(sql,value)
return self.__getInsertId()
def insertMany(self,sql,values):
"""
@summary: 向数据表插入多条记录
@param sql:要插入的ＳＱＬ格式
@param values:要插入的记录数据tuple(tuple)/list[list]
@return: count 受影响的行数
"""
count = self._cursor.executemany(sql,values)
return count
def __getInsertId(self):
"""
获取当前连接最后一次插入操作生成的id,如果没有则为０
"""
self._cursor.execute("SELECT @@IDENTITY AS id")
result = self._cursor.fetchall()
return result[0]['id']
def __query(self,sql,param=None):
if param is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql,param)
return count
def update(self,sql,param=None):
"""
@summary: 更新数据表记录
@param sql: ＳＱＬ格式及条件，使用(%s,%s)
@param param: 要更新的 值 tuple/list
@return: count 受影响的行数
"""
return self.__query(sql,param)
def delete(self,sql,param=None):
"""
@summary: 删除数据表记录
@param sql: ＳＱＬ格式及条件，使用(%s,%s)
@param param: 要删除的条件 值 tuple/list
@return: count 受影响的行数
"""
return self.__query(sql,param)
def begin(self):
"""
@summary: 开启事务
"""
self._conn.autocommit(0)
def end(self,option='commit'):
"""
@summary: 结束事务
"""
if option=='commit':
self._conn.commit()
else:
self._conn.rollback()
def dispose(self,isEnd=1):
"""
@summary: 释放连接池资源
"""
if isEnd==1:
self.end('commit')
else:
self.end('rollback');
self._cursor.close()
self._conn.close()配置文件模块Cnofig,包括数据库的连接信息/用户名密码等：
#coding:utf-8
'''''
Created on 2016年5月7日
@author: baocheng
'''
DBHOST = "localhost"
DBPORT = 33606
DBUSER = "zbc"
DBPWD = "123456"
DBNAME = "test"
DBCHAR = "utf8"创建test模块，测试一下使用连接池进行mysql访问：
#coding:utf-8
'''''
@author: baocheng
'''
from MySqlConn import Mysql
from _sqlite3 import Row
#申请资源
mysql = Mysql()
sqlAll = "SELECT tb.uid as uid, group_concat(tb.goodsname) as goodsname FROM ( SELECT goods.uid AS uid, IF ( ISNULL(goodsrelation.goodsname), goods.goodsID, goodsrelation.goodsname ) AS goodsname FROM goods LEFT JOIN goodsrelation ON goods.goodsID = goodsrelation.goodsId ) tb GROUP BY tb.uid"
result = mysql.getAll(sqlAll)
if result :
print "get all"
for row in result :
print "%s\t%s"%(row["uid"],row["goodsname"])
sqlAll = "SELECT tb.uid as uid, group_concat(tb.goodsname) as goodsname FROM ( SELECT goods.uid AS uid, IF ( ISNULL(goodsrelation.goodsname), goods.goodsID, goodsrelation.goodsname ) AS goodsname FROM goods LEFT JOIN goodsrelation ON goods.goodsID = goodsrelation.goodsId ) tb GROUP BY tb.uid"
result = mysql.getMany(sqlAll,2)
if result :
print "get many"
for row in result :
print "%s\t%s"%(row["uid"],row["goodsname"])
result = mysql.getOne(sqlAll)
print "get one"
print "%s\t%s"%(result["uid"],result["goodsname"])
#释放资源
mysql.dispose()当然，还有很多其他参数可以配置：
- dbapi ：数据库接口
- mincached ：启动时开启的空连接数量
- maxcached ：连接池最大可用连接数量
- maxshared ：连接池最大可共享连接数量
- maxconnections ：最大允许连接数量
- blocking ：达到最大数量时是否阻塞
- maxusage ：单个连接最大复用次数
根据自己的需要合理配置上述的资源参数，以满足自己的实际需要。
至此，python中的mysql连接池实现完了，下次就直接拿来用就好了。
