27. Extending python-oracledb

You can extend the capabilities of python-oracledb by using standard Python functionality for subclassing python-oracledb classes. You can also use python-oracledb plugins, connection protocol hooks, connection password hooks, and connection parameter hooks.

27.1. Subclassing Connections

Subclassing enables applications to change python-oracledb, for example by extending connection and statement execution behavior. This can be used to alter, or log, connection and execution parameters, or to further change python-oracledb functionality.

The example below demonstrates subclassing a connection to log SQL execution to a file. This example also shows how connection credentials can be embedded in the custom subclass, so application code does not need to supply them.

class Connection(oracledb.Connection):
    log_file_name = "log.txt"

    def __init__(self):
        connect_string = "hr/hr_password@dbhost.example.com/orclpdb"
        self._log("Connect to the database")
        return super(Connection, self).__init__(connect_string)

    def _log(self, message):
        with open(self.log_file_name, "a") as f:
            print(message, file=f)

    def execute(self, sql, parameters):
        self._log(sql)
        cursor = self.cursor()
        try:
            return cursor.execute(sql, parameters)
        except oracledb.Error as e:
            error_obj, = e.args
            self._log(error_obj.message)
            raise

connection = Connection()
connection.execute("""
        select department_name
        from departments
        where department_id = :id""", dict(id=270))

The messages logged in log.txt are:

Connect to the database

            select department_name
            from departments
            where department_id = :id

If an error occurs, perhaps due to a missing table, the log file would contain instead:

Connect to the database

            select department_name
            from departments
            where department_id = :id
ORA-00942: table or view "HR"."DEPARTMENTS" does not exist

In production applications, be careful not to log sensitive information.

See Subclassing.py for an example.

27.2. Python-oracledb Plugins

Plugins simplify extending python-oracledb functionality, and optionally allow the distribution of such extensions as modules. The plugin mechanism provides a loose coupling for python-oracledb, permitting different python-oracledb configurations.

Examples are the pre-supplied plugins for Centralized Configuration Providers. The loose coupling allows these plugins to be included with all python-oracledb installations but the large Python SDK modules used by the plugins are not installed as python-oracledb dependencies. Only users who want to use a specific configuration provider plugin need to install its required SDK.

All python-oracledb installations bundle the following plugins:

oci_config_provider which allows connection and pool creation calls to access connection configuration information stored in OCI Object Storage. See Using an OCI Object Storage Centralized Configuration Provider.
azure_config_provider which allows connection and pool creation calls to access connection configuration information stored using Azure App Configuration. See Using an Azure App Centralized Configuration Provider.
oci_tokens which uses the Oracle Cloud Infrastructure (OCI) Software Development Kit (SDK) to generate access tokens when authenticating with OCI Identity and Access Management (IAM) token-based authentication. See OCI Cloud Native Authentication with the oci_tokens Plugin.
azure_tokens which uses the Microsoft Authentication Library (MSAL) to generate access tokens when authenticating with OAuth 2.0 token-based authentication. See Azure Cloud Native Authentication with the azure_tokens Plugin.
end_user_sec_provider which automates the acquisition of an end user identity, and creates and sets an end user security context to connect to Oracle Database. See End User Security Provider Plugin.

To import these python-oracledb plugins in your application, use import oracledb.plugins.<name of plugin>, for example:

import oracledb.plugins.oci_tokens

Note that the namespace oracledb.plugins.ldap_support is reserved for future use by the python-oracledb project.

27.2.1. Storing Secret Values

Pre-supplied plugins such as end_user_sec_provider, oci_tokens, and azure_tokens, use python-oracledb’s SecretValue class to store and retrieve secret values such as tokens that are used to configure an end user security context. The save_secret() function is used to store a secret value with an expiry in python-oracledb’s internal cache, and get_secret() is used to retrieve this secret from the cache. The secret value can be stored in global storage which is shared across all threads, or in thread local storage where each thread has its own secret value.

The end_user_sec_provider plugin stores an end user’s identity value in python-oracledb’s internal cache under a fixed key using thread local storage. Using this same key, the identity value can be retrieved from the cache. This identity can be provided by an external Identity and Access Management (IAM) system such as OCI IAM and Microsoft Entra ID, or by Oracle Database. This pluginFor more information on using this plugin, see end_user_sec_provider.

The oci_tokens and azure_tokens plugins store the database access token in python-oracledb’s internal cache as a key which is configured from the parameter values in end_user_sec_params key such as auth_flow, authority, client_id, client_credentials, and scopes. Using this same key, the database access token is retrieved from the cache. For more information on using these plugins with end user security context, see end_user_sec_provider.

27.2.2. Building Custom Plugins

If you want to use the plugin mechanism for your own packages, you can create a namespace package. You can distribute plugin packages either internally within your organization, or on a package repository such as PyPI.

Example 1

The following example creates a plugin that uses a connection protocol hook function to do special processing of connection strings prefixed with “myprefix://”.

In a terminal or IDE, create a working directory, for example myplugin:
```
mkdir myplugin
```

In the myplugin directory, create the following files:

A README file:
```
My sample connection plugin.
```

A pyproject.toml file:

[build-system]
requires = ["setuptools"]
build-backend = "setuptools.build_meta"

A setup.cfg file:

[metadata]
name = myplugin
version = 1.0.0
description = Sample connection plugin for python-oracleb
long_description = file: README
long_description_content_type = text/markdown
author = Your Name
author_email = youremail@example.com
license = Apache Software License

[options]
zip_safe = False
package_dir =
    =src
install_requires = oracledb

[options.packages.find]
where = src

In the myplugin directory, create the subdirectory hierarchy src/oracledb/plugins/:
```
mkdir -p src/oracledb/plugins
```
Create the plugin’s code in myplugin/src/oracledb/plugins/myplugin.py:

import oracledb

def myhookfunc(protocol, arg, params):
    print(f"In myhookfunc: protocol={protocol} arg={arg}")
    params.parse_connect_string(arg)

oracledb.register_protocol("myprefix", myhookfunc)

In the myplugin directory, build the sample package:
```
python -m pip install build
python -m build
```
This creates your plugin package wheel in the dist subdirectory with a name like myplugin-1.0.0-py3-none-any.whl. You can distribute and install this package.

Install the sample package:

python -m pip install dist/myplugin-1.0.0-py3-none-any.whl

To show the plugin in use, create an application file in a working directory containing:

import oracledb
import oracledb.plugins.myplugin

cs = "myprefix://localhost/orclpdb"

cp = oracledb.ConnectParams()
cp.parse_connect_string(cs)

print(f"host={cp.host}, port={cp.port}, service name={cp.service_name}")

Running this will print:

In myhookfunc: protocol=myprefix arg=localhost/orclpdb
host=localhost, port=1521, service name=orclpdb

To uninstall the plugin, remove the package:
```
python -m pip uninstall myplugin
```

Example 2

The following sample plugin shows how all connection creations can be logged, regardless of the connection string. If the plugin myplugin/src/oracledb/plugins/myplugin.py created above contained code to register a connection parameter hook:

import oracledb

def my_params_hook(params: oracledb.ConnectParams):
    print(f"Connecting to the database as {params.user}")

oracledb.register_params_hook(my_params_hook)

Then running an application that contains:

connection = oracledb.connect(user="hr", password=userpwd,
                              dsn="dbhost.example.com/orclpdb")

will print the trace output:

Connecting to the database as hr

27.3. Connection Hooks

Connection hooks allow you to modify the connection logic based on your needs. The hooks supported by python-oracledb are listed in this section.

27.3.1. Connection Protocol Hooks

The oracledb.register_protocol() method registers a user protocol hook function that can be called internally by python-oracledb prior to connection or pool creation.

See Using Protocol Hook Functions for usage information.

27.3.2. Connection Password Hooks

The oracledb.register_password_type() method registers a user password hook function that will be called internally by python-oracledb prior to connection or pool creation.

See Using Password Hook Functions for usage information.

27.3.3. Connection Parameter Hooks

The oracledb.register_params_hook() method registers a user parameter hook function that will be called internally by python-oracledb prior to connection or pool creation.

See Using Parameter Hook Functions for usage information.