python-oracledb 1.3 supports Oracle Database 23c JSON-Relational Duality

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

Photo by DESIGNECOLOGIST on Unsplash

Python-oracledb is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features. It is the new name for the cx_Oracle driver.

In a happy timing coincidence with the release of Oracle Database 23c Free, the latest python-oracledb driver release is available. You don’t need 23c to benefit from the new python-oracledb version but there are lots of great 23c features in the database itself that I’m sure you’ll love. You can download Oracle Database 23c Free here.

Oracle Database 23c Free — Developer Release is the first release of the next-generation Oracle Database, allowing developers a head-start on building applications with innovative 23c features that simplify development of modern data-driven apps. The entire feature set of Oracle Database 23c is planned to be generally available within the next 12 months.

Main python-oracledb 1.3 Features

Thin mode now natively decodes and encodes the Oracle Database 21c JSON data type storage format. This internal format was introduced in 21c, building on previous database releases use of CLOB or BLOB storage for JSON. The latest python-oracledb change removes the need to use a type handler in Thin mode for the JSON database data type. It becomes easier to write code, and it allows the same code to be used in Thin and Thick modes (as long as Thick mode uses Oracle Client 21c or later). Querying ‘just works’, as you’d expect. The change makes using Oracle Database 23c’s fantastic new JSON-relational duality views simple. With these you can create, query, and modify the same data as a set of JSON documents or as a set of related tables and columns. I’ll let the documentation explain further. A brief snippet to create a JSON-relational duality view over Oracle Database 23c’s sample HR schema tables is:

create or replace json relational duality view t as
select json {
  e.employee_id,
  e.first_name,
  e.hire_date,
  'dept': (select json {'id' is d.department_id,
                        'name' is d.department_name}
           from departments d with (update)
           where e.department_id = d.department_id) returning json}
from employees e with (insert, update, delete)

There is a larger example on GitHub.

Note you don’t need to update to python-oracledb 1.3 to connect to Oracle Database 23c, or to use 23c’s JSON-relational duality views, but using JSON is simplified and harmonized in 1.3.

For a quick JSON demo in Python you could create an empty table:

create table t (j json);

With this, you can insert four records and query them like:

with connection.cursor() as cursor:
    data = [
        [{"name":"Rod", "dept": "Sales", "location": "Germany"}],
        [{"name": "George", "dept": "Marketing", "location": "Bangalore"}],
        [{"name": "Sam", "dept": "Sales", "location": "Mumbai"}],
        [{"name": "Jill", "dept": "Marketing", "location": "Germany"}]
    ]
    cursor.setinputsizes(oracledb.DB_TYPE_JSON)  # tell the driver the bind var is JSON
    cursor.executemany("insert into t values (:1)", data)

    for j, in cursor.execute("select j from t"):
        print(j)

The output is:

{'name': 'Rod', 'dept': 'Sales', 'location': 'Germany'}
{'name': 'George', 'dept': 'Marketing', 'location': 'Bangalore'}
{'name': 'Sam', 'dept': 'Sales', 'location': 'Mumbai'}
{'name': 'Jill', 'dept': 'Marketing', 'location': 'Germany'}

You can see it “just works”, getting out of your way and letting you efficiently map Python data structures to, and from, JSON in the database.

Reference: the python-oracledb JSON documentation.

Thin mode handles the new Oracle Database 23c SQL BOOLEAN data type values as True and False. This matches how Thick mode behaves when python-oracledb uses Oracle Client 23c libraries. Previous python-oracledb and Oracle Client versions handle this type as 1 and 0. Note this is a brand new SQL type, not the PL/SQL BOOLEAN type which has been supported for a long time.

Given the table:

create table t (c boolean);

the code below prints “False”:

cursor.execute("insert into t values(:1)", [False])
for r, in cursor.execute("select * from t"):
    print(r)

and the following code prints “True”:

sql = "insert into t (c) values (true) returning c into :bv"
bv = cursor.var(oracledb.DB_TYPE_BOOLEAN)
cursor.execute(sql, [bv])
b, = bv.getvalue()
print(b)

Thin mode connection pools will now shrink to min connections if you are using dynamic pool sizing where the pool max value is greater than min and you have timeout set. In this version, Thin mode pool shrinkage is initiated in a similar fashion as in Thick mode when Thick mode uses Oracle Client 19c libraries. (In Thick mode the pooling functionality is provided by Oracle Client libraries). So if you leave an application completely idle then the pool won't shrink — there has to be some pool access made for the expiry time setting to be checked. Our next release will improve on this and make Thin mode do the expiry in the background like Thick mode does when it uses Oracle Client 21c libraries. As a public-service-announcement remember that we generally prefer fixed size pools.

Support for Oracle Database 23c’s longer passwords was added.

Python-oracledb driver type annotations were improved, so using IDEs with type checking enabled is a better experience giving fewer false warnings.

To simplify application error handling, the error DPY-6005: cannot connect to database is now raised at the top of the error stack for all failures to connect to the database.

Some internal improvements, including connection performance improvements, and many bug fixes landed. See the Release Notes for all the 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