Connection Pooling

This chapter gives a tutorial for pooling Exasol connections in Python.

Creating a database connection can be slow. Connection pooling is a way to reuse existing connections instead of opening and closing a new one for every request.

Exasol recommends using SQLAlchemy Connection Pooling for the following reasons:

• SQLAlchemy is very popular, well-maintained, and documented.

• SQLAlchemy Connection Pooling is convenient, advanced, and provides a rich and established feature set.

SQLAlchemy provides different Pool implementations extending the abstract class sqlalchemy.Pool. The most versatile is the QueuePool, limiting the number of open connections.

Creating a Connection Pool

See Connection Pooling in our list of examples.

Stale Connections

For discarding stale connections and freeing the resources allocated by them, SQLALchemy pools offer methods Pool.dispose() and Pool.recreate().

For avoiding stale connections, you can set a timeout with engine option pool_recycle or use option pool_pre_ping which invokes the DBAPI-specific ping() method, or uses SQL statement SELECT 1

Errors

Invalid credentials will raise an error in SQLAlchemy as shown below, chained with __cause__. The password is not revealed.

Initial exception: <class 'sqlalchemy.exc.DBAPIError'>:
  (exasol.driver.websocket._errors.Error)
  (Background on this error at: https://sqlalche.me/e/20/dbapi)
__cause__: <class 'exasol.driver.websocket._errors.Error'>:
__cause__: <class 'pyexasol.exceptions.ExaAuthError'>:
(
    message     =>  Connection exception - authentication failed.
    dsn         =>  127.0.0.1/nocertcheck:8563
    user        =>  sys
    schema      =>
    session_id  =>
    code        =>  08004
)

Events

You can use SQLAlchemy’s Pool Events to react on each time a connection is checked out or handed back to the pool, see Connection Pooling in our list of examples.