The python-oracledb and cx_Oracle Drivers — python-oracledb 3.2.0b1 documentation (original) (raw)

The python-oracledb driver is the renamed, major version successor to cx_Oracle 8.3. The python-oracledb driver has many new features and some Deprecated and Desupported Features compared with cx_Oracle. Also, seeUpgrading from cx_Oracle 8.3 to python-oracledb. The cx_Oracle driver is obsolete and should not be used for new development.

29.1. Differences between the python-oracledb and cx_Oracle Drivers

The differences between python-oracledb and the obsolete cx_Oracle driver are listed here.

29.1.1. Mode differences from cx_Oracle

By default, python-oracledb runs in a ‘Thin’ mode which connects directly to Oracle Database. This mode does not need Oracle Client libraries. However, some additional functionality is available when python-oracledb uses them. Python-oracledb is said to be in ‘Thick’ mode when Oracle Client libraries are used. See Enabling python-oracledb Thick mode. Both modes have comprehensive functionality supporting the Python Database API v2.0 Specification.

cx_Oracle always runs in a Thick mode using Oracle Client libraries. The features in python-oracledb Thick mode and cx_Oracle 8.3 are the same, subject to the new features, some deprecations, and to other changes noted in this section.

29.1.2. Oracle Client Library Loading Differences from cx_Oracle

Oracle Client libraries are now only loaded iforacledb.init_oracle_client() is called in your application. This changes python-oracledb to Thick mode. The init_oracle_client() method must be called before any standalone connection orconnection pool is created. If a connection or pool is created first in the default Thin mode, then Thick mode cannot be enabled.

See Enabling python-oracledb Thick mode for more information.

Calling the init_oracle_client() method immediately loads Oracle Client libraries. To emulate the cx_Oracle behavior of deferring library loading until the creation of the first connection (in the case wheninit_oracle_client() is not called), your application will need to explicitly defer calling init_oracle_client() as appropriate.

In python-oracledb, init_oracle_client() can now be called multiple times in the one Python process as long as its arguments are the same each time.

29.1.2.1. oracledb.clientversion()

The oracledb.clientversion() method shows the version of the Oracle Client libraries being used. There is no Oracle Client used in the python-oracledb Thin mode so this function can only be called in python-oracledb Thick mode. If this function is called beforeoracledb.init_oracle_client(), an exception is thrown.

29.1.3. Connection Differences from cx_Oracle

29.1.3.1. oracledb.connect() Differences

The oracledb.connect() function in the python-oracledb driver differs from cx_Oracle:

The use of the class constructor method oracledb.Connection() to create connections is no longer recommended for creating connections. Useconnect() instead.

The oracledb.makedsn() method for creating the dsn value has been deprecated. New code should use oracledb.ConnectParams() or use the new keyword arguments in oracledb.connect().

29.1.3.2. Connection Object Differences

The Connection object differences between the python-oracledb and cx_Oracle drivers are:

See Connection Attributes for more information.

29.1.4. Pooling Differences from cx_Oracle

It is recommended to use the new ConnectionPool Objectinstead of the equivalent SessionPool object, which is deprecated. To create a connection pool, use oracledb.create_pool(), which is equivalent to calling cx_Oracle.SessionPool().

For more information, see Connection Pooling.

29.1.4.1. oracledb.SessionPool() Differences

The python-oracledb oracledb.SessionPool() method (which is an alias oforacledb.create_pool()) differs from cx_Oracle.SessionPool() as follows:

The oracledb.makedsn() method for creating the dsn value has been deprecated. New code should use oracledb.ConnectParams() or use the new keyword arguments to oracledb.create_pool().

29.1.4.2. SessionPool Object Differences

The SessionPool object (which is an alias for the ConnectionPool object) differences between the python-oracledb and cx_Oracle drivers are:

29.1.5. Cursor Object Differences from cx_Oracle

The differences between the Cursor object in python-oracledb and cx_Oracle drivers are:

29.1.6. Fetching IS JSON Column Differences from cx_Oracle

In python-oracledb, VARCHAR2 and LOB columns that have the IS JSONconstraint enabled are fetched as Python objects. These columns are fetched in the same way that JSON type columns are fetched when using Oracle Database 21c (or later). The returned value varies depending on the JSON data. If the JSON data is an object, then a dictionary is returned. If it is an array, then a list is returned. If it is a scalar value, then that particular scalar value is returned.

In cx_Oracle, VARCHAR2 and LOB columns that have the IS JSON constraint enabled are fetched as strings and LOB objects respectively. To enable this same fetch behavior in python-oracledb, you must use anoutput type handler as shown below.

def type_handler(cursor, fetch_info): if fetch_info.is_json: return cursor.var(fetch_info.type_code, cursor.arraysize)

29.1.7. Advanced Queuing (AQ) Differences from cx_Oracle

Use the new Advanced Queuing (AQ) API instead of the older API which was deprecated in cx_Oracle 7.2 and is not available in python-oracledb.

Replace:

The AQ support in python-oracledb has the following enhancements from cx_Oracle:

See Oracle Advanced Queuing (AQ).

29.1.8. Error Handling Differences from cx_Oracle

In python-oracledb Thick mode, error messages generated by the Oracle Client libraries and the ODPI-C layer used by cx_Oracle and python-oracledb in Thick mode are mostly returned unchanged from cx_Oracle 8.3. Some exceptions shown below.

Note that the python-oracledb driver error messages can also vary between Thin and Thick modes. See Error Handling in Thin and Thick Modes.

29.1.8.1. ConnectionPool.acquire() Message Differences

ConnectionPool.acquire() ORA errors will be mapped to DPY errors. For example:

DPY-4005: timed out waiting for the connection pool to return a connection

replaces the cx_Oracle 8.3 error:

ORA-24459: OCISessionGet() timed out waiting for pool to create new connections

29.1.8.2. Dead Connection Detection and Timeout Message Differences

Application code which detects connection failures or statement execution timeouts will need to check for new errors, DPY-4011 and DPY-4024respectively. The error DPY-1001 is returned if an already dead connection is attempted to be used.

The new Error object attribute full_code may be useful for checking the error code.

Example error messages are:

29.2. Upgrading from cx_Oracle 8.3 to python-oracledb

This section provides the detailed steps needed to upgrade from the obsolete cx_Oracle driver to python-oracledb.

29.2.1. Things to Know Before the Upgrade

Below is a list of some useful things to know before upgrading from cx_Oracle to python-oracledb:

29.2.2. Steps to Upgrade to python-oracledb

If you are creating new applications, follow Installing python-oracledb and refer to other sections of the documentation for usage information.

To upgrade existing code from cx_Oracle to python-oracledb, perform the following steps:

  1. Install the new python-oracledb module:
    python -m pip install oracledb
    See Installing python-oracledb for more details.
  2. Import the new interface module. This can be done in two ways. You can change:
    to:
    import oracledb as cx_Oracle
    Alternatively, you can replace all references to the module cx_Oraclewith oracledb. For example, change:
    import cx_Oracle
    c = cx_Oracle.connect(...)
    to:
    import oracledb
    c = oracledb.connect(...)
    Any new code being introduced during the upgrade should aim to use the latter syntax.
  3. Use keyword parameters in calls to oracledb.connect(),oracledb.Connection(), and oracledb.SessionPool().
    You must replace positional parameters with keyword parameters, unless only one parameter is being passed. Python-oracledb uses keyword parameters exclusively unless a DSN containing the user, password, and connect string combined, for example un/pw@cs, is used. This change makes the driver compliant with the Python Database API specification PEP 249.
    For example, the following code will fail:
    c = oracledb.connect("un", "pw", "cs")
    and needs to be changed to:
    c = oracledb.connect(user="un", password="pw", dsn="cs")
    The following example will continue to work without change:
    c = oracledb.connect("un/pw@cs")
  4. Review obsolete encoding parameters in calls to oracledb.connect(),oracledb.Connection(), and oracledb.SessionPool():
    • encoding and nencoding are desupported in python-oracledb and must be removed. The python-oracledb driver uses UTF-8 exclusively.
    • threaded is desupported in oracledb.connect() andoracledb.Connection() by python-oracledb and must be removed. This parameter was already ignored in oracledb.SessionPool() from cx_Oracle 8.2.
  5. Remove all references to Cursor.fetchraw() as this method was deprecated in cx_Oracle 8.2 and has been removed in python-oracledb. Instead, use one of the other fetch methods such asCursor.fetchmany().
  6. The default value of the oracledb.SessionPool() parametergetmode now waits for an available connection. That is, the default is now POOL_GETMODE_WAIT instead ofPOOL_GETMODE_NOWAIT. The new default value improves the behavior for most applications. If the pool is in the middle of growing, the new value prevents transient connection creation errors from occurring when using the Thin mode, or when using the Thick mode with recent Oracle Client libraries.
    If the old default value is required, modify any pool creation code to explicitly specify getmode=oracledb.POOL_GETMODE_NOWAIT.
    Note a ConnectionPool class deprecates the equivalent SessionPool class. The method oracledb.create_pool() deprecates the use of oracledb.SessionPool(). New pool parameter constant names such as POOL_GETMODE_NOWAIT and PURITY_SELFare now preferred. The old namespaces still work.
  7. The method signature of the output type handlerwhich can be specified on aconnection or on acursor is handler(cursor, metadata). The old signature handler(cursor, name, default_type, length, precision, scale) was deprecated in python-oracledb 1.4 but will still work and will be removed in a future version.
  8. VARCHAR2 and LOB columns that have the IS JSON constraint enabled are fetched by default as Python objects in python-oracledb. In cx_Oracle, VARCHAR2 and LOB columns that contain JSON data are fetched by default as strings and LOB objects respectively. See Fetching IS JSON Column Differences from cx_Oracle.
  9. Review the following sections to see if your application requirements are satisfied by the python-oracledb Thin mode:
  10. Review Differences between the python-oracledb and cx_Oracle Drivers.

If your code base uses an older cx_Oracle version, review the previousrelease notes for additional changes to modernize the code.

  1. Modernize code as needed or desired. See Deprecated and Desupported Features for the list of deprecations in python-oracledb.

29.2.2.1. Additional Upgrade Steps to use python-oracledb Thin Mode

To use python-oracledb Thin mode, the following changes need to be made in addition to the common Steps to Upgrade to python-oracledb:

  1. Remove calls to init_oracle_client() since this turns on the python-oracledb Thick mode.
  2. If the config_dir parameter of init_oracle_client() had been used, then set the new defaults.config_dir attribute to the desired value or set the config_dir parameter in your connection or pool creation method call. For example:
    oracledb.defaults.config_dir = "/opt/oracle/config"
    or
    connection = oracledb.connect(user="hr", password=userpwd, dsn="orclpdb",
    config_dir="/opt/oracle/config")

Also, see Oracle Net Services and Client Configuration Files. 3. If the driver_name parameter of init_oracle_client() had been used, then set the new defaults.driver_name attribute to the desired value or set the driver_name parameter when connecting. The convention for this parameter is to separate the product name from the product version by a colon and single blank characters. For example:
oracledb.defaults.driver_name = "python-oracledb : 1.2.0"
See Other python-oracledb Thick Mode Initialization. 4. If the application is connecting using a TNS Alias from a tnsnames.ora file located in a “default” location such as the Instant Client network/admin/ subdirectory, in $ORACLE_HOME/network/admin/, or in $ORACLE_BASE/homes/XYZ/network/admin/ (in a read-only Oracle Database home), then the configuration file directory must now explicitly be set as shown in Step 2. 5. Remove calls to oracledb.clientversion() which is only available in the python-oracledb Thick mode. Oracle Client libraries are not available in Thin mode. 6. Ensure that any assumptions about when connections are created in the connection pool are eliminated. The python-oracledb Thin mode creates connections in a daemon thread and so the attributeConnectionPool.opened will change over time and will not be equal to ConnectionPool.min immediately after the pool is created. Note that this behavior is also similar in recent versions of the Oracle Call Interface (OCI) Session Pool used by the Thick mode. Unless theoracledb.SessionPool() function’s parameter getmode isoracledb.POOL_GETMODE_WAIT, then applications should not callConnectionPool.acquire() until sufficient time has passed for connections in the pool to be created. 7. Review error handling improvements. See Error Handling in Thin and Thick Modes. 8. Review locale and globalization usage. See Character Sets and Globalization.

29.2.2.2. Additional Upgrade Steps to use python-oracledb Thick Mode

To use python-oracledb Thick mode, the following changes need to be made in addition to the common Steps to Upgrade to python-oracledb:

  1. The function oracledb.init_oracle_client() must be called. It can be called anywhere before the first call to connect(),oracledb.Connection(), and oracledb.SessionPool(). This enables the Thick mode. See Enabling python-oracledb Thick mode for more details.
    The requirement to call init_oracle_client() means that Oracle Client library loading is not automatically deferred until the driver is first used, such as when a connection is opened. The application must explicitly manage this if deferral is required.
    In python-oracledb, init_oracle_client() can be called multiple times in a Python process as long as the arguments are the same.
    Note that on Linux and related operating systems, theinit_oracle_client() parameter lib_dir should not be passed. Instead, set the system library search path with ldconfig orLD_LIBRARY_PATH prior to running Python.
  2. Replace all usages of the deprecated Advanced Queuing API with the new API originally introduced in cx_Oracle 7.2, see Using Oracle Transactional Event Queues and Advanced Queuing.
  3. Review error handling improvements. See Error Handling in Thin and Thick Modes.

29.2.3. Code to Aid the Upgrade to python-oracledb

29.2.3.1. Toggling between Drivers

The sample oracledb_upgrade.py shows a way to toggle applications between cx_Oracle and the two python-oracledb modes. Note this script cannot map some functionality such as obsolete cx_Oraclefeatures or error message changes.

An example application showing this module in use is:

test.py

import oracledb_upgrade as cx_Oracle import os

un = os.environ.get("PYTHON_USERNAME") pw = os.environ.get("PYTHON_PASSWORD") cs = os.environ.get("PYTHON_CONNECTSTRING")

connection = cx_Oracle.connect(user=un, password=pw, dsn=cs) with connection.cursor() as cursor: sql = """select unique client_driver from v$session_connect_info where sid = sys_context('userenv', 'sid')""" for r, in cursor.execute(sql): print(r)

You can then choose what mode is in use by setting the environment variableORA_PYTHON_DRIVER_TYPE to one of “cx”, “thin”, or “thick”:

export ORA_PYTHON_DRIVER_TYPE=thin python test.py

Output shows the python-oracledb Thin mode was used:

python-oracledb thn : 3.0.0

You can customize oracledb_upgrade.py to your needs. For example, if your connection and pool creation calls always use keyword parameters, you can remove the shims that map from positional arguments to keyword arguments.

The simplest form is shown in Python Frameworks, SQL Generators, and ORMs.

29.2.3.2. Testing Which Driver is in Use

To know whether the driver is cx_Oracle or python-oracledb, you can use code similar to:

import oracledb as cx_Oracle

or:

import cx_Oracle

if cx_Oracle.name == 'cx_Oracle': print('cx_Oracle') else: print('oracledb')

Another method that can be used to check which driver is in use is to query the view V$SESSION_CONNECT_INFO, see Finding the python-oracledb Mode.

29.2.4. Python Frameworks, SQL Generators, and ORMs

Python-oracledb’s Thin and Thick modes cover the feature needs of frameworks that depend upon the Python Database API.

For versions of SQLAlchemy, Django, Superset, other frameworks, object-relational mappers (ORMs), and libraries that do not have native support for python-oracledb, you can add code like this to use python-oracledb instead of cx_Oracle:

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

Note

This must occur before any import of cx_Oracle by your code or the library.

To use Thick mode, for example if you need to connect to Oracle Database 11gR2, also add a call to oracledb.init_oracle_client() with the appropriate parameters for your environment, see Enabling python-oracledb Thick mode.

SQLAlchemy 2 and Django 5 have native support for python-oracledb so the above code snippet is not needed in those versions.

29.2.4.1. Connecting with SQLAlchemy

SQLAlchemy 1.4

Using python-oracledb in SQLAlchemy 1.4

import os import getpass import oracledb from sqlalchemy import create_engine from sqlalchemy import text

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

Uncomment to use python-oracledb Thick mode

Review the doc for the appropriate parameters

#oracledb.init_oracle_client()

un = os.environ.get("PYTHON_USERNAME") cs = os.environ.get("PYTHON_CONNECTSTRING") pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

Note the first argument is different for SQLAlchemy 1.4 and 2

engine = create_engine('oracle://@', connect_args={ # Pass any python-oracledb connect() parameters "user": un, "password": pw, "dsn": cs } )

with engine.connect() as connection: print(connection.scalar(text( """select unique client_driver from v$session_connect_info where sid = sys_context('userenv', 'sid')""")))

Note that the create_engine() argument driver declaration usesoracle:// for SQLAlchemy 1.4 and oracle+oracledb:// for SQLAlchemy 2.

The connect_args dictionary can use any appropriateoracledb.connect() parameter.

SQLAlchemy 2

Using python-oracledb in SQLAlchemy 2

import os import getpass import oracledb from sqlalchemy import create_engine from sqlalchemy import text

Uncomment to use python-oracledb Thick mode

Review the doc for the appropriate parameters

#oracledb.init_oracle_client()

un = os.environ.get("PYTHON_USERNAME") cs = os.environ.get("PYTHON_CONNECTSTRING") pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

Note the first argument is different for SQLAlchemy 1.4 and 2

engine = create_engine('oracle+oracledb://@', connect_args={ # Pass any python-oracledb connect() parameters "user": un, "password": pw, "dsn": cs } )

with engine.connect() as connection: print(connection.scalar(text( """select unique client_driver from v$session_connect_info where sid = sys_context('userenv', 'sid')""")))

Note that the create_engine() argument driver declaration usesoracle:// for SQLAlchemy 1.4 and oracle+oracledb:// for SQLAlchemy 2.

The connect_args dictionary can use any appropriateoracledb.connect() parameter.

SQLAlchemy Connection Pools

Most multi-user applications should use a connection pool. The python-oracledb pool is preferred because of its high availability support. For example:

Using python-oracledb in SQLAlchemy 2

import os, platform import getpass import oracledb from sqlalchemy import create_engine from sqlalchemy import text from sqlalchemy.pool import NullPool

Uncomment to use python-oracledb Thick mode

Review the doc for the appropriate parameters

#oracledb.init_oracle_client()

un = os.environ.get("PYTHON_USERNAME") cs = os.environ.get("PYTHON_CONNECTSTRING") pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

pool = oracledb.create_pool(user=un, password=pw, dsn=cs, min=4, max=4, increment=0) engine = create_engine("oracle+oracledb://", creator=pool.acquire, poolclass=NullPool)

with engine.connect() as connection: print(connection.scalar(text("""select unique client_driver from v$session_connect_info where sid = sys_context('userenv', 'sid')""")))

You can also use python-oracledb connection pooling with SQLAlchemy 1.4. Use the appropriate name mapping code and first argument to create_engine().