Python-oracledb 2.5 brings greater flexibility to Oracle Database apps

Python-oracledb 2.5, the extremely popular Oracle Database interface for Python, is now on PyPI.

Photo by Ankush Minda on Unsplash

Python-oracledb 2.5 is available on PyPi. This has been a big release — check the release notes for all the improvements and bug fixes.

Python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features. By default, it is a ‘Thin’ driver that is immediately usable without needing any additional install e.g. no Instant Client is required. Python-oracledb is the new name for the cx_Oracle driver.

Release Highlights

A new register_protocol() method

The new register_protocol() method lets you add your own custom pre-connection hook function that can parse connect() or create_pool() connection strings (in Thin mode) and modify them for the driver to use when the connection to the database is actually created.

The canonical use case is for LDAP in Thin mode. You can register your own custom function to handle the “ldap” protocol. When your application later calls:

connect(. . ., dsn="ldap://ldapserver/dbname,cn=OracleContext,dc=dom,dc=com") 

then your custom hook function will be invoked. Your function can parse the LDAP connection string and use your favorite Python LDAP module to access the LDAP server to retrieve the database connection string that will then internally be used by python-oracledb to complete the connection.

It’s simpler than it sounds. See the documentation and sample LDAP hook function in LDAP Directory Naming.

Pipeline warnings and column metadata

Pipelining allows an application to send multiple, independent statements to Oracle Database with one call. The database can be kept busy without waiting for the application to receive a result set and send the next statement. And while the database processes the pipeline of statements, the application can continue with non-database work. Pipelining was my favorite feature of our previous release. See my blogs Pipelined database operations with python-oracledb 2.4 and Pipelined database operation performance redux with python-oracledb: very impressive.

Now, in python-oracledb 2.5, you can access any warning that occurred in a pipelined operation, for example if an operation is to create a stored PL/SQL procedure and it results in a “Procedure created with compilation errors” warning. See samples/pipelining_error.py for an example. This makes pipelines even more useful for schema creation scripts.

Additionally, you can now get column metadata for queries executed by the pipeline. This lets you check, or use, column names and types. See samples/pipelining_basic.py.

Pipelining is still technically marked as ‘pre-release’ in python-oracledb 2.5 so we can gather feedback and potentially make breaking changes to the API. Nothing has been raised yet that makes me think we will need to alter it, but yell out if you have ideas or needs.

[Update: I posted a blog showing the new attributes in action].

Settable V$ attributes

The values of program, terminal, machine, osuser, and driver_name can now be set in oracledb.defaults or during connection in Thin mode. This functionality is not available in Thick mode.

We’ve seen a number of requests for users to be able to set specific values of V$ views like V$SESSION. These requests seem to be coming from Java-centric environments since JDBC already allows the relevant, default system values to be overridden. If you want to use Python, and your existing data model or logging protocol needs it, you now can change the values. Note my general recommendation is to not change the defaults, but instead to make use of application contexts (see lower), or use the client_identifier, clientinfo, dbop, module and action settings (see Oracle Database End-to-End Tracing). All of these alternatives are available in Thin and Thick modes, and are designed for you to set your own application metadata values.

Database size attribute max_identifier_length

A new read-only connection attribute max_identifier_length specifies the maximum database identifier length allowed by the current database. This is available in python-oracledb Thin and Thick modes. Generic frameworks might find this useful, since it saves the cost of a round-trip query at application startup.

Attributes session_id and serial_num

New read-only connection attributes show the session identifier and serial number of a connection in Thin mode. These are not available in Thick mode. These values are mostly useful for testing whether database sessions used by application connections are reused or new. You may find the values similarly useful.

One big proviso is that if you use Database Resident Connection Pooling, then the values in the attributes won’t be updated until after a round-trip occurs after a connection is acquired by the application. Caveat Emptor, particularly if you have an app that may get very popular and may need to move to DRCP.

An “application context” can now be set at connection in Thin mode

Oracle Database can use application contexts to either permit or prevent users from accessing data.

You can now set the appcontext parameter when connecting or creating a connection pool in Thin mode. Previously this was a Thick mode-only feature. See the documentation Connection Metadata and Application Contexts.

In python-oracledb, the appcontext parameter should be a list of 3-tuples that identifies the application context used by the connection. Each tuple should contain a namespace, name, and value:

myctx = [
    ("CLIENTCONTEXT", "MATERIAL", "Steel"),
    ("CLIENTCONTEXT", "LOCATION", "1900") 
]
connection = oracledb.connect(..., appcontext=myctx)

An Edition-Based Redefinition “edition” can now be set at connection time in Thin mode

Previously this feature was only available in Thick mode, while Thin mode users had to explicitly execute an ALTER SESSION command. Now the overhead in Thin mode isn’t needed and the edition can be set during connection.

See the documentation Edition-Based Redefinition.

A new enable_thin_mode() method

A new, optional enable_thin_mode() function forces python-oracledb Thin mode to be enabled and any subsequent init_oracle_client() call to fail.

Since Thin mode is the default, why is this function needed? There is one special case where it can be useful: when your application opens lots of standalone (non-pooled) connections in multiple threads at application startup. It’s not needed if you have a singled-threaded application. It’s not needed if you use a connection pool. It’s obviously not needed if you use a Thick mode application. Note that calling the new function is optional and driver functionality will be no different if it is not called.

The documentation Explicitly Enabling python-oracledb Thin Mode succinctly explains the edge case this method is suited for, but let me try and expand on it here.

The python-oracledb driver starts in Thin mode but this is not enforced until a connection pool or standalone connection is successfully created, after which you can’t change to Thick mode. However if the app hasn’t created a pool or connection, then you can first call init_oracle_client() which enforces the mode to be Thick — and you can’t move back to Thin mode.

Where the subtlety comes in is that if the very first (Thin mode) standalone connection call fails, then your application can still call init_oracle_client() to switch to Thick mode. This behavior was an early feature request. Some users wanted to distribute applications that worked in unknown environments. The developers couldn’t count on Instant Client being available. So their app tries to connect (in Thin mode), but if that fails (for example because the database happens to be Oracle Database 10g, which Thin mode can’t connect to), the app then calls init_oracle_client() with the hope that Instant Client is actually available, allowing a second connection attempt to be made.

To make this all possible in python-oracledb, the mode is not enforced to be Thin mode until a connection pool (even with min=0) is created, or a standalone connection is successfully created. Once one of those calls succeeds, the mode is enforced as Thin mode. So, since all connections in an application must be in the same mode, and the mode isn’t enforced to be Thin mode until a connection or pool is created, any second connection call has to wait for the first one to succeed so it knows what mode to create that second connection in. In a single threaded application there is no problem: the second connect() call isn’t executed until after the first succeeds and the mode is known. But in a multi-threaded application, any second connection in a second thread has to wait for the first connect() call in the first thread to succeed so it knows definitely that Thin mode is determined. This can cause a delay before the second connection is created, particularly if that first connection takes some time to fail. To prevent this delay, your application can call enable_thin_mode() to force the mode to be Thin mode.

In summary, this is an optional call that benefits one special scenario only: multi-threaded applications that open multiple connections but don’t use a connection pool.

Locale aware path handling for init_oracle_client()

Some Windows users were having issues with multi-byte characters in their Instant Client lib_dir and configuration config_dir directory names. Now, from Python 3.11, locale.getencoding() is used for these paths. You can alternatively pass the names as bytes, which may be useful for legacy Python versions.

Installing or Upgrading python-oracledb

You can install or upgrade python-oracledb by running:

python3 -m pip install oracledb --upgrade

The pip options --proxy and --user may be useful in some environments. See python-oracledb Installation for details.

Python-oracledb Resources

Home page: oracle.github.io/python-oracledb/index.html

Installation instructions: python-oracledb.readthedocs.io/en/latest/installation.html

Documentation: python-oracledb.readthedocs.io/en/latest/index.html

Release Notes: python-oracledb.readthedocs.io/en/latest/release_notes.html

Discussions: github.com/oracle/python-oracledb/discussions

Issues: github.com/oracle/python-oracledb/issues

Source Code Repository: github.com/oracle/python-oracledb