U
|
M ±dåf ã @ s d Z ddlZddlmZ z eefZW n ek
|
r< eZY nX G dd deZ G dd de Z
|
dd
|
d Z G d d d Z G dd dZ dS )aå SteadyDB - hardened DB-API 2 connections.
|
|
Implements steady connections to a database based on an
|
arbitrary DB-API 2 compliant database interface module.
|
|
The connections are transparently reopened when they are
|
closed or the database connection has been lost or when
|
they are used more often than an optional usage limit.
|
Database cursors are transparently reopened as well when
|
the execution of a database operation cannot be performed
|
due to a lost connection. Only if the connection is lost
|
after the execution, when rows are already fetched from the
|
database, this will give an error and the cursor will not
|
be reopened automatically, because there is no reliable way
|
to recover the state of the cursor in such a situation.
|
Connections which have been marked as being in a transaction
|
with a begin() call will not be silently replaced either.
|
|
A typical situation where database connections are lost
|
is when the database server or an intervening firewall is
|
shutdown and restarted for maintenance reasons. In such a
|
case, all database connections would become unusable, even
|
though the database service may be already available again.
|
|
The "hardened" connections provided by this module will
|
make the database connections immediately available again.
|
|
This approach results in a steady database connection that
|
can be used by PooledDB or PersistentDB to create pooled or
|
persistent connections to a database in a threaded environment
|
such as the application server of "Webware for Python."
|
Note, however, that the connections themselves may not be
|
thread-safe (depending on the used DB-API module).
|
|
For the Python DB-API 2 specification, see:
|
https://www.python.org/dev/peps/pep-0249/
|
For information on Webware for Python, see:
|
https://webwareforpython.github.io/w4py/
|
|
Usage:
|
|
You can use the connection constructor connect() in the same
|
way as you would use the connection constructor of a DB-API 2
|
module if you specify the DB-API 2 module to be used as the
|
first parameter, or alternatively you can specify an arbitrary
|
constructor function returning new DB-API 2 compliant connection
|
objects as the first parameter. Passing just a function allows
|
implementing failover mechanisms and load balancing strategies.
|
|
You may also specify a usage limit as the second parameter
|
(set it to None if you prefer unlimited usage), an optional
|
list of commands that may serve to prepare the session as a
|
third parameter, the exception classes for which the failover
|
mechanism shall be applied, and you can specify whether is is
|
allowed to close the connection (by default this is true).
|
When the connection to the database is lost or has been used
|
too often, it will be transparently reset in most situations,
|
without further notice.
|
|
import pgdb # import used DB-API 2 module
|
from dbutils.steady_db import connect
|
db = connect(pgdb, 10000, ["set datestyle to german"],
|
host=..., database=..., user=..., ...)
|
...
|
cursor = db.cursor()
|
...
|
cursor.execute('select ...')
|
result = cursor.fetchall()
|
...
|
cursor.close()
|
...
|
db.close()
|
|
|
Ideas for improvement:
|
|
* Alternatively to the maximum number of uses,
|
implement a maximum time to live for connections.
|
* Optionally log usage and loss of connection.
|
|
|
Copyright, credits and license:
|
|
* Contributed as supplement for Webware for Python and PyGreSQL
|
by Christoph Zwerschke in September 2005
|
* Allowing creator functions as first parameter as in SQLAlchemy
|
suggested by Ezio Vernacotola in December 2006
|
|
Licensed under the MIT license.
|
é Né )Ú __version__c @ s e Zd ZdZdS )Ú SteadyDBErrorzGeneral SteadyDB error.N©Ú__name__Ú
|
__module__Ú __qualname__Ú__doc__© r
|
r
|
úHd:\z\workplace\vscode\pyvenv\venv\Lib\site-packages\dbutils/steady_db.pyr f s r c @ s e Zd ZdZdS )Ú InvalidCursorzDatabase cursor is invalid.Nr r
|
r
|
r
|
r r j s r Tc O s t | |||||f||S )a A tough version of the connection constructor of a DB-API 2 module.
|
|
creator: either an arbitrary function returning new DB-API 2 compliant
|
connection objects or a DB-API 2 compliant database module
|
maxusage: maximum usage limit for the underlying DB-API 2 connection
|
(number of database operations, 0 or None means unlimited usage)
|
callproc(), execute() and executemany() count as one operation.
|
When the limit is reached, the connection is automatically reset.
|
setsession: an optional list of SQL commands that may serve to prepare
|
the session, e.g. ["set datestyle to german", "set time zone mez"]
|
failures: an optional exception class or a tuple of exception classes
|
for which the failover mechanism shall be applied, if the default
|
(OperationalError, InternalError) is not adequate
|
ping: determines when the connection should be checked with ping()
|
(0 = None = never, 1 = default = when _ping_check() is called,
|
2 = whenever a cursor is created, 4 = when a query is executed,
|
7 = always, and all other bit combinations of these values)
|
closeable: if this is set to false, then closing the connection will
|
be silently ignored, but by default the connection can be closed
|
args, kwargs: the parameters that shall be passed to the creator
|
function or the connection constructor of the DB-API 2 module
|
)ÚSteadyDBConnection)ÚcreatorÚmaxusageÚ
|
setsessionÚfailuresÚpingÚ closeableÚargsÚkwargsr
|
r
|
r Úconnectn s þþþr c @ s¼ e Zd ZdZeZd.ddZdd Zd d
|
Zd d Z d/d dZ
|
dd Z dd Z d0ddZ d1ddZdd Zdd Zd d Zd d Zd d! Zd"d# Zd$d% Zd&d' Zd(d) Zd*d+ Zd,d- ZdS )2r z*A "tough" version of DB-API 2 connections.Nr Tc O s d| _ d| _z|j| _|| _W n~ tk
|
r || _z |j| _W nV tk
|
r z"tj|j | _| jj|krptW n tt
|
fk
|
r d| _Y nX Y nX Y nX z |j | _ W n> tk
|
rè z| jj | _ W n tk
|
râ d| _ Y nX Y nX t | jstd|f |dkrd}t|ts&td|| _|| _|dk r\t|ts\t|ts\td|| _t|trr|nd| _|| _|| | _| _| | ¡ ¡ dS )z%Create a "tough" DB-API 2 connection.NTz %r is not a connection provider.r z$'maxusage' must be an integer value.z)'failures' must be a tuple of exceptions.) Ú_conÚ_closedr Ú_creatorÚ_dbapiÚAttributeErrorÚdbapiÚsysÚmodulesr ÚKeyErrorÚ threadsafetyÚ _threadsafetyÚcallableÚ TypeErrorÚ
|
isinstanceÚbaseintÚ _maxusageÚ_setsession_sqlÚtupleÚ
|
issubclassÚ ExceptionÚ _failuresÚintÚ_pingÚ
|
_closeableÚ_argsÚ_kwargsÚ_storeÚ_create) Úselfr r r r r r r r r
|
r
|
r Ú__init__ sV
|
|
ÿÿzSteadyDBConnection.__init__c C s | S )z4Enter the runtime context for the connection object.r
|
©r3 r
|
r
|
r Ú __enter__À s z SteadyDBConnection.__enter__c G s: |d dkr.|d dkr.|d dkr.| ¡ n| ¡ dS )zExit the runtime context for the connection object.
|
|
This does not close the connection, but it ends a transaction.
|
r Nr é )ÚcommitÚrollback©r3 Úexcr
|
r
|
r Ú__exit__Ä s $
|
zSteadyDBConnection.__exit__c C sÞ | j | j| j}zzz| jj| j kr(tW n> tk
|
rj z
|
|j}W n tk
|
r` d}Y nX |rÊz tj| | _t | jjstW n tt
|
fk
|
r Y nX qf| d¡}|dk r¼d}qb|d|
}qbz |j j}W n tk
|
rî d}Y nX |r`z"tj| | _t | jjstW n tt
|
fk
|
r2 Y nX qf| d¡}|dk rRd}qð|d|
}qðd| _Y nX | j dkrÂz| jj| _ W n: tk
|
rÀ z |j| _ W n tk
|
rº Y nX Y nX | jdkr\z| jj | jjf| _W nv tk
|
rZ z| j j | j jf| _W nH tk
|
rT z|j |jf| _W n tk
|
rN tdY nX Y nX Y nX t| jtrx| jd | _n| j| _| |¡ W nL tk
|
rØ } z,z | ¡ W n tk
|
r Y nX |W 5 d}~X Y nX |S )z3Create a new connection using the creator function.NÚ.r zNCould not determine failure exceptions (please set failures or creator.dbapi).)r r/ r0 r r r r r r r" r ÚrfindZOperationalErrorr! r r+ Z InternalErrorr$ r( Ú_failureÚ _setsessionr* Úclose)r3 ÚconÚmodÚiÚerrorr
|
r
|
r r2 Î s
|
|
|
|
|
|
|
þ
|
þ
|
ÿ
|
ÿ zSteadyDBConnection._createc C s> |dkr| j }| jr:| ¡ }| jD ]}| |¡ q"| ¡ dS )z1Execute the SQL commands for session preparation.N)r r' ÚcursorÚexecuterA )r3 rB rF Zsqlr
|
r
|
r r@ ' s
|
z SteadyDBConnection._setsessionc C s || _ d| _d| _d| _dS )z/Store a database connection for subsequent use.Fr N)r Ú _transactionr Ú_usage)r3 rB r
|
r
|
r r1 1 s zSteadyDBConnection._storec C s: | j s6z| j ¡ W n tk
|
r( Y nX d| _d| _ dS )z§Close the tough connection.
|
|
You can always close a tough connection with this method
|
and it will not complain if you close it more than once.
|
FTN)r r rA r* rH r5 r
|
r
|
r Ú_close8 s zSteadyDBConnection._closeFc C s6 | j s2|s| jr2z | ¡ W n tk
|
r0 Y nX dS )zfReset a tough connection.
|
|
Rollback if forced or the connection was in a transaction.
|
N)r rH r9 r* )r3 Úforcer
|
r
|
r Ú_resetF s
|
zSteadyDBConnection._resetc C sÞ || j @ rÚz4z| j d¡}W n tk
|
r: | j ¡ }Y nX W n@ ttttfk
|
rh d| _ d}d}Y n, tk
|
r~ d}Y nX |dkrd}|rd}|rÖ| jsÖz | ¡ }W n tk
|
r¾ Y nX |
|
¡ | |¡ d}|S dS )a Check whether the connection is still alive using ping().
|
|
If the the underlying connection is not active and the ping
|
parameter is set accordingly, the connection will be recreated
|
unless the connection is currently inside a transaction.
|
Fr NT) r- r r r# r Ú
|
IndexErrorÚ
|
ValueErrorr* rH r2 rJ r1 )r3 r Z reconnectÚaliverB r
|
r
|
r Ú _ping_checkQ s2
|
|
|
|
z SteadyDBConnection._ping_checkc C s | j dkrtd| j S )z8Return the underlying DB-API 2 module of the connection.Nz?Could not determine DB-API 2 module (please set creator.dbapi).)r r r5 r
|
r
|
r r t s
|
|
ÿzSteadyDBConnection.dbapic C s& | j dkr | jdkr tddS | j S )z1Return the thread safety level of the connection.NzTCould not determine threadsafety (please set creator.dbapi or creator.threadsafety).r )r! r r r5 r
|
r
|
r r | s
|
|
ÿz SteadyDBConnection.threadsafetyc C s" | j r| ¡ n| jr | ¡ dS )a] Close the tough connection.
|
|
You are allowed to close a tough connection by default
|
and it will not complain if you close it more than once.
|
|
You can disallow closing connections by setting
|
the closeable parameter to something false. In this case,
|
closing tough connections will be silently ignored.
|
N)r. rJ rH rL r5 r
|
r
|
r rA s
|
|
zSteadyDBConnection.closec O s6 d| _ z | jj}W n tk
|
r& Y n X ||| dS )aH Indicate the beginning of a transaction.
|
|
During a transaction, connections won't be transparently
|
replaced, and all errors will be raised to the application.
|
|
If the underlying driver supports this method, it will be called
|
with the given parameters (e.g. for distributed transactions).
|
TN)rH r Úbeginr )r3 r r rQ r
|
r
|
r rQ s zSteadyDBConnection.beginc C sv d| _ z| j ¡ W n\ | jk
|
rp } z<z | ¡ }W n tk
|
rH Y nX | ¡ | |¡ |W 5 d}~X Y nX dS )z Commit any pending transaction.FN)rH r r8 r+ r2 r* rJ r1 ©r3 rE rB r
|
r
|
r r8 ¦ s
|
zSteadyDBConnection.commitc C sv d| _ z| j ¡ W n\ | jk
|
rp } z<z | ¡ }W n tk
|
rH Y nX | ¡ | |¡ |W 5 d}~X Y nX dS )z Rollback pending transaction.FN)rH r r9 r+ r2 r* rJ r1 rR r
|
r
|
r r9 µ s
|
zSteadyDBConnection.rollbackc C s2 d| _ z | jj}W n tk
|
r& Y nX | dS )zvCancel a long-running transaction.
|
|
If the underlying driver supports this method, it will be called.
|
FN)rH r Úcancelr )r3 rS r
|
r
|
r rS Ä s zSteadyDBConnection.cancelc O s | j j||S )zPing connection.)r r ©r3 r r r
|
r
|
r r Ñ s zSteadyDBConnection.pingc O s | j }|s| d¡ z.| jr2| j| jkr2|s2| j| jj||}W nÄ | jk
|
r } z¢z | ¡ }W n t k
|
rx Y npX z|j||}W n t k
|
r Y n(X |
|
¡ | |¡ |rº|| W Y ¢6S z | ¡ W n t k
|
ræ Y nX |ròd| _ |W 5 d}~X Y nX |S )z)A "tough" version of the method cursor().r7 FN) rH rP r& rI r? r rF r+ r2 r* rJ r1 rA )r3 r r Ú transactionrF rE rB r
|
r
|
r Ú_cursorÕ s>
|
ÿ
|
zSteadyDBConnection._cursorc O s t | f||S )z0Return a new Cursor Object using the connection.)ÚSteadyDBCursorrT r
|
r
|
r rF û s zSteadyDBConnection.cursorc C s& z | ¡ W n tk
|
r Y nX dS )z Delete the steady connection.N)rJ r* r5 r
|
r
|
r Ú__del__ÿ s zSteadyDBConnection.__del__)NNNr T)N)F)r T)r r r r r Úversionr4 r6 r< r2 r@ r1 rJ rL rP r r rA rQ r8 r9 rS r rV rF rX r
|
r
|
r
|
r r s6 þ
|
/
|
Y
|
|
|
|
#
|
&r c @ sl e Zd ZdZdd Zdd Zdd Zdd Zdd d Zd d Z dddZ
|
dd Z dd Z dd Z dd Zd
|
S )rW z&A "tough" version of DB-API 2 cursors.c O sh d| _ d| _|| _|| | _| _| ¡ z|j ||| _ W n" tk
|
r\ td|f Y nX d| _dS )z!Create a "tough" DB-API 2 cursor.NTz %r is not a SteadyDBConnection.F)rV r r r/ r0 Ú _clearsizesr r# )r3 rB r r r
|
r
|
r r4
|
s zSteadyDBCursor.__init__c C s | S )z0Enter the runtime context for the cursor object.r
|
r5 r
|
r
|
r r6 s zSteadyDBCursor.__enter__c G s | ¡ dS )z/Exit the runtime context for the cursor object.N)rA r: r
|
r
|
r r< s zSteadyDBCursor.__exit__c C s
|
|| _ dS )z6Store input sizes in case cursor needs to be reopened.N)Ú _inputsizes)r3 Úsizesr
|
r
|
r Ú setinputsizes! s z SteadyDBCursor.setinputsizesNc C s || j |< dS )z7Store output sizes in case cursor needs to be reopened.N)Ú _outputsizes)r3 ÚsizeÚcolumnr
|
r
|
r Ú setoutputsize% s z SteadyDBCursor.setoutputsizec C s g | _ i | _dS )z$Clear stored input and output sizes.N)r[ r^ r5 r
|
r
|
r rZ ) s zSteadyDBCursor._clearsizesc C sX |dkr| j }| jr | | j¡ | j ¡ D ](\}}|dkrF| |¡ q*| ||¡ q*dS )z7Set stored input and output sizes for cursor execution.N)rV r[ r] r^ Úitemsra )r3 rF r` r_ r
|
r
|
r Ú _setsizes. s zSteadyDBCursor._setsizesc C s4 | j s0z| j ¡ W n tk
|
r( Y nX d| _ dS )z^Close the tough cursor.
|
|
It will not complain if you close it more than once.
|
TN)r rV rA r* r5 r
|
r
|
r rA : s zSteadyDBCursor.closec s fdd}|S )z4Return a "tough" version of the given cursor method.c s& d¡}j}|j}|s$| d¡ zP|jrB|j|jkrB|sB|j|rN ¡ tj }|| |}|rp
|
¡ W n |j k
|
r } zv|sTz|j j j }W n tk
|
rº Y nX z2|rÌ |¡ t| }|| |}|rì
|
¡ W n tk
|
r Y n,X ¡ |_ | jd7 _| W Y ¢ÔS z | ¡ W n tk
|
rR Y nX z | ¡ } W n tk
|
rx Y nvX z| jj j }W n tk
|
r¦ Y n$X |rÒ ¡ | ¡ | | ¡ |_ |d }
|
z6|rè |¡ t| } | | |}|r
|
|
¡ W nJ |jk
|
r, d} |}
|
Y n0 tk
|
rV } z d} |}
|
W 5 d }~X Y nX d} | r¦ ¡ | ¡ | | ¡ |_ | jd7 _|
|
r|
|
| W Y ¢^S z | ¡ W n tk
|
rÈ Y nX z | ¡ W n tk
|
rì Y nX |rúd_|W 5 d }~X Y nX | jd7 _|S d S )NrG é r FT)Ú
|
startswithr rH rP r& rI r? rc ÚgetattrrV rZ r+ r/ r0 r* rA r2 rF rJ r1 Ú __class__) r r rG rB rU ÚmethodÚresultrE Zcursor2Zcon2Zerror2Zmethod2Zuse2©Únamer3 r
|
r Ú tough_methodH sÀ
|
|
ÿ
|
ÿ
|
|
|
ÿ
|
|
|
|
|
z6SteadyDBCursor._get_tough_method.<locals>.tough_methodr
|
)r3 rk rl r
|
rj r Ú_get_tough_methodF s cz SteadyDBCursor._get_tough_methodc C s0 | j r(| d¡r| |¡S t| j |S ntdS )z4Inherit methods and attributes of underlying cursor.)rG ÚcallN)rV re rm rf r )r3 rk r
|
r
|
r Ú __getattr__ s
|
|
|
zSteadyDBCursor.__getattr__c C s& z | ¡ W n tk
|
r Y nX dS )zDelete the steady cursor.N)rA r* r5 r
|
r
|
r rX ¸ s zSteadyDBCursor.__del__)N)N)r r r r r4 r6 r< r] ra rZ rc rA rm ro rX r
|
r
|
r
|
r rW s
|
|
g rW )NNNr T)r r Ú r r, Úlongr% Ú NameErrorr* r r r r rW r
|
r
|
r
|
r Ú<module> s$ [
|
þ
|
}
|
