Python数据库连接池——PersistentDBPooledDB对⽐及参数解析Python 数据库连接池
⼀. 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 met
hod 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 = Conn()
开端男女主在一起了吗"""
#连接池对象
__pool =None
@staticmethod
def getConn():
数据库恢复"""
@summary: 静态⽅法，从连接池中取出连接
本科一批省控是什么意思@tion
"""
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 __tion()
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 = tion(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:
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 = tion()
cur = db.cursor()
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
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论