python-oracledb 2.2 and the VECTOR type in Oracle Database 23ai

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

We’re pleased to release python-oracledb 2.2 to coincide with the general availability of Oracle Database 23ai.

Photo by Jamie Street on Unsplash

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.

To get started quickly, use samples/sample_container to create a container image containing the database and python-oracledb.

Main Changes for Oracle Database 23ai

Support for the Oracle Database 23ai VECTOR data type

A major feature of Oracle Database 23ai is AI Vector Search (which is part of the database and is available at no additional charge in Enterprise Edition, Standard Edition 2, Database Free, and all Oracle Database cloud services).

Python-oracle supports this feature with its native capabilities for binding and fetching the new VECTOR data type. For example, given the table:

SQL> drop table if exists mytab_v; 
SQL> create table mytab_v (v64 vector(3, float64));

Python code to insert a vector could look like:

vector_data_64 = array.array("d", [11.25, 11.75, 11.5])

cursor.execute(
    "insert into mytab_v (v64) values (:1)",
    [vector_data_64]
)

A query:

cursor.execute("select * from mytab_v")
for row in cursor:
    print(row)

would then show:

(array('d', [11.25, 11.75, 11.5]),)

Other examples are on GitHub.

(And did you note the new DROP TABLE IF EXISTS syntax I used?!)

Update: a technical brief Using Oracle Database 23ai Vectors in python-oracledb is available.

Support for the Oracle Database 23ai BOOLEAN data type

Oracle Database 23ai’s new SQL BOOLEAN data type lets you store true and false values in database tables. (Previously this type was only available in PL/SQL).

Python-oracledb 2.2 supports binding and fetching SQL BOOLEAN values. For example:

bv = cursor.var(oracledb.DB_TYPE_BOOLEAN)
bv.setvalue(0, True)
for r, in cursor.execute("select :bv", [bv]):
    print(r)

displays:

True

Note there is no FROM DUAL in the query! One Oracle Database 23ai enhancement was to make the clause optional in this common query idiom.

Support for the Oracle Database INTERVAL YEAR TO MONTH data type

The not-so-common INTERVAL YEAR TO MONTH data type is not new to the database, but python-oracledb 2.2 now has support via anoracledb.IntervalYM class. The class is a named tuple with two integer attributes, years and months. The code:

bv = cursor.var(oracledb.DB_TYPE_INTERVAL_YM)
bv.setvalue(0, oracledb.IntervalYM(years=3, months=10))
cursor.execute("insert into mytab_b (iym_col) values (:1)", [bv])

for r, in cursor.execute("select * from mytab_b"):
    print(r)

displays:

IntervalYM(years=3, months=10)

Oracle Database 23ai JSON and SODA improvements

One big Oracle Database 23ai announcement is JSON Relational Duality, letting you leverage the power of relational and the simplicity of JSON development within a single app. Also check out this blog post.

Various enhancements to python-oracledb improve its support for JSON and SODA features in Oracle Database 23ai.

Oracle Database 23ai Implicit Connection Pooling

Implicit connection pooling is a great feature that leverages Oracle Database’s popular DRCP pooling. The intended use case is for applications that are not using python-oracledb connection pooling, but have become popular and are now causing excess connection load on the database server. Typically legacy applications can’t be modified to explicitly use application connection pooling calls. However they can make use of implicit connection pooling simply by starting DRCP in the database and then using a new connection string option POOL_BOUNDARY. The driver then automatically associates and disassociates DRCP pooled servers from application connections as they are actually used by the application to do database work. This can occur transparently between the application's explicit oracledb.connect() call and the connection.close() (or equivalent application's connection release at end-of-scope). By allowing each database pooled server to be used by multiple application connections, the load on the database host is reduced.

Implicit connection pooling is available in both python-oracledb Thin and Thick modes. It works with DRCP and also with CMAN-TDM’s Proxy Connection Pooling (PRCP). CMAN-TDM is Oracle Database’s mid-tier proxy solution.

As an example, to use implicit connection pooling with DRCP, your python-oracledb connection string might look like:

dsn = "localhost:1521/orclpdb:pooled?pool_boundary=statement"

Oracle Database 23ai Improved Connections

During the Oracle Database 23ai development cycle there were improvements to the database stack, and also in the Oracle Cloud networking infrastructure, that help connection performance in various scenarios. Some changes reduce the time to connect to the database, while others improve the transport across established connections. Oracle Database’s language drivers benefit in different ways. I can’t point to a specific “tell all” benchmark because there are lots of different connection configurations (such as the speed of your network, whether dedicated or pre-spawned server process are being used, whether encryption is being used, what language stack is used, are you in the cloud etc., etc.) but overall we’re very happy with the improvements.

Some of the specific changes in the python-oracledb driver include:

• Internal optimization in Python-oracledb Thin mode (and Thick mode when using Oracle Client 23ai libraries) to reduce overheads and connection establishment times when connecting to Oracle Database 23ai.
• Internal support for an Oracle Database 23ai network protocol improvement that directly benefits python-oracledb’s asyncio (and node-oracledb’s) performance when fetching large columns. This lets python-oracledb 2.2 more efficiently handle internal packet management.

Oracle Database 23ai SQL Annotations

The python-oracledb query metadata attribute FetchInfo.annotation returns a dictionary containing the database annotations associated with a fetched column. Annotations provide a mechanism to store application metadata centrally in the database so that they can be shared across applications, modules and microservices.

Oracle Database 23ai SQL Domains

The python-oracledb query metadata attributes FetchInfo.domain_schema and FetchInfo.domain_name support data use case domain information associated with a fetched column.

A use case domain is high-level dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints. You can define table columns to be associated with a domain, thereby explicitly applying the domain’s optional properties and constraints to the columns.

Oracle Database Error URLs

One recent database group project was a review and rewording of common database error messages. An error portal now contains database messages with their Cause and Action help information. Python-oracledb will show a link to the relevant portal entry when printing database error messages. By following the link, further troubleshooting information is available to you. For example:

$ python t.py ORA-01017: invalid credential or not authorized; logon denied 
Help: https://docs.oracle.com/error-help/db/ora-01017/

These help links are displayed in Thin and Thick modes. With Thick mode, Oracle Client 23ai libraries are needed.

Summary

See the python-oracledb Release Notes for more improvements and bug fixes. Some recent versions had limited support for pre-release Oracle Database 23ai and the Oracle Database 23.3 Free edition features, but we strongly encourage you to upgrade to python-oracledb 2.2 if you are using any edition of Oracle Database 23.4 because of recent internal changes.

Don’t forget there are many great Oracle Database 23ai features that are part of the database itself, or are usable through SQL and PL/SQL — or are in other tools. See the Oracle Database New Features guide.

For examples, did you know SQL*Plus 23ai has a new PING command and a matching command line -P option to ping the database? This removes the need to install tnsping to check database availability. Did you know that you can use JavaScript for Oracle Database stored procedures? Did you know that our C language Oracle Call Interface API now supports database request pipelining? Database pipelining is something we'd like to expose in a future python-oracle version, so stay tuned to our release announcements!

Finally, if you are taking advantage of Oracle Database 23ai Free in Oracle Cloud, then our documentation Connecting to Oracle Cloud Autonomous Databases will be useful.

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