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_objhas been removed
- New Connection object attributes
- New SQL Domain and Annotation attributes
- Some obsolete, long deprecated parameters like
nencodinghave 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
- 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
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
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
The only property of
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.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.annotations are available after executing SQL queries. These provide python-oracledb applications with support for the Oracle Database 23c domain and annotations features. See the FetchInfo Attributes documentation for the full list of available attributes.
There are other improvements and various bug fixes — and desupport of some previously deprecated functionality (such as the parameters
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
--user may be useful in some environments. See python-oracledb Installation for details.
Home page: oracle.github.io/python-oracledb/index.html
Installation instructions: python-oracledb.readthedocs.io/en/latest/installation.html
Release Notes: python-oracledb.readthedocs.io/en/latest/release_notes.html
Source Code Repository: github.com/oracle/python-oracledb