Implements solid 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.

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 unusuable, 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 solid database connection that can be used by sqlapi.connect.pool to create pooled or persistent connections to a database in a threaded (or unthreaded) environment.

Usage:

You can use the connection constructor connect() in the same way as you would use the same constructor of the DB-API 2 module. The only difference is that you have to specify the DB-API 2 module to be used as a first parameter, and you may also specify a usage limit as the second paramenter (set it to 0 if you prefer unlimited usage), and an optional list of commands that may serve to prepare the session as a third parameter. 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 SolidDB 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 and credit info:

• Contributed as supplement for Webware for Python and PyGreSQL by Christoph Zwerschke in September 2005
• Adapted by Ian Bicking for SQL-API in February 2006

License and disclaimer:

See http://www.webwareforpython.org/Webware/Docs/Copyright.html