Simplifying Python applications by using named connection pools – Christopher Jones

Python-oracledb 3.0 allows you to name pools when they are created. You can access them later by that name instead of having to pass around a pool handle. This feature is very helpful when your application spans many code files, or consists of independent libraries.

Multi-user (and some single-user) database applications should almost always use a driver connection pool. This has performance, scalability, and reliability benefits. Check out my previous posts on this topic are A driver connection pool, a DRCP pool, or an Implicit Connection Pool? and Always Use Connection Pools — and How.

But when your application spans multiple files, it can be tricky to pass the pool handle between your code modules. In python-oracledb 3.0 we introduced a driver connection pool cache to simplify your life. You can use the pool cache in both python-oracledb Thin and Thick modes with all the Oracle Database versions that python-oracledb supports. The same cache concept has already proven itself in our node-oracledb driver.

To put pool caching into practice, consider the new code connection_pool_pc.py which is a variant of the sample connection_pool.py. (Follow those links to see the full files).

The original connection_pool.py code creates a pool and returns its handle to the rest of the application:

    pool = oracledb.create_pool(
        user=sample_env.get_main_user(),
        password=sample_env.get_main_password(),
        dsn=sample_env.get_connect_string(),
        params=sample_env.get_pool_params(),
        min=pool_min,
        max=pool_max,
        increment=pool_inc,
        session_callback=init_session,
    )

    return pool

The new code in connection_pool_pc.py adds a pool_alias=my_pool_alias parameter to create_pool(). It doesn’t retain, or use, the pool handle returned by create_pool():

    my_pool_alias = 'mypool'

    oracledb.create_pool(
pool_alias=my_pool_alias,
        user=sample_env.get_main_user(),
        password=sample_env.get_main_password(),
        dsn=sample_env.get_connect_string(),
        params=sample_env.get_pool_params(),
        min=pool_min,
        max=pool_max,
        increment=pool_inc,
        session_callback=init_session,
    )

Every time a connection is needed from the pool, the old code:

with pool.acquire() as connection:

is replaced to access the pool directly from the oracledb module:

with oracledb.connect(pool_alias=my_pool_alias) as connection:

The full diff between the files is:

71a72,73
> my_pool_alias = 'mypool'
> 
88c90,91
<     pool = oracledb.create_pool(
---
>     oracledb.create_pool(
>         pool_alias=my_pool_alias,
99d101
<     return pool
101d102
< 
128c129
<     with pool.acquire() as connection:
---
>     with oracledb.connect(pool_alias=my_pool_alias) as connection:
172c173
<     with pool.acquire() as connection:
---
>     with oracledb.connect(pool_alias=my_pool_alias) as connection:
190c191
<     with pool.acquire() as connection:
---
>     with oracledb.connect(pool_alias=my_pool_alias) as connection:
201c202
<     pool = start_pool()
---
>     start_pool()

The files run identically.

The benefit of pool caching is that modules and libraries that access a pool only need to agree on a name (or names — if you have multiple pools). After importing oracledb, each part of the code can access a pool directly off the imported oracledb module by using the agreed name.

You can also pass options to oracledb.connect() that you might have previously passed to pool.acquire(). The documented example is when you are using a heterogeneous pool where each connection could be a different user. In

Truncated by Planet PHP, read more at the original (another 1879 bytes)