U
|
M ±dÅ* ã @ s® d Z zddlmZmZmZ W n( ek
|
rD ddlmZmZmZ Y nX ddlmZ ddlm Z G dd de
|
Z G dd d e Z G d
|
d d e Z G d d d ZG dd dZdS )aÅ PooledPg - pooling for classic PyGreSQL connections.
|
|
Implements a pool of steady, thread-safe cached connections
|
to a PostgreSQL database which are transparently reused,
|
using the classic (not DB-API 2 compliant) PyGreSQL API.
|
|
This should result in a speedup for persistent applications such as the
|
application server of "Webware for Python," without loss of robustness.
|
|
Robustness is provided by using "hardened" SteadyPg connections.
|
Even if the underlying database is restarted and all connections
|
are lost, they will be automatically and transparently reopened.
|
However, since you don't want this to happen in the middle of a database
|
transaction, you must explicitly start transactions with the begin()
|
method so that SteadyPg knows that the underlying connection shall not
|
be replaced and errors passed on until the transaction is completed.
|
|
Measures are taken to make the pool of connections thread-safe
|
regardless of the fact that the classic PyGreSQL pg module itself
|
is not thread-safe at the connection level.
|
|
For more information on PostgreSQL, see:
|
https://www.postgresql.org/
|
For more information on PyGreSQL, see:
|
http://www.pygresql.org
|
For more information on Webware for Python, see:
|
https://webwareforpython.github.io/w4py/
|
|
|
Usage:
|
|
First you need to set up the database connection pool by creating
|
an instance of PooledPg, passing the following parameters:
|
|
mincached: the initial number of connections in the pool
|
(the default of 0 means no connections are made at startup)
|
maxcached: the maximum number of connections in the pool
|
(the default value of 0 or None means unlimited pool size)
|
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", ...]
|
|
Additionally, you have to pass the parameters for the actual
|
PostgreSQL connection which are passed via PyGreSQL,
|
such as the names of the host, database, user, password etc.
|
|
For instance, if you want a pool of at least five connections
|
to your local database 'mydb':
|
|
from dbutils.pooled_pg import PooledPg
|
pool = PooledPg(5, dbname='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
|
classic PyGreSQL API connections. Actually what you get is a
|
proxy class for the hardened SteadyPg version of the connection.
|
|
The connection will not be shared with other threads. 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 or with db.reopen().
|
|
Warning: In a threaded environment, never do the following:
|
|
res = pool.connection().query(...).getresult()
|
|
This would release the connection too early for reuse which may be
|
fatal because 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()
|
res = db.query(...).getresult()
|
db.close() # or del db
|
|
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 given back to the connection pool. To end
|
transactions, use one of the end(), commit() or rollback() methods.
|
|
|
Ideas for improvement:
|
|
* Add a thread for monitoring, restarting (or closing) bad or expired
|
connections (similar to DBConnectionPool/ResourcePool by Warren Smith).
|
* Optionally log usage, bad connections and exceeding of limits.
|
|
|
Copyright, credits and license:
|
|
* Contributed as supplement for Webware for Python and PyGreSQL
|
by Christoph Zwerschke in September 2005
|
* Based on the code of DBPool, contributed to Webware for Python
|
by Dan Green in December 2000
|
|
Licensed under the MIT license.
|
é )ÚQueueÚEmptyÚFullé )Ú __version__)ÚSteadyPgConnectionc @ s e Zd ZdZdS )Ú PooledPgErrorzGeneral PooledPg error.N©Ú__name__Ú
|
__module__Ú __qualname__Ú__doc__© r r úHd:\z\workplace\vscode\pyvenv\venv\Lib\site-packages\dbutils/pooled_pg.pyr w s r c @ s e Zd ZdZdS )ÚInvalidConnectionz Database connection is invalid.Nr r r r r r { s r c @ s e Zd ZdZdS )ÚTooManyConnectionsz*Too many database connections were opened.Nr r r r r r s r c @ sF e Zd ZdZeZdddZdd Zd d
|
Zd d Z d d Z
|
dd Z dS )ÚPooledPgz§Pool for classic PyGreSQL connections.
|
|
After you have created the connection pool, you can use
|
connection() to get pooled, steady PostgreSQL connections.
|
r FNc s || _ _| _| _|p d _|dkr0d}|dkr<d}|dkrHd}|rX||k rX|}|r||k rh|}ddlm}
|
|
|
| _| _nd _t | _
|
fddt |D } | r¾| ¡ ¡ q¬dS )ac Set up the PostgreSQL connection pool.
|
|
mincached: initial number of connections in the pool
|
(0 means no connections are made at startup)
|
maxcached: maximum number of connections in the pool
|
(0 or None means unlimited pool size)
|
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 ..."]
|
reset: how connections should be reset when returned to the pool
|
(0 or None to rollback transcations started with begin(),
|
1 to always issue a rollback, 2 for a complete reset)
|
args, kwargs: the parameters that shall be used to establish
|
the PostgreSQL connections using class PyGreSQL pg.DB()
|
r N)Ú Semaphorec s g | ] } ¡ qS r )Ú
|
connection)Ú.0Úi©Úselfr r Ú
|
<listcomp>À s z%PooledPg.__init__.<locals>.<listcomp>)Ú_argsÚ_kwargsÚ _maxusageÚ _setsessionÚ_resetÚ threadingr Ú _connectionsÚ _blockingr Ú_cacheÚrangeÚpopÚclose) r Z mincachedZ maxcachedZmaxconnectionsÚblockingZmaxusageZ
|
setsessionÚresetÚargsÚkwargsr Zidler r r Ú__init__ s0
|
|
|
zPooledPg.__init__c C s t | j| jdf| j| jS )z-Get a steady, unpooled PostgreSQL connection.T)r r r r r r r r r Ústeady_connectionÄ s
|
ÿÿzPooledPg.steady_connectionc C sP | j r| j | j¡stz| j d¡}W n tk
|
rD | ¡ }Y nX t| |S )z9Get a steady, cached PostgreSQL connection from the pool.r ) r Úacquirer! r r" Úgetr r+ ÚPooledPgConnection©r Úconr r r r É s zPooledPg.connectionc C s zT| j dkr| ¡ n.| j s"|jrDz | ¡ W n tk
|
rB Y nX | j |d¡ W n tk
|
rp | ¡ Y nX | j r| j
|
¡ dS )z*Put a connection back into the pool cache.é r N) r r' Z _transactionÚrollbackÚ Exceptionr" Úputr r% r Úreleaser/ r r r ÚcacheÔ s
|
|
zPooledPg.cachec C sb zB| j d¡}z | ¡ W n tk
|
r. Y nX | jr@| j ¡ W q tk
|
rZ Y q^Y q X q dS )z"Close all connections in the pool.r N)r" r- r% r3 r r5 r r/ r r r r% å s zPooledPg.closec C s& z | ¡ W n tk
|
r Y nX dS )zDelete the pool.N©r% r3 r r r r Ú__del__ó s zPooledPg.__del__)r r r FNNN) r
|
r r r r Úversionr* r+ r r6 r% r8 r r r r r s ý
|
8 r c @ s8 e Zd ZdZdd Zdd Zdd Zdd Zd
|
d Zd S ) r. z.Proxy class for pooled PostgreSQL connections.c C s || _ || _dS )zCreate a pooled DB-API 2 connection.
|
|
pool: the corresponding PooledPg instance
|
con: the underlying SteadyPg connection
|
N)Ú_poolÚ_con)r Úpoolr0 r r r r* s zPooledPgConnection.__init__c C s | j r| j | j ¡ d| _ dS )z Close the pooled connection.N)r; r: r6 r r r r r% s zPooledPgConnection.closec C s" | j r| j ¡ n | j ¡ | _ dS )z Reopen the pooled connection.N)r; Úreopenr: r r r r r r= s zPooledPgConnection.reopenc C s | j rt| j |S tdS )z Proxy all members of the class.N)r; Úgetattrr )r Únamer r r Ú __getattr__ s z PooledPgConnection.__getattr__c C s& z | ¡ W n tk
|
r Y nX dS )z Delete the pooled connection.Nr7 r r r r r8 " s zPooledPgConnection.__del__N) r
|
r r r r* r% r= r@ r8 r r r r r. ý s
|
r. N)r r r r Ú ImportErrorÚqueueÚ r Z steady_pgr r3 r r r r r. r r r r Ú<module> s m z
|
