cx_Oracle 7.1, the extremely popular Oracle Database interface for Python, is now Production on PyPI.
Another great release of cx_Oracle is available from PyPI, this time with a focus on session pooling. There were also a number of incremental improvements and fixes, all detailed in the release notes.
When applications use a lot of connections for short periods, Oracle recommends using a session pool for efficiency. The session pool is a pool of connections to Oracle Database. (For all practical purposes, a 'session' is the same as a 'connection'). Many applications set some kind of state in connections (e.g. using ALTER SESSION commands to set the date format, or a time zone) before executing the 'real' application SQL. Pooled connections will retain this state after they have been released back to the pool with conn.close() or pool.release(), and the next user of the connection will see the same state. However, because the number of connections in a pool can vary over time, or connections in the pool can be recreated, there is no guarantee a subsequent pool.acquire() call will return a database connection that has any particular state. In previous versions of cx_Oracle, any ALTER SESSION commands had to be run after each and every pool.acquire() call. This added load and reduced system efficiency.
In cx_Oracle 7.1, a new cx_Oracle.SessionPool() option sessionCallback reduces configuration overhead, as featured in the three scenarios shown below. Further details on session callbacks can be found in my post about the equivalent feature set in node-oracledb.
When all connections in a pool should have exactly the same state, you can set sessionCallback to a Python function:
def InitSession(conn, requestedTag): cursor = conn.cursor() cursor.execute("alter session ....") pool = cx_Oracle.SessionPool(un, pw, connectstring, sessionCallback=InitSession, threaded=True) . . .
The function InitSession will be called whenever a pool.acquire() call selects a newly created database connection in the pool that has not been used before. It will not be called if the connection in the pool was previously used by the application. It is called before pool.acquire() returns. The big advantage is that it saves the cost of altering session state if a previous user of the connection has already set it. Also the current caller of pool.acquire() can always assume the correct state is set.
If you need to execute more than one SQL statement in the callback, use a PL/SQL block to reduce round-trips between Python and the database:
def InitSession(conn, requestedTag): cursor = conn.cursor() cursor.callproc( """begin execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN'; -- other SQL statements could be put here end;""")
The requestedTag parameter is shown in the next section.
When callers of pool.acquire() need different session states, for example if they need different time zones, then session tagging can be used in conjunction with sessionCallback. See SessionCallback.py for a runnable example.
A tag is a semi-arbitrary string that you assign to connections before you release them to the pool. Typically a tag represents the session state you have set in the connection. Note that when cx_Oracle is using Oracle Client 12.2 (or later) libraries then tags are multi-property and must be in the form of one or more "name=value" pairs, separated by a semi-colon. You can choose the property names and values.
Subsequent pool.acquire() calls may request a connection be returned that has a particular tag already set, for example:
conn = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE")
This will do one of:
Select an existing connection in the pool that has the requested tag. In this case, the sessionCallback function is NOT called.
Select a new, previously unused connection in the pool (which will have no tag) and call the sessionCallback function.
Will select a previously used connection with a different tag. The existing session and tag are cleared, and the sessionCallback function is called.
An optional matchanytag parameter can be used:
conn = pool.acquire(tag="TIME_ZONE=MST", matchanytag=True)
In this case, a connection that has a different tag may be selected from the pool and the sessionCallback function will be invoked.
When the callback is executed, it can compare the requested tag with the tag that the connection currently has. It can then set the desired connection state and update the connection's tag to represent that state. The connection is then returned to the application by the pool.acquire() call:
def InitSession(conn, requestedTag): # Display the requested and actual tags print("InitSession(): requested tag=%r, actual tag=%r" % (requestedTag, conn.tag)) # Compare the requested and actual tags and set some state . . . cursor = conn.cursor() cursor.execute("alter session ....") # Assign the requested tag to the connection so that when the connection # is closed, it will automatically be retagged conn.tag = requestedTag
The sessionCallback function is always called before pool.acquire() returns.
The underlying Oracle Session Pool tries to optimally select a connection from the pool. Overall, a pool.acquire() call will try to return a connection which has the requested tag string or tag properties, therefore avoiding invoking the sessionCallback function.
When using Oracle client libraries 12.2 (or later) the sessionCallback can alternatively be a PL/SQL procedure. Instead of setting sessionCallback to a Python function, set it to a string containing the name of a PL/SQL procedure, for example:
pool = cx_Oracle.SessionPool(un, pw, connectstring, sessionCallback="myPlsqlCallback", threaded=True)
The procedure has the declaration:
PROCEDURE myPlsqlCallback ( requestedTag IN VARCHAR2, actualTag IN VARCHAR2 );
For an example PL/SQL callback, see SessionCallbackPLSQL.py.
The PL/SQL procedure is called only when the properties in the requested connection tag do not match the properties in the actual tag of the connection that was selected from the pool. The callback can then change the state before pool.acquire() returns to the application.
When DRCP connections are being used, invoking the PL/SQL callback procedure does not need round-trips between Python and the database. In comparison, a complex (or badly coded) Python callback function could require lots of round-trips, depending on how many ALTER SESSION or other SQL statements it executes.
A PL/SQL callback can also be used without DRCP; in this case invoking the callback requires just one round-trip.
cx_Oracle 7.1 is a solid release which should particularly please session pool users.
Home page: oracle.github.io/python-cx_Oracle/index.html
Installation instructions: cx-oracle.readthedocs.io/en/latest/installation.html
Release Notes: cx-oracle.readthedocs.io/en/latest/releasenotes.html
Source Code Repository: github.com/oracle/python-cx_Oracle
Facebook group: https://www.facebook.com/groups/418337538611212/