Python 数据库连接池——PersistentDB&PooledDB对比及参数解析

Python 数据库连接池

官方文档：https://webwareforpython.github.io/DBUtils/UsersGuide.html

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

主要分为PooledDB和PersistentDB两个模块

二. PersistentDB

介绍

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.

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.

简单来说：此模块的好处在于线程安全：为每个线程建立一个链接，线程之间的链接不可共享。每一个线程每次获取的链接使用的都是同一个链接，一旦线程die了，链接就关闭了。

三. PooledDB

1. 介绍

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.

简单来说，当有线程想要获取一个链接时，如果要求的是非共享链接，直接就拿出一个空闲链接返回；而如果是共享链接，由于有一个maxshared参数限制共享链接的总个数，所以要先看看共享池满没满（达没达到maxshared上限），如果满了就报错/等待（这取决于block参数的设定）。

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.

当close一个链接的时候，如果这个链接没有被其他线程共享，就把链接返回线程池。此时线程池多出来一个空闲链接，如果达到了maxcached的上限，就销毁这个链接；否则就等待继续利用。

2. 创建参数

• creator: either an arbitrary function returning new DB-API 2 connection objects or a DB-API 2 compliant database module 一般python3里面使用pymysql

• mincached : 连接池里面初始的空闲链接（默认为0，代表开始时没有空闲连接）

• maxcached: 连接池里面最大的空闲连接数量（默认为0/None，代表无限制）

• maxshared: 最大的允许共享的连接数（默认为0/None，代表所有连接都不可共享）

  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: 最大的允许的连接数（默认为0/None，代表多少连接都行）

• blocking: 确定当超过最大连接数后的行为，如果为true，则阻塞等待连接减少，默认为报告错误（error）

• maxusage: 单个连接最大可以被复用的次数（默认为0/None，代表不限制复用）

  当达到最大的被复用次数后，连接自动重置（关闭/重新打开）

• setsession: an optional list of SQL commands that may serve to prepare the session, e.g. [“set datestyle to german”, …]

• reset: 当连接放回连接池时，是否每次都调用 rollback 以保证事务终止，为 False 或 None 则不额外调用 rollback 方法

  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.

3. 使用例子

class MysqlController(object):
    """
    MYSQL数据库对象，负责产生数据库连接 , 此类中的连接采用连接池实现获取连接对象：conn = MysqlController.getConn()
    """
    #连接池对象
    __pool = None

    @staticmethod
    def getConn():
        """
        @summary: 静态方法，从连接池中取出连接
        @return pymysql.connection
        """
        if MysqlController.__pool is None:
            __pool = PooledDB(
              creator=pymysql, 
              mincached=1 , #最小空闲连接数1
              maxcached=20 ,   #最大空闲连接数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()

4. 注意（有关线程安全）

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

四. 比较

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 use PooledDB. The latter will also allow more fine-tuning, particularly if you are using a thread-safe DB-API 2 module.

有稳定个数的线程用PersistentDB，

经常开关线程用PooledDB