Python-oracledb 2.0 has asyncio support

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

Photo by Luca Bravo on Unsplash

python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features.

This is a big release, marking a significant milestone in the history of Python drivers for Oracle Database. Prior to today, the most recent previous, big news for drivers was python-oracledb 1.0’s introduction of a Thin mode in May 2022. This renamed successor to the cx_Oracle 8.3 driver is now widely used for its ease of use and performance. In python-oracledb 2.0, the introduction of support for asynchronous concurrent programming brings even more power and capabilities to your applications.

Main Changes in python-oracledb 2.0

The main changes are:

• Support for asynchronous concurrent coding
• Support for ‘Success With Info’ warnings
• Support for configuring the SDU in Thin mode
• Support for the obsolete Python 3.6 release has been dropped
• The future oracledb.__future__.old_json_col_as_obj has been removed
• New Connection object attributes
• New SQL Domain and Annotation attributes
• Some obsolete, long deprecated parameters like encoding and nencoding have been desupported

Support for asynchronous concurrent coding

Now it is easy to write concurrent code with Python’s popular asyncio module:

• A new python-oracledb async API is part of the existing python-oracledb module.
• It is available in the default python-oracledb Thin mode.
• Any Oracle Database call which needs a round-trip to the database now has an async counterpart.
• Connection is via new methods connect_async() and create_pool_async().
• There are new classes AsyncConnection, AsyncConnectionPool, AsyncCursor, and AsyncLOB.
• There are also new convenience methods from AsyncConnection objects that remove to need to explicitly create cursors. For example connection.execute() is a shortcut for creating a cursor, executing a statement with the cursor, and then closing the cursor.

See my blog post Concurrent Python Programming with python-oracledb and asyncio for more detail.

Supporting asyncio was a hugely popular feature request and early adopters already love it.

In python-oracledb 2.0 we are calling the asyncio support a pre-release so that you can do wider testing, and giving us some leeway in case we want to make changes based on your feedback.

Support for ‘Success With Info’ warnings

A new Cursor.warning property aids efficiency when using python-oracledb to create stored PL/SQL procedures and functions (and types). PL/SQL creation failures don't return an error so previously the data dictionary needed to be explicitly queried after each CREATE statement to see if there had been a problem. The new flag is now an efficient way to see if there was an issue. See Creating Stored Procedures and Packages for an example.

A similar, new Connection.warning property is set if connection creation succeeded but the password is in the grace period. By checking the flag you can log that the password needs updating, or add logic to immediately change the password . You might also see this property set in python-oracledb Thick mode if the connection pool was created but database host resource limitations didn't allow for the full size to be attained.

Support for configuring the SDU in Thin mode

The Session Data Unit (SDU) sizing the internal buffers used to communicate with Oracle Database can now be configured in Thin mode, bringing it to parity with python-oracledb Thick mode.

Increasing the SDU size may improve the performance of large data transfers over slow networks. Like all tuning, benchmarking in your own environment to find the optimal SDU size is crucial. The current defaults may be perfectly fine for you. Bigger sizes may be counter-productive in some cases, such as when your applications mostly do data transfers of small sizes, or when your network is fast. Making the size unnecessarily big can negatively impact both network performance and database memory use.

In python-oracledb Thin and Thick mode the SDU can be set in the connect descriptor or Easy Connect connection string. Alternatively in Thin mode you can pass it as a connection or pool creation parameter. In Thick mode you can optionally set it in your “client side” sqlnet.ora file.

The SDU value actually used by the network communication is negotiated down to the minimum of the database server and python-oracledb settings. The default size in python-oracledb is 8 K, which matches the current Oracle Database default.

Support for the obsolete Python 3.6 release has been dropped

We started providing binaries for 3.12 back in the python-oracledb 1.4.2 release, so there is plenty of choice available to you: Python 3.7, 3.8, 3.9, 3.10, 3.11 and 3.12

The future oracledb.__future__.old_json_col_as_obj is no longer needed

python-oracledb has an oracledb.__future__ object allowing the use of driver behavior that we intend(ed) to make the default at some future release, thus allowing your applications to future-proof themselves. This is similar to Python’s__future__ statement,

The only property of oracledb.__future__ was old_json_col_as_obj which changed the behavior of how JSON data found in VARCHAR2 and LOB columns with the “IS JSON” constraint was fetched.

Now in python-oracledb 2.0 the future is here and old_json_col_as_obj has been removed. JSON data found in VARCHAR2 and LOB columns with the “IS JSON” constraint (the “old” way of storing JSON in Oracle Database) is now always fetched as an easy-to-manipulate object the same way that data in Oracle Database 21c JSON data type columns is fetched. If you still want data returned as VARCHAR2s or LOBs, you can implement an output type handler to return the desired type.

New Connection object attributes

New Connection object attributes Connection.db_domain, Connection.db_name, Connection.max_open_cursors, Connection.sdu, Connection.service_name, Connection.transaction_in_progress, and Connection.proxy_user give more visibility into connection environment and state. See the Connection Attributes documentation for the full list of available attributes.

New SQL Domain and Annotation attributes

New FetchInfo properties FetchInfo.domain_schema, FetchInfo.domain_name and FetchInfo.annotations are available after executing SQL queries. These provide python-oracledb applications with support for the Oracle Database 23ai domain and annotations features. See the FetchInfo Attributes documentation for the full list of available attributes.

Other

There are other improvements and various bug fixes — and desupport of some previously deprecated functionality (such as the parameters encoding and nencoding — remove these from your code). See the Release Notes and Deprecations documentation for more details.

Installing or Upgrading python-oracledb

You can install or upgrade python-oracledb by running:

python -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 References

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