python-oracledb is a renamed, major release of cx_Oracle for connecting Python to Oracle Database

[cjbj]

Welcome to python-oracledb, the new, major cx_Oracle driver release. It comes with a brand new name python-oracledb, hence this new GitHub repository.

See the original release announcement for details. Since that first release we have had several others, check the release notes.

Interesting Stuff

• Python-oracledb is the new name for our popular Python cx_Oracle driver for Oracle Database.

• Python-oracledb 1.0 was a new major release - the successor to cx_Oracle 8.3.

• Python-oracledb is simple and small to install — under 15 MB (including its Python package dependencies): pip install oracledb

• Python-oracledb is now a Thin driver by default - it connects directly to Oracle Database without always needing Oracle Client libraries.

• Python-oracledb runs on many platforms including favorites like Linux, macOS and Windows. It can also be used on platforms where Oracle Client libraries are not available (such as Apple M1, Alpine Linux, or IoT devices), or where the client libraries are not easily installed (such as some cloud environments).

• Python-oracledb has comprehensive functionality conforming to the Python Database API v2.0 Specification, with many additions and just a couple of exclusions.

• A “Thick” mode can be optionally enabled by an application call. This mode has similar functionality to cx_Oracle and supports Oracle Database features that extend the Python DB API. To use this mode, the widely used and tested Oracle Client libraries such as from Oracle Instant Client must be installed separately.

Resources

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

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

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

[tkofford]

Awesome, does this new version support Kerberos authentication?

[anthony-tuininga]

The thick mode does, not the thin mode, though, at least not yet!

[cjbj]

Kerberos support was mentioned by our VP last week as being important, so it's on our radar.

[fenchu]

Great, eager to test, Does this mean I do not oracle instant client anymore. It will mean 300MB less container size.

[doerwalter]

Also it seems that custom types are no longer supported. With

create or replace type varchars as table of varchar2(4000);

I get:

Python 3.10.4 (main, Apr 26 2022, 19:42:59) [Clang 13.1.6 (clang-1316.0.21.2)]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.3.0 -- An enhanced Interactive Python. Type '?' for help.
🐍 — 1 — 18:20:16.308616 ❯ import oracledb, cx_Oracle
🐍 — 2 — 18:20:21.518450 ❯ odb = oracledb.connect("user/pwd@db")
🐍 — 3 — 18:20:26.352752 ❯ cdb = cx_Oracle.connect("user/pwd@db")
🐍 — 4 — 18:20:30.198178 ❯ oc = odb.cursor()
🐍 — 5 — 18:20:33.284193 ❯ cc = cdb.cursor()
🐍 — 8 — 18:20:52.522373 ❯ cc.execute("select varchars('foo', 'bar') from dual").fetchone()
🐍 — 8 — 18:34:14.630333 ❮ (<cx_Oracle.Object LL.VARCHARS at 0x10fadc370>,)
🐍 — 9 — 18:34:14.636108 ❯ oc.execute("select varchars('foo', 'bar') from dual").fetchone()
---------------------------------------------------------------------------
NotSupportedError                         Traceback (most recent call last)
Input In [9], in <cell line: 1>()
----> 1 oc.execute("select varchars('foo', 'bar') from dual").fetchone()

File ~/pyvenvs/default/lib/python3.10/site-packages/oracledb/cursor.py:378, in Cursor.execute(self, statement, parameters, **keyword_parameters)
    376 if parameters is not None:
    377     impl.bind_one(self, parameters)
--> 378 impl.execute(self)
    379 if impl.fetch_vars is not None:
    380     return self

File src/oracledb/impl/thin/cursor.pyx:122, in oracledb.thin_impl.ThinCursorImpl.execute()

File src/oracledb/impl/thin/protocol.pyx:294, in oracledb.thin_impl.Protocol._process_single_message()

File src/oracledb/impl/thin/protocol.pyx:295, in oracledb.thin_impl.Protocol._process_single_message()

File src/oracledb/impl/thin/protocol.pyx:255, in oracledb.thin_impl.Protocol._process_message()

File src/oracledb/impl/thin/protocol.pyx:234, in oracledb.thin_impl.Protocol._process_message()

File src/oracledb/impl/thin/messages.pyx:279, in oracledb.thin_impl.Message.process()

File src/oracledb/impl/thin/messages.pyx:785, in oracledb.thin_impl.MessageWithData._process_message()

File src/oracledb/impl/thin/messages.pyx:865, in oracledb.thin_impl.MessageWithData._process_row_data()

File src/oracledb/impl/thin/messages.pyx:591, in oracledb.thin_impl.MessageWithData._process_column_data()

File ~/pyvenvs/default/lib/python3.10/site-packages/oracledb/errors.py:103, in _raise_err(error_num, context_error_message, cause, **args)
    101     message = f"{message}\n{context_error_message}"
    102 exc_type = ERR_EXCEPTION_TYPES[error_num // 1000]
--> 103 raise exc_type(_Error(message)) from cause

NotSupportedError: DPY-3007: database type "DB_TYPE_OBJECT" is not supported

[anthony-tuininga]

Custom (Oracle Database) types are not supported with thin mode currently. That is on the list of things to implement. :-) If you use thick mode (by calling init_oracle_client() before creating your connection) it will work as expected.

[anthony-tuininga]

A similar issue may be occurring with your date scenario. If you call "alter session set time_zone" does that make a difference? If not, please open a new discussion or issue so we can see if the scenario can be replicated. Thanks!

[doerwalter]

OK, done for the timestamp issue: #7

[doerwalter]

Yes, using init_oracle_client() makes the varchars example work:

Python 3.10.4 (main, Apr 26 2022, 19:42:59) [Clang 13.1.6 (clang-1316.0.21.2)]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.3.0 -- An enhanced Interactive Python. Type '?' for help.
🐍 — 1 ❯ import oracledb
🐍 — 2 ❯ oracledb.init_oracle_client()
🐍 — 3 ❯ odb = oracledb.connect("user/pwd@db")
🐍 — 5 ❯ oc = odb.cursor()
🐍 — 7 ❯ oc.execute("select varchars('foo', 'bar') from dual").fetchone()
🐍 — 7 ❮ (<oracledb.Object LL.VARCHARS at 0x10bf06200>,)
🐍 — 8 ❯

[bburbidge]

FYI for anyone looking and working with SQLAlchemy, this is what needs to be done until the next SQLAlchemy release

import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb
import cx_Oracle

[cjbj]

Thanks - that's a good call to share this useful info. Some related resources:

• the user doc Python Frameworks, SQL Generators, and ORMs
• blog post Using python-oracledb 1.0 with SQLAlchemy, Pandas, Django and Flask
• blog post Using SQLAlchemy 2.0 (development) with python-oracledb