4. Connecting to Oracle Database — python-oracledb 3.2.0b1 documentation (original) (raw)

Connections between python-oracledb and Oracle Database are used for executingSQL and PL/SQL, for callingSODA functions, for receiving databasenotifications and messages, and forstarting and stopping the database.

This chapter covers python-oracledb’s synchronous programming model. For discussion of asynchronous programming, see Concurrent Programming with asyncio and Pipelining.

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.

If you intend to use the Thick mode, then you must callinit_oracle_client() in the application before any standalone connection or pool is created. The python-oracledb Thick mode loads Oracle Client libraries which communicate over Oracle Net to an existing database. The Oracle Client libraries need to be installed separately. SeeInstalling python-oracledb. Oracle Net is not a separate product: it is how the Oracle Client and Oracle Database communicate.

There are two ways to create a connection to Oracle Database using python-oracledb:

• Standalone connections: Standalone connectionsare useful when the application needs a single connection to a database. Connections are created by calling oracledb.connect().
• Pooled connections: Connection pooling is important for performance when applications frequently connect and disconnect from the database. Pools support Oracle’s high availability features and are recommended for applications that must be reliable. Small pools can also be useful for applications that want a few connections available for infrequent use. Pools are created with oracledb.create_pool() at application initialization time, and then ConnectionPool.acquire() can be called to obtain a connection from a pool.

Many connection behaviors can be controlled by python-oracledb connection options. Other settings can be configured in Optional Oracle Net Configuration Files or inOptional Oracle Client Configuration File. These include limiting the amount of time that opening a connection can take, or enabling network encryption.

Note

Creating a connection in python-oracledb Thin mode always requires a connection string, or the database host name and service name, to be specified. The Thin mode cannot use “bequeath” connections and does not reference Oracle environment variables ORACLE_SID, TWO_TASK, or LOCAL.

4.1. Standalone Connections

Standalone connections are database connections that do not use a python-oracledb connection pool. They are useful for simple applications that use a single connection to a database. Simple connections are created by calling oracledb.connect() and passing:

• A database username
• The database password for that user
• A ‘data source name’ connection string, see Oracle Net Services Connection Strings

Python-oracledb also supports external authentication so passwords do not need to be in the application.

4.1.1. Creating a Standalone Connection

Standalone connections are created by calling oracledb.connect().

A simple standalone connection example:

import oracledb import getpass

userpwd = getpass.getpass("Enter password: ")

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

You could alternatively read the password from an environment variable:

userpwd = os.environ.get("PYTHON_PASSWORD")

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

The oracledb.connect() method allows the database host name and database service name to be passed as separate parameters. The database listener port can also be passed:

import os

userpwd = os.environ.get("PYTHON_PASSWORD")

connection = oracledb.connect(user="hr", password=userpwd, host="localhost", port=1521, service_name="orclpdb")

A single, combined connection string can be passed to connect() but this may cause complications if the password contains “@” or “/” characters:

username="hr" userpwd = os.environ.get("PYTHON_PASSWORD") host = "localhost" port = 1521 service_name = "orclpdb"

dsn = f'{username}/{userpwd}@{host}:{port}/{service_name}' connection = oracledb.connect(dsn)

If you like to encapsulate values, parameters can be passed using aConnectParams Object:

params = oracledb.ConnectParams(host="my_host", port=my_port, service_name="my_service_name") conn = oracledb.connect(user="my_user", password="my_password", params=params)

Some values such as the database host name can be specified as connect()parameters, as part of the dsn connection string, and in the paramsobject. A final connection string is internally constructed from any dsn, individual parameters, and params object values. The precedence is that values in a dsn parameter override values passed as individual parameters, which themselves override values set in the params object.

4.1.1.1. Closing Connections

Connections should be released when they are no longer needed. You may prefer to let connections be automatically cleaned up when references to them go out of scope. This lets python-oracledb close dependent resources in the correct order. For example, you can use a Python context manager with block:

with oracledb.connect(user="hr", password=userpwd, dsn="myhostname/orclpdb") as connection: with connection.cursor() as cursor: cursor.execute("insert into SomeTable values (:1)", ("Some string")) connection.commit()

This code ensures that once the block is completed, the connection is closed and resources have been reclaimed by the database. In addition, any attempt to use the variable connection outside of the block will simply fail.

Alternatively, you can explicitly close a connection by calling.Connection.close():

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

do something with the connection

. . .

close the connection

connection.close()

If you explicitly close connections you may also need to close other resources first.

4.1.2. Common Connection Errors

Some of the common connection errors that you may encounter in the python-oracledb’s default Thin mode are detailed below. Also seeError Handling in Thin and Thick Modes.

4.1.2.1. Use keyword parameters

If you use:

connection = oracledb.connect("hr", userpwd, "localhost/orclpdb")

then you will get the error:

TypeError: connect() takes from 0 to 1 positional arguments but 3 were given

The oracledb.connect() method requires keyword parameters to be used

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

The exception passing a single argument containing the combined credential and connection string. This is supported:

connection = oracledb.connect("hr/userpwd@localhost/orclpdb")

4.1.2.2. Use the correct credentials

If your username or password are not known by the database that you attempted to connect to, then you will get the error:

ORA-01017: invalid credential or not authorized; logon denied

Find the correct username and password and try reconnecting.

4.1.2.3. Use the correct connection string

If the hostname, port, or service name are incorrect, then the connection will fail with the error:

DPY-6001: cannot connect to database. Service "doesnotexist" is not registered with the listener at host "localhost" port 1521. (Similar to ORA-12514)

This error means that Python successfully reached a computer (in this case, “localhost” using the default port 1521) that is running a database. However, the database service you wanted (“doesnotexist”) does not exist there.

Technically, the error means the listener does not know about the service at the moment. So you might also get this error if the database is currently restarting.

This error is similar to the ORA-12514 error that you may see when connecting with python-oracledb in Thick mode, or with some other Oracle tools.

The solution is to use a valid service name in the connection string. You can:

• Check and fix any typos in the service name you used
• Check if the hostname and port are correct
• Ask your database administrator (DBA) for the correct values
• Wait a few moments and re-try in case the database is restarting
• Review the connection information in your cloud console or cloud wallet, if you are using a cloud database
• Run lsnrctl status on the database machine to find the known service names

4.2. Oracle Net Services Connection Strings

The data source name parameter dsn of oracledb.connect(),oracledb.create_pool(), oracledb.connect_async(), andoracledb.create_pool_async(), is the Oracle Database Oracle Net Services Connection String (commonly abbreviated as “connection string”) that identifies which database service to connect to. The dsn value can be one of Oracle Database’s naming methods:

• An Oracle Easy Connect string
• A Connect Descriptor
• A TNS Alias mapping to a Connect Descriptor stored in atnsnames.ora file
• An LDAP URL
• A Configuration Provider URL

Connection strings used for JDBC and Oracle SQL Developer need to be altered to be usable as the dsn value, see JDBC and Oracle SQL Developer Connection Strings.

For more information about naming methods, see the Database Net Services Administrator’s Guide.

4.2.1. Easy Connect Syntax for Connection Strings

An Easy Connect string is often the simplest connection string to use in the data source name parameter dsn of connection functions such as oracledb.connect(),oracledb.create_pool(), oracledb.connect_async(), andoracledb.create_pool_async().

Using Easy Connect strings means that an external tnsnames.ora configuration file is not needed.

The Easy Connect syntax in python-oracledb is:

[[protocol:]//]host1{,host12}[:port1]{,host2:port2}{;host1{,host12}[:port1]}[/[service_name][:server][/instance_name]][?parameter_name=value{&parameter_name=value}]

See the Database Net Services Administrator’s Guideand the technical brief Oracle Database Easy Connect Plus for more details.

For example, to connect to the Oracle Database service orclpdb that is running on the host dbhost.example.com with the default Oracle Database port 1521, use:

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

If the database is using a non-default port, it must be specified:

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

The Easy Connect syntax supports Oracle Database service names. It cannot be used with the older System Identifiers (SID).

Oracle Net Settings in Easy Connect Strings

The Easy Connect syntax allows some Oracle Network and database configuration options to be set. This means that a sqlnet.ora file is not needed for common connection scenarios.

For example, to set a connection timeout and keep-alive value:

connection = oracledb.connect(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb?transport_connect_timeout=10&expire_time=2")

For more information, see Oracle Net Connect Descriptor and Easy Connect Keywords. Any Easy Connect parameters that are not known to python-oracledb are ignored and not passed to the database.

Python-oracledb Settings in Easy Connect Strings

Many python-oracledb connection method API arguments can alternatively be passed as Easy Connect parameters with a “pyo.” prefix. For example, to set the statement cache size used by connections:

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

See Python-oracledb Parameters Settable in Easy Connect Strings or Central Configuration Providers for the usable attributes.

4.2.2. Connect Descriptors

Connect Descriptors can be embedded directly in python-oracledb applications, or referenced via a TNS Alias.

An example of direct use is:

dsn = """(DESCRIPTION= (FAILOVER=on) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=sales.example.com)))"""

connection = oracledb.connect(user="hr", password=userpwd, dsn=dsn)

The oracledb.ConnectParams() andConnectParams.get_connect_string() functions can be used to construct a connect descriptor from the individual components, see Using the ConnectParams Builder Class. For example:

cp = oracledb.ConnectParams(host="dbhost.example.com", port=1521, service_name="orclpdb") dsn = cp.get_connect_string() print(dsn)

This prints:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclpdb)))

Syntax is shown in the Database Net Services Reference.

Any DESCRIPTION, CONNECT_DATA and SECURITY parameters of a full connect descriptor that are unrecognized by python-oracledb are passed to the database unchanged.

4.2.3. TNS Aliases for Connection Strings

Connect Descriptors are commonly stored in atnsnames.ora file and associated with a TNS Alias. This alias can be used directly for the data source name parameter dsn oforacledb.connect(), oracledb.create_pool(),oracledb.connect_async(), and oracledb.create_pool_async(). For example, given a file /opt/oracle/config/tnsnames.ora with the following contents:

ORCLPDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclpdb) ) )

Then you could connect by passing the TNS Alias “ORCLPDB” (case insensitive) as the dsn value:

connection = oracledb.connect(user="hr", password=userpwd, dsn="orclpdb", config_dir="/opt/oracle/config")

In python-oracledb Thick mode, the configuration directory can also be set during library initialization:

oracledb.init_oracle_client(config_dir="/opt/oracle/config") connection = oracledb.connect(user="hr", password=userpwd, dsn="orclpdb")

More options for how python-oracledb locates tnsnames.orafiles are detailed in Using Optional Oracle Configuration Files.

TNS Aliases may also be resolved by LDAP.

For more information about Net Service Names, see Database Net Services Reference.

4.2.4. LDAP URL Connection Strings

The python-oracledb connection string can be an LDAP URL like:

ldapurl = "ldaps://ldapserver.example.com/cn=orcl,cn=OracleContext,dc=example,dc=com" connection = oracledb.connect(user="scott", password=pw, dsn=ldapurl)

This syntax removes the need for external LDAP and sqlnet.ora configuration files. See the technical brief Oracle Client 23ai LDAP URL Syntax.

In python-oracledb Thin mode, an additional connection protocol hook function is required to handle this connection protocol, see LDAP Directory Naming. A connection protocol hook function is also required in python-oracledb Thick mode ifdefaults.thick_mode_dsn_passthrough is False.

To use LDAP URLs in python-oracledb Thick mode applications whendefaults.thick_mode_dsn_passthrough is True, the Oracle Client libraries must be 23.4, or later.

4.2.5. Centralized Configuration Provider URL Connection Strings

A Centralized Configuration Provider URL connection string allows python-oracledb configuration information to be stored centrally in OCI Object Storage, in Azure App Configuration, or in a local file. Given a provider URL, python-oracledb will access the information stored in the configuration provider and use it to connect to Oracle Database.

The database connect descriptor and any database credentials stored in a configuration provider will be used by any language driver that accesses the configuration. Other driver-specific sections can exist. Python-oracledb will take settings that are in a section with the prefix “pyo”, and will ignore other sections.

For example, to use connection configuration stored in a local file/opt/oracle/my-config.json:

{ "connect_descriptor": "localhost/orclpdb", "pyo": { "min": 5, "max": 10, "increment": 2 "stmtcachesize": 4 } }

You could use this to create a connection pool by specifying the dsnconnection string parameter as:

pool = oracledb.create_pool(user="hr", password=userpwd, dsn="config-file:///opt/oracle/my-config.json")

The pool will be created using the pool settings from the configuration.

The Centralized Configuration Provider URL must begin with “config-://” where the configuration-provider value can be set to ociobject, azure, or file, depending on the location of your configuration information.

See Centralized Configuration Providers for more information, particularly regarding using python-oracledb Thick mode.

The valid keys for the “pyo” object are shown in Python-oracledb Parameters Settable in Easy Connect Strings or Central Configuration Providers.

4.2.6. JDBC and Oracle SQL Developer Connection Strings

The python-oracledb connection string syntax is different from Java JDBC and the common Oracle SQL Developer syntax. If these JDBC connection strings reference a service name like:

jdbc:oracle:thin:@hostname:port/service_name

For example:

jdbc:oracle:thin:@dbhost.example.com:1521/orclpdb

then use Oracle’s Easy Connect syntax in python-oracledb:

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

You may need to remove JDBC-specific parameters from the connection string and use python-oracledb alternatives.

If a JDBC connection string uses an old-style Oracle Database SID “system identifier”, and the database does not have a service name:

jdbc:oracle:thin:@hostname:port:sid

For example:

jdbc:oracle:thin:@dbhost.example.com:1521:orcl

then connect by using the sid parameter:

connection = oracledb.connect(user="hr", password=userpwd, host="dbhost.example.com", port=1521, sid="orcl")

Alternatively, create a tnsnames.ora entry (see Optional Oracle Net Configuration Files), for example:

finance = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.com)(PORT = 1521)) (CONNECT_DATA = (SID = ORCL) ) )

This can be referenced in python-oracledb:

connection = oracledb.connect(user="hr", password=userpwd, dsn="finance")

4.2.7. Oracle Net Connect Descriptor and Easy Connect Keywords

Easy Connect syntax is described in Easy Connect Syntax for Connection Strings.

Connect Descriptor keywords are shown in the Database Net Services Reference.

Notes on specific keywords

The POOL_CONNECTION_CLASS or POOL_PURITY values will only work when connected to Oracle Database 21c, or later. Note if POOL_PURITY=SELF is used in a connect string, then python-oracledb Thick mode applications will ignore the action to drop the session when attempting to remove an unusable connections from a pool in some uncommon error cases. It is recommended to avoid using POOL_PURITY=SELF in a connect string with python-oracledb Thick mode. Instead, code python-oracledb Thick mode applications to explicitly specify the purity and connection class as attributes.

The ENABLE=BROKEN connect descriptor option is not supported by python-oracledb Thin mode. Use EXPIRE_TIME instead.

If a name is given as a connect string, then python-oracledb will consider it as a Net Service Name and not as the minimal Easy Connect string of a hostname. The given connect string will be looked up in a tnsnames.ora file. If supporting a bare name as a hostname is important to you in python-oracledb, then you can alter the connection string to include a protocol such as tcp://hostname, or a port number such ashostname:1521.

In python-oracledb Thick mode, when defaults.thick_mode_dsn_passthroughis False, any DESCRIPTION, CONNECT_DATA and SECURITY parameters of a full connect descriptor that are unrecognized by python-oracledb are passed to the database unchanged. Any Easy Connect parameters that are not known to python-oracledb are discarded and not passed to the database.

4.2.8. Python-oracledb Parameters Settable in Easy Connect Strings or Central Configuration Providers

Some python-oracledb connection and pool creation parameters can be set inEasy Connect strings or via a Centralized Configuration Provider. This is an alternative to passing explicit arguments to oracledb.connect(),oracledb.create_pool(), oracledb.connect_async(), ororacledb.create_pool_async(). This allows application behavior to be changed without needing application code to be updated.

The parameters are shown below in this table. Parameters have a “pyo.” prefix or are under a “pyo” key. Each of these parameters that is defined in an Easy Connect string or via a Centralized Configuration Provider will take precedence over the value passed as the equivalent python-oracledb API parameter.

Parameters that apply to pool creation will be ignored if they are used in the context of standalone connections. Parameters with unknown names will be ignored in both cases.

Python-oracledb Parameters in Easy Connect Strings

The Easy Connect parameter names are similar to the python-oracledb method argument names, but have a “pyo.” prefix. For example:

cs = "host.example.com:1522/orclpdb?pyo.stmtcachesize=30&pyo.mode=SYSDBA" connection = oracledb.connect(user="hr", password=userpwd, dsn=cs)

is the same as:

cs = "host.example.com:1522/orclpdb" connection = oracledb.connect(user="hr", password=userpwd, dsn=cs, stmtcachesize=30, mode=oracledb.AuthMode.SYSDBA)

If a parameter is specified multiple times in an Easy Connect string, then the last value of that parameter is used. For example, in “localhost/orclpdb?pyo.sdu=10&pyo.sdu=20” the SDU is set to 20.

Note some Oracle Net parameters can also be prefixed with “pyo.”.

Parameters with the prefix “pyo.” can only be used in Easy Connect strings and not in Connect Descriptors.

Python-oracledb Parameters in Configuration Providers

With the File Centralized Configuration Provider orOCI Object Storage Centralized Configuration Provider, the settable python-oracledb driver attributes should be in the JSON file under the key “pyo”. An example is:

{ "connect_descriptor": "localhost/orclpdb", "pyo": { "min": 5, "max": 10, "increment": 2 "stmtcachesize": 4 } }

With Azure App Configuration, values are set using a key such as “/pyo/”. This is similar to how Oracle Call Interface settings use the key “/oci/” as shown inOracle Net Service Administrator’s Guide.

Parameter Names

When used in Easy Connect Strings, the parameter names should be prefixed with “pyo.”. When used in a Centralized Configuration Provider, the parameter names are used to form the key names under a parent “pyo” key or with a “pyo/” prefix. The names are case insensitive.

Table 4.1 Python-oracledb parameters usable in Easy Connect Strings or Centralized Configuration Providers

4.3. Centralized Configuration Providers

Centralized Configuration Providers allow the storage and management of database connection credentials and application configuration information in a central location. Providers allow you to separately store configuration information from the code of your application. The values that can be stored includes the database connection string, database credentials, a cache time, and python-oracledb specific attributes such as connection pool settings. Python-oracledb can use the centrally stored information to connect to Oracle Database with oracledb.connect(),oracledb.create_pool(), oracledb.connect_async(), andoracledb.create_pool_async().

The following configuration providers are supported by python-oracledb:

• File Centralized Configuration Provider
• Oracle Cloud Infrastructure (OCI) Object Storage Centralized Configuration Provider
• Microsoft Azure App Centralized Configuration Provider

To use python-oracledb Centralized Configuration Provider functionality in Thick mode, you should setdefaults.thick_mode_dsn_passthrough to False. Alternatively useConnectParams.parse_connect_string(), see Using the ConnectParams Builder Class.

Note: In Thick mode, when defaults.thick_mode_dsn_passthrough is_True_, it is the Oracle Client libraries that access the configuration provider when python-oracledb connection or pool creation methods are invoked. Any python-oracledb parameter section will be ignored. Any Oracle Client Interface parameter section should be removed from the configuration because its values may be different to those that python-oracledb assumes, and will cause undefined behavior.

Precedence of Attributes

Defining attributes in multiple places is not recommended. However, if you have defined the values of user and password in both the application and the configuration provider, then the values defined in the application will have the higher precedence. If the externalauth parameter is set to True, then the user and password values specified in the configuration provider are ignored.

If other python-oracledb connection attributes have been defined in both the application and the configuration provider, then the values defined in the configuration provider will have higher precedence.

If you are using Thick mode, and have defined python-oracledb attributes in anoraaccess.xml file (see Optional Oracle Client Configuration File), the configuration provider, and the application, then the values defined in the configuration provider will have the higher precedence followed by the oraaccess.xml file settings, and then application settings.

4.3.1. Using a File Centralized Configuration Provider

The File Centralized Configuration Provider enables the storage and management of Oracle Database connection information using local files.

To use a File Centralized Configuration Provider, you must:

1. Store the connection information in a JSON file on your local file system.
2. Set the path to the file in the dsn parameter of connection and pool creation methods.

File Centralized Configuration Provider JSON File Syntax

The configuration file must contain at least a connect_descriptor key to specify the database connection string. Optionally, you can store the database user name, password, a cache time, and python-oracledb settings. The keys that can be stored in the file are:

Table 4.2 JSON keys for the File Configuration Provider

See the Oracle Net Service Administrator’s Guide for more information on the generic provider sub-objects usable in JSON files.

Multiple configurations can be defined by specifying the above keys under user-chosen, top-level keys, see the example further below.

File Centralized Configuration Provider DSN Syntax

To use a file provider, specify the dsn parameter oforacledb.connect(), oracledb.create_pool(),oracledb.connect_async(), or oracledb.create_pool_async() using the following format:

config-file://[?key=]

The elements of the dsn parameter are detailed in the table below.

Table 4.3 Connection String Parameters for File Configuration Provider

File Configuration Provider Examples

An example of File Configuration Provider file syntax is:

{ "user": "scott", "password": { "type": "base64", "value": "dGlnZXI=" }, "connect_descriptor": "dbhost.example.com:1522/orclpdb", "pyo": { "stmtcachesize": 30, "min": 2, "max": 10 } }

This encodes the password as base64. See Using an OCI Object Storage Centralized Configuration Provider for other password examples. Plaintext passwords are not supported.

Note that python-oracledb caches configurations by default, seeCaching Configuration Information.

If you have this configuration file in /opt/oracle/my-config1.json, you could use it like:

connection = oracledb.connect(dsn="config-file:///opt/oracle/my-config1.json")

Multiple configurations can be defined by specifying user-chosen top-level keys:

{ "production": { "connect_descriptor": "localhost/orclpdb" }, "testing": { "connect_descriptor": "localhost/orclpdb", "user": "scott", "password": { "type": "base64", "value": "dGlnZXI=" } } }

If you have this configuration file in /opt/oracle/my-config2.json, you could use it like:

connection = oracledb.connect(user="hr", password=userpwd, dsn="config-file:///opt/oracle/my-config2.json?key=production")

4.3.2. Using an OCI Object Storage Centralized Configuration Provider

The Oracle Cloud Infrastructure (OCI) Object Storage configuration provider enables the storage and management of Oracle Database connection information as JSON in OCI Object Storage.

To use an OCI Object Storage Centralized Configuration Provider, you must:

1. Upload a JSON file that contains the connection information into an OCI Object Storage Bucket. See Uploading an Object Storage Object to a Bucket and the Oracle Net Service Administrator’s Guide for the steps. See OCI Object Storage Centralized Configuration Provider Parameters for the configuration information that can be added.
2. Install the Python OCI module, seeInstall Modules for the OCI Object Storage Centralized Configuration Provider.
3. Import the oracledb.plugins.oci_config_providerplugin in your application.
4. Use an OCI Object Storage connection string URLin the dsn parameter of connection and pool creation methods.

OCI Object Storage Centralized Configuration Provider JSON File Syntax

The stored JSON configuration file must contain a connect_descriptor key. Optionally, you can specify the database user name, password, a cache time, and python-oracledb attributes. The database password can also be stored securely using OCI Vault or Azure Key Vault. The keys that can be in the JSON file are listed below.

Table 4.4 JSON Keys for OCI Object Storage Configuration Provider

OCI Object Storage Centralized Configuration Provider DSN Syntax

The dsn parameter for oracledb.connect(),oracledb.create_pool(), oracledb.connect_async(), ororacledb.create_pool_async() calls should use a connection string URL in the format:

config-ociobject:/n/{namespaceName}/b/{bucketName}/o/ [/c/][?=&=...]

The elements of the connection string are detailed in the table below.

Table 4.5 Connection String Parameters for OCI Object Storage

OCI Object Storage Centralized Configuration Provider Examples

An example of OCI Object Centralized Storage Configuration Provider JSON file syntax is:

{ "user": "scott", "password": { "type": "ocivault", "value": "oci.vaultsecret.my-secret-id" "authentication": { "method": "OCI_INSTANCE_PRINCIPAL" } }, "connect_descriptor": "dbhost.example.com:1522/orclpdb", "pyo": { "stmtcachesize": 30, "min": 2, "max": 10 } }

Passwords can optionally be stored using the Azure Key Vault. To do this, you must import the oracledb.plugins.azure_config_provider python-oracledb plugin in your application and you must define the Azure Key Vault credentials in the password key. In this, theazure_client_id and azure_tenant_id must be specified. Also, eitherazure_client_secret or azure_client_certificate_path should be specified. For example:

"password": { "type": "azurevault", "value": "", "authentication": { "azure_tenant_id": "", "azure_client_id": "", "azure_client_secret": "" } }

Or:

"password": { "type": "azurevault", "value": "", "authentication": { "azure_tenant_id": "", "azure_client_id": "", "azure_client_certificate_path": "" } }

Note that python-oracledb caches configurations by default, seeCaching Configuration Information.

An example of a connection string for the OCI Object Centralized Storage configuration provider is:

configociurl = "config-ociobject://abc.oraclecloud.com/n/abcnamespace/b/abcbucket/o/abcobject?authentication=oci_default&oci_tenancy=abc123&oci_user=ociuser1&oci_fingerprint=ab:14:ba:13&oci_key_file=ociabc/ocikeyabc.pem"

To create a standalone connection you could use this like:

import oracledb.plugins.oci_config_provider

configociurl = "config-ociobject://abc.oraclecloud.com/n/abcnamespace/b/abcbucket/o/abcobject?authentication=oci_default&oci_tenancy=abc123&oci_user=ociuser1&oci_fingerprint=ab:14:ba:13&oci_key_file=ociabc/ocikeyabc.pem"

connection = oracledb.connect(dsn=configociurl)

The configuration can also be used to create a connection pool, for example:

pool = oracledb.create_pool(dsn=configociurl)

4.3.3. Using an Azure App Centralized Configuration Provider

Azure App Configuration is a cloud-based service provided by Microsoft Azure. It can be used for storage and management of Oracle Database connection information as key-value pairs.

To use python-oracledb with Azure App Configuration, you must:

1. Save your configuration information in your Azure App Configuration Provider. See Azure App Centralized Configuration Provider Parameters.
2. Install the Azure App modules, see Install Modules for the Azure App Centralized Configuration Provider.
3. Import the oracledb.plugins.azure_config_providerplugin in your application.
4. Use an Azure App Configuration connection string URL in the dsn parameter of connection and pool creation methods.

Azure App Centralized Configuration Provider Parameters

Key-value pairs for stored connection information can be added using the Configuration explorer page of your Azure App Configuration. See Create a key-value in Azure App Configuration for more information. Alternatively, they can be set by making REST calls. Also see the Oracle Net Service Administrator’s Guide.

You can organize the key-value pairs under a prefix based on your application’s needs. For example, if you have two applications, Sales and Human Resources, then you can store the relevant configuration information under the prefix sales and the prefix hr.

The key-value pairs must contain the key connect_descriptor which specifies the database connection string. This can be set using a prefix as “/connect_descriptor”, for example, sales/connect_descriptor.

You can additionally store the database user name using a key such as “/user”, and store the password using “/password”. For example,sales/user and sales/password. The database password can also be stored securely using Azure Key Vault. A cache time can optionally be stored using “/config_time_to_live”. For example, sales/60000. SeeCaching Configuration Information.

Optional python-oracledb settings can be set using a key such as “/pyo/”, for example sales/pyo/min. This is similar to howOracle Call Interface settings use keys like “/oci/” as shown inOracle Net Service Administrator’s Guide.

The keys that can be added in Azure App Configuration are listed below:

Table 4.6 Keys for Azure App Configuration

Azure App Centralized Configuration Provider DSN Syntax

You must define a connection string URL in a specific format in the dsnparameter of oracledb.connect(), oracledb.create_pool(),oracledb.connect_async(), or oracledb.create_pool_async() to access the information stored in Azure App Configuration. The syntax is:

config-azure://[?key=&label=&=&=...]

The elements of the connection string are detailed in the table below.

Table 4.7 Connection String Parameters for Azure App Centralized Configuration Provider

Azure App Centralized Configuration Examples

The following table shows sample configuration information defined using the Configuration explorer page of your Azure App Configuration provider. The example uses the prefix test/.

Note that python-oracledb caches configurations by default, seeCaching Configuration Information.

An example of a connection string for the Azure App Configuration provider is:

configazureurl = "config-azure://aznetnamingappconfig.azconfig.io/?key=test/&azure_client_id=123-456&azure_client_secret=MYSECRET&azure_tenant_id=789-123"

An example using a standalone connection is:

import oracledb.plugins.azure_config_provider

configazureurl = "config-azure://aznetnamingappconfig.azconfig.io/?key=test/&azure_client_id=123-456&azure_client_secret=MYSECRET&azure_tenant_id=789-123"

oracledb.connect(dsn=configazureurl)

The configuration can also be used to create a connection pool, for example:

oracledb.create_pool(dsn=configazureurl)

4.3.4. Caching Configuration Information

Python-oracledb caches configurations obtained from Centralized Configuration Providers to reduce access overheads.

You can use the config_time_to_live configuration key to specify the number of seconds that python-oracledb should keep the information cached. The default time is 86,400 seconds (24 hours).

When config_time_to_live is reached, the configuration is considered to be “softly expired” and subsequent python-oracledb connections will attempt to obtain the configuration again from the configuration provider. If it cannot be retrieved, python-oracledb will continue to use the previous configuration for up to config_time_to_live_grace_period seconds which defaults to 1,800 seconds (30 minutes). After this grace period the cached configuration fully expires. Future connection attempts will try to retrieve the configuration from the provider but will fail if the new configuration cannot be obtained.

An example of changing the cache time to 12 hours with an additional grace time of 10 minutes for the File or OCI Object Storage Centralized Configuration Providers is:

{ "connect_descriptor": "dbhost.example.com:1522/orclpdb", "config_time_to_live": 43200, "config_time_to_live_grace_period": 600, "pyo": { "stmtcachesize": 30, "min": 2, "max": 10 } }

4.4. Using the ConnectParams Builder Class

The ConnectParams class allows you to define connection parameters in a single place. The oracledb.ConnectParams() function returns a ConnectParams object. The object can be passed tooracledb.connect() or oracledb.connect_async(). For example:

cp = oracledb.ConnectParams(user="hr", password=userpwd, host="dbhost", port=1521, service_name="orclpdb") connection = oracledb.connect(params=cp)

For connection pools, see Using the PoolParams Builder Class.

The use of the ConnectParams class is optional because you can pass the same parameters directly to connect(). For example, the code above is equivalent to:

connection = oracledb.connect(user="hr", password=userpwd, host="dbhost", port=1521, service_name="orclpdb")

If you want to keep credentials separate, you can use ConnectParams just to encapsulate connection string components:

cp = oracledb.ConnectParams(host="dbhost", port=1521, service_name="orclpdb") connection = oracledb.connect(user="hr", password=userpwd, params=cp)

You can use ConnectParams.get_connect_string() to get a connection string from a ConnectParams object:

cp = oracledb.ConnectParams(host="dbhost", port="my_port", service_name="my_service_name") dsn = cp.get_connect_string() connection = oracledb.connect(user="hr", password=userpwd, dsn=dsn)

Some values such as the database host name can be specified as connect()parameters, as part of the dsn connection string, and in the paramsobject. A final connection string is internally constructed from any dsn, individual parameters, and params object values. The precedence is that values in a dsn parameter override values passed as individual parameters, which themselves override values set in the params object.

To parse a connection string and store its components as attributes of a ConnectParams instance, use ConnectParams.parse_connect_string(). For example:

dsn = "host.example.com:1522/orclpdb?transport_connect_timeout=15&pyo.stmtcachesize=30" cp = oracledb.ConnectParams() cp.parse_connect_string(dsn)

connection = oracledb.connect(user="hr", password=userpwd, params=cp)

Most parameter values of oracledb.ConnectParams() are gettable as attributes. For example, to get the stored host name:

Attributes such as the password are not gettable.

You can set individual default attributes using ConnectParams.set():

cp = oracledb.ConnectParams(host="localhost", port=1521, service_name="orclpdb")

set a new port

cp.set(port=1522)

change both the port and service name

cp.set(port=1523, service_name="orclpdb")

Note ConnectParams.set() has no effect afterConnectParams.parse_connect_string() has been called.

The method ConnectParams.parse_dsn_with_credentials() can be used to extract the username, password, and connection string from a DSN:

cp = oracledb.ConnectParams() (un,pw,cs) = cp.parse_dsn_with_credentials("scott/tiger@localhost/orclpdb")

print(un) # scott print(pw) # tiger print(cs) # localhost/orclpdb

Any component not found in the DSN is returned as None.

The method ConnectParams.get_network_service_names() can be used to get a list of the network service names that are defined in the tnsnames.ora file. The directory that contains file can be specified in theconfig_dir attribute.

cp = oracledb.ConnectParams(host="my_host", port=my_port, dsn="orclpdb", config_dir="/opt/oracle/config") cp.get_network_service_names()

If ConnectParams.get_network_service_names() is called but atnsnames.ora file does not exist, then an error such as the following is returned:

DPY-4026: file tnsnames.ora not found in /opt/oracle/config

If config_dir is not specified, then the following error is returned:

DPY-4027: no configuration directory specified

When creating a standalone connection (or connection pool with aPoolParams class) the equivalent internal extraction is done automatically when a value is passed for the dsn parameter oforacledb.connect(), oracledb.connect_async(),oracledb.create_pool(), or oracledb.create_pool_async() but no value is passed for the user parameter.

4.5. Connection Hook Functions

Python-oracledb supports protocol, password, and parameter hook functions that can be used to customize connection logic.

4.5.1. Using Protocol Hook Functions

The oracledb.register_protocol() method registers a user protocol hook function that will be called internally by python-oracledb Thin mode prior to connection or pool creation. The hook function will be invoked whenoracledb.connect(), oracledb.create_pool(),oracledb.connect_async(), or oracledb.create_pool_async() are called with a dsn parameter value prefixed with a specified protocol. Your hook function is expected to construct valid connection details, which python-oracledb will use to complete the connection or pool creation.

You can also make use of a protocol hook function in python-oracledb Thick mode connection calls by setting defaults.thick_mode_dsn_passthrough to_False_. Alternatively use ConnectParams.parse_connect_string(), seeUsing the ConnectParams Builder Class.

For example, the following hook function handles connection strings prefixed with the tcp:// protocol. When oracledb.connect() is called, the sample hook is invoked internally. It prints the parameters, and sets the connection information in the params parameter (without passing thetcp:// prefix to parse_connect_string() otherwise recursion would occur). This modified ConnectParams object is used by python-oracledb to establish the database connection:

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

oracledb.register_protocol("tcp", myprotocolhook)

connection = oracledb.connect(user="scott", password=userpwd, dsn="tcp://localhost/orclpdb")

with connection.cursor() as cursor: for (r,) in cursor.execute("select user from dual"): print(r)

The output would be:

In myprotocolhook: protocol=tcp arg=localhost/orclpdb SCOTT

The params attributes can be set withConnectParams.parse_connect_string(), as shown, or by usingConnectParams.set().

See LDAP Directory Naming for a fuller example.

Internal protocol hook functions for the “tcp” and “tcps” protocols are pre-registered but can be overridden, if needed. If any other protocol has not been registered, then connecting will result in an error.

Calling register_protocol() with the hook_functionparameter set to None will result in a previously registered user function being removed and the default behavior restored.

Connection Hooks and parse_connect_string()

A registered user protocol hook function will also be invoked in python-oracledb Thin or Thick modes whenConnectParams.parse_connect_string() is called with aconnect_string parameter beginning with the registered protocol. The hook function params value will be the invoking ConnectParams instance that you can update using ConnectParams.set() orConnectParams.parse_connect_string().

For example, with the hook myprotocolhook shown previously, then the code:

cp = oracledb.ConnectParams() cp.set(port=1234) print(f"host is {cp.host}, port is {cp.port}, service name is {cp.service_name}") cp.parse_connect_string("tcp://localhost/orclpdb") print(f"host is {cp.host}, port is {cp.port}, service name is {cp.service_name}")

prints:

host is None, port is 1234, service name is None In myprotocolhook: protocol=tcp arg=localhost/orclpdb host is localhost, port is 1234, service name is orclpdb

If you have an application that can run in either python-oracledb Thin or Thick modes, and you want a registered connection protocol hook function to be used in both modes, your connection code can be like:

dsn = "tcp://localhost/orclpdb"

cp = oracledb.ConnectParams() cp.parse_connect_string(dsn) connection = oracledb.connect(user="hr", password=userpwd, params=cp)

4.5.2. Using Password Hook Functions

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 when oracledb.connect(),oracledb.create_pool(), oracledb.connect_async(), ororacledb.create_pool_async() are called. If the password,newpassword, or wallet_password parameters to those methods are a dictionary containing the key “type”, then the registered user password hook function for the specific type will be invoked. Your hook function is expected to accept the dictionary and return the actual password string.

Below is an example of a password hook function that handles passwords of type base64 stored in a dict like “dict(type=’base64’, value=’dGlnZXI=’)”. Note this specific hook function is already included and registered in python-oracledb:

def mypasswordhook(args): return base64.b64decode(args["value"].encode()).decode()

oracledb.register_password_type("base64", mypasswordhook)

When oracledb.connect() is called as shown below, the sample hook is invoked internally. It decodes the base64-encoded string in the key “value” and returns the password which is then used by python-oracledb to establish a connection to the database:

connection = oracledb.connect(user="scott", password=dict(type="base64", value="dGlnZXI="), dsn="localhost/orclpdb")

Calling register_password_type() with the hook_functionparameter set to None will result in a previously registered user function being removed.

4.5.3. Using Parameter Hook Functions

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 when oracledb.connect(),oracledb.create_pool(), oracledb.connect_async(), ororacledb.create_pool_async() are called. Your parameter hook function should accept a copy of the parameters that will be used to create the pool or standalone connections. The function can access and modify them in any way necessary to allow python-oracledb to subsequently complete the connection or pool creation request.

Pre-supplied python-oracledb plugins such as the OCI Cloud Native Authentication Plugin (oci_tokens) make use oforacledb.register_params_hook(). This plugin uses the information found in a connection method’s extra_auth_params parameter and modifies theaccess_token parameter with a function that will acquire the authentication token needed to complete a connection. Refer to the complete plugin implementation in oci_tokens.py. The key code section showing registering of a parameter hook function is:

def oci_token_hook(params: oracledb.ConnectParams):

if params.extra_auth_params is not None:

def token_callback(refresh):
  return generate_token(params.extra_auth_params, refresh)

params.set(access_token=token_callback)

oracledb.register_params_hook(oci_token_hook)

Your code might then try to connect like:

token_based_auth = { "auth_type": "SimpleAuthentication", "user": , "key_file": , "fingerprint": , "tenancy": , "region": , "profile": }

connection = oracledb.connect( dsn=mydb_low, extra_auth_params=token_based_auth)

To unregister a user function that was earlier registered, you can useoracledb.unregister_params_hook().

If you have registered user hook methods withoracledb.register_protocol() andoracledb.register_params_hook(), then the method registered withoracledb.register_protocol() is invoked first during connection or pool creation calls. If you call ConnectParams.parse_connect_string(), the registered protocol hook method will be called but the parameter hook will not be.

4.6. LDAP Directory Naming

Directory Naming centralizes the network names and addresses used for connections in a single place. More details can be found in Configuring Oracle Database Clients for OID and OUD Directory Namingand Configuring Oracle Database Clients for Microsoft Active Directory Naming.

The DSN for LDAP connections can be an alias, as shown in the above references. Alternatively it can be an LDAP URL. The URL syntax removes the need for external LDAP and sqlnet.ora configuration files. See the technical briefOracle Client 23ai LDAP URL Syntax.

Python-oracledb Thick Mode LDAP Aliases

Once a directory server is configured, and necessary configuration files have been created as explained in the above references, python-oracledb Thick mode applications can use the LDAP alias as the python-oracledb connection DSN:

connection = oracledb.connect(user="scott", password=pw, dsn="myLdapAlias")

Python-oracledb Thick Mode LDAP URLs

Python-oracledb Thick mode applications using Oracle Client 23.4, or later, can connect with an LDAP URL. For example:

ldapurl = "ldaps://ldapserver.example.com/cn=orcl,cn=OracleContext,dc=example,dc=com" connection = oracledb.connect(user="scott", password=pw, dsn=ldapurl)

To use an LDAP URL in python-oracledb Thick mode whendefaults.thick_mode_dsn_passthrough is False, a connection hook function is required as shown below for Thin mode. This lets LDAP URLs be utilized when python-oracledb uses any supported Oracle Client library version.

Python-oracledb Thin Mode LDAP URLs

To use LDAP in python-oracledb Thin mode, calloracledb.register_protocol() to register your own user connection protocol hook function that gets the database connection string from your LDAP server. Your application can then specify an LDAP URL as the DSN in connection and pool creation calls.

For example:

import ldap3 import re

Get the Oracle Database connection string from an LDAP server when

connection calls use an LDAP URL.

In this example, "protocol"' will have the value "ldap", and "arg" will

be "ldapserver/dbname,cn=OracleContext,dc=dom,dc=com"

def ldap_hook(protocol, arg, params): pattern = r"^(.+)/(.+),(cn=OracleContext.*)$" match = re.match(pattern, arg) ldap_server, db, ora_context = match.groups()

server = ldap3.Server(ldap_server)
conn = ldap3.Connection(server)
conn.bind()
conn.search(ora_context, f"(cn={db})", attributes=['orclNetDescString'])
connect_string = conn.entries[0].orclNetDescString.value
params.parse_connect_string(connect_string)

oracledb.register_protocol("ldap", ldap_hook)

connection = oracledb.connect(user="hr" password=userpwd, dsn="ldap://ldapserver/dbname,cn=OracleContext,dc=dom,dc=com")

You can modify or extend this as needed, for example to use an LDAP module that satisfies your business and security requirements, to handled LDAPS, or to cache the response from the LDAP server.

4.7. Connection Metadata and Application Contexts

During connection you can set additional metadata properties that can be accessed in the database for tracing and for enforcing fine-grained data access, for example with Oracle Virtual Private Database policies. Values may appear in logs and audit trails.

End-to-End Tracing Attributes

The connection attributes Connection.client_identifier,Connection.clientinfo, Connection.dbop,Connection.module, and Connection.action set metadata about the connection.

It is recommended to always set at least client_identifier,module, and action for all applications because their availability in the database can greatly aid future troubleshooting.

See Oracle Database End-to-End Tracing for more information.

Application Contexts

An application context stores user identification that can enable or prevent a user from accessing data in the database. See the Oracle Database documentation About Application Contexts.

A context has a namespace and a key-value pair. The namespace CLIENTCONTEXT is reserved for use with client session-based application contexts. Contexts are set during connection as an array of 3-tuples containing string values for the namespace, key, and value. For example:

myctx = [ ("clientcontext", "loc_id", "1900") ]

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

Context values set during connection can be directly queried in your applications. For example:

with connection.cursor() as cursor: sql = """select * from locations where location_id = sys_context('clientcontext', 'loc_id')""" for r in cursor.execute(sql): print(r)

This will print:

(1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA')

Multiple context values can be set when connecting. For example:

myctx = [ ("clientcontext", "loc_id", "1900"), ("clientcontext", "my_world", "earth"), ]

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

with connection.cursor() as cursor: sql = """select sys_context('clientcontext', 'loc_id'), sys_context('clientcontext', 'my_world') from dual""" for r in cursor.execute(sql): print(r)

will display:

You can use contexts to set up restrictive policies that are automatically applied to any query executed. See Oracle Database documentation Oracle Virtual Private Database (VPD).

4.8. Connection Pooling

Connection pooling can significantly improve application performance and scalability, allows resource sharing, and lets applications use advanced Oracle High Availability features.

The pooling solutions available to python-oracledb applications are:

• Driver Connection Pools: These are managed by the driver layer. They provide readily available database connections that can be shared by multiple users and are quick for applications to obtain. They help make applications scalable and highly available. They are created withoracledb.create_pool() or oracledb.create_pool_async().
  The main use case is for applications that hold connections for relatively short durations while doing database work, and that acquire and release connections back to the pool as needed to do those database operations. Using a driver pool is recommended for applications that need to support multiple users. High availability benefits also make driver pools useful for single-user applications that do infrequent database operations.
• Database Resident Connection Pooling (DRCP): This is pooling of server processes on the database host so they can be shared between application connections. This reduces the number of server processes that the database host needs to manage.
  DRCP is useful if there are large number of application connections, typically from having multiple application processes, and those applications do frequent connection acquire and release calls as needed to do database operations. It is recommended to use DRCP in conjunction with a driver connection pool, since this reduces the number of re-authentications and session memory re-allocations.
• Proxy Resident Connection Pooling (PRCP): This is connection pooling handled by a dedicated mid-tier connection proxy, CMAN-TDM.
  This is useful for applications taking advantage of CMAN-TDM.
• Implicit Connection Pooling: This can add pooling benefits to applications that connect when they start, and only close the connection when the application terminates — but relatively infrequently do database work. It makes use of DRCP or PRCP, but instead of relying on the application to explicitly acquire and release connections, Implicit Connection Pooling automatically detects when applications are not performing database work. It then allows the associated database server process to be used by another connection that needs to do a database operation.
  Implicit Connection Pooling is useful for legacy applications or third-party code that cannot be updated to use a driver connection pool.

Python-oracledb driver connection pools are the first choice for performance, scalability, and high availability. If your database is under memory pressure from having too many applications opening too many connections, then consider either DRCP or Implicit Connection Pooling, depending on your application’s connection life-cycle. If you are utilizing CMAN-TDM, then using PRCP can be considered.

4.8.1. Driver Connection Pooling

Python-oracledb’s driver connection pooling lets applications create and maintain a pool of open connections to the database. Connection pooling is available in both Thin and Thick modes. Connection pooling is important for performance and scalability when applications need to handle a large number of users who do database work for short periods of time but have relatively long periods when the connections are not needed. The high availability features of pools also make small pools useful for applications that want a few connections available for infrequent use and requires them to be immediately usable when acquired. Applications that would benefit from connection pooling but are too difficult to modify from the use ofstandalone connections can take advantage ofImplicit Connection Pooling.

In python-oracledb Thick mode, the pool implementation uses Oracle’s session pool technology which supports additional Oracle Database features, for example some advanced high availability features.

Note

Python-oracledb driver connection pools must be created, used, and closed within the same process. Sharing pools or connections across processes has unpredictable behavior.

Using connection pools in multi-threaded architectures is supported.

Multi-process architectures that cannot be converted to threading may get some benefit from Database Resident Connection Pooling (DRCP).

4.8.1.1. Creating a Connection Pool

A driver connection pool is created by calling oracledb.create_pool(). Various pool options can be specified as described increate_pool() and detailed below.

For example, to create a pool that initially contains one connection but can grow up to five connections:

pool = oracledb.create_pool(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb", min=1, max=5, increment=1)

4.8.1.2. Getting Connections from a Pool

After a pool has been created, your application can get a connection from it by calling ConnectionPool.acquire():

connection = pool.acquire()

These connections can be used in the same way that Standalone Connectionsare used.

By default, acquire() calls wait for a connection to be available before returning to the application. A connection will be available if the pool currently has idle connections, when another user returns a connection to the pool, or after the pool grows. Waiting allows applications to be resilient to temporary spikes in connection load. Users may have to wait a brief time to get a connection but will not experience connection failures.

You can change the behavior of acquire() by setting thegetmode option during pool creation. For example, the option can be set so that if all the connections are currently in use by the application, any additional acquire() call will return an error immediately.

pool = oracledb.create_pool(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb", min=2, max=5, increment=1, getmode=oracledb.POOL_GETMODE_NOWAIT)

Note that when using this option value in python-oracledb Thick mode with Oracle Client libraries 12.2 or earlier, the acquire()call will still wait if the pool can grow. However, you will get an error immediately if the pool is at its maximum size. With newer Oracle Client libraries and with Thin mode, an error will be returned if the pool has to, or cannot, grow.

4.8.1.3. Returning Connections to a Pool

When your application has finished performing all required database operations, the pooled connection should be released back to the pool to make it available for other users. For example, you can use a Python context manager with block which lets pooled connections be closed implicitly at the end of scope and cleans up dependent resources:

with pool.acquire() as connection: with connection.cursor() as cursor: for result in cursor.execute("select * from mytab"): print(result)

Alternatively, you can explicitly return connections withConnectionPool.release() or Connection.close(), however you may also need to close other resources first.

If you need to force a connection to be closed and its associated database server process to be released, use ConnectionPool.drop():

with pool.acquire() as connection:

. . .

pool.drop(connection)

4.8.1.4. Closing a Connection Pool

At application shutdown, the connection pool can be completely closed usingConnectionPool.close():

To force immediate pool termination when connections are still in use, execute:

See connection_pool.py for a runnable example of connection pooling.

4.8.2. Using the Connection Pool Cache

When your application architecture makes it difficult to pass aConnectionPool object between your code layers, you can use the python-oracledb connection pool cache. This lets you store and retrieve pools by name.

Adding a pool to the python-oracledb connection pool cache

To use the python-oracledb pool cache, specify the pool_alias parameter when you create a pool during application initialization. Its value should be a user-chosen string. For example:

import oracledb

NAME = "my_pool"

oracledb.create_pool( user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb", pool_alias=NAME )

This creates a pool and stores it in the cache under the name “my_pool”. The application does not need to store or manage the reference to the pool so thecreate_pool() return value is not saved.

If a pool already exists with the name “my_pool”, the following error will be raised:

DPY-2055: connection pool with name "my_pool" already exists

Getting a connection from a cached pool

Applications can get a connection from a cached pool by passing its name directly to oracledb.connect():

import oracledb

NAME = "my_pool"

connection = oracledb.connect(pool_alias=NAME)

This is equivalent to calling ConnectionPool.acquire(). You can pass additional parameters to connect() that are allowed foracquire(). For example, with a heterogeneous pool you can pass the username and password:

import oracledb

NAME = "my_pool"

connection = oracledb.connect(pool_alias=NAME, user="toto", password=pw)

If there is no pool named my_pool in the cache, you will get the following error:

DPY-2054: connection pool with name "my_pool" does not exist

You cannot pass pool_alias and the deprecated pool parameter together to oracledb.connect() or oracledb.connect_async(). If you do, the following error is raised:

DPY-2014: "pool_alias" and "pool" cannot be specified together

Getting a pool from the connection pool cache

You can use oracledb.get_pool() to retrieve a pool and then access it directly:

import oracledb

NAME = "my_pool"

pool = oracledb.get_pool(NAME) connection = pool.acquire()

This allows any connection pool method orattribute from a cached pool to be used, as normal.

If there is no pool named my_pool in the cache, thenget_pool() will return None.

Removing a pool from the cache

A pool is automatically removed from the cache when the pool is closed:

import oracledb

NAME = "my_pool"

pool = oracledb.get_pool(NAME) pool.close()

4.8.3. Connection Pool Sizing

The Oracle Real-World Performance Group’s recommendation is to use fixed size connection pools. The values of min and max should be the same. When using older versions of Oracle Client libraries the increment parameter will need to be zero (which is internally treated as a value of one), but otherwise you may prefer a larger size since this will affect how the connection pool is re-established after, for example, a network dropout invalidates all connections.

Fixed size pools avoid connection storms on the database which can decrease throughput. See Guideline for Preventing Connection Storms: Use Static Pools, which contains more details about sizing of pools. Having a fixed size will also guarantee that the database can handle the upper pool size. For example, if a dynamically sized pool needs to grow but the database resources are limited, then ConnectionPool.acquire() may return errors such as ORA-28547. With a fixed pool size, this class of error will occur when the pool is created, allowing you to change the pool size or reconfigure the database before users access the application. With a dynamically growing pool, the error may occur much later while the application is in use.

The Real-World Performance Group also recommends keeping pool sizes small because they often can perform better than larger pools. The pool attributes should be adjusted to handle the desired workload within the bounds of available resources in python-oracledb and the database.

4.8.3.1. Connection Pool Growth

At pool creation, min connections are established to the database. When a pool needs to grow, new connections are created automatically limited by themax size. The pool max size restricts the number of application users that can do work in parallel on the database.

The number of connections opened by a pool can shown with the attribute.ConnectionPool.opened. The number of connections the application has obtained with acquire() can be shown withConnectionPool.busy. The difference in values is the number of connections unused or ‘idle’ in the pool. These idle connections may be candidates for the pool to close, depending on the pool configuration.

Pool growth is normally initiated when acquire() is called and there are no idle connections in the pool that can be returned to the application. The number of new connections created internally will be the value of the create_pool() parameter increment.

Depending on whether Thin or Thick mode is used and on the pool creationgetmode value that is set, any acquire() call that initiates pool growth may wait until all increment new connections are internally opened. However, in this case the cost is amortized because lateracquire() calls may not have to wait and can immediately return an available connection. Some users set largerincrement values even for fixed-size pools because it can help a pool re-establish itself if all connections become invalid, for example after a network dropout. In the common case of Thin mode with the default getmodeof POOL_GETMODE_WAIT, any acquire() call that initiates pool growth will return after the first new connection is created, regardless of how big increment is. The pool will then continue to re-establish connections in a background thread.

A connection pool can shrink back to its minimum size min when connections opened by the pool are not used by the application. This frees up database resources while allowing pools to retain open connections for active users. If there are more than min connections open, and connections are idle in the pool (i.e. not currently acquired by the application) and unused for longer than the pool creation attribute timeout value, then they will be closed. The check occurs every timeout interval and hence in the worst case it may take twice the timeout time to close the idle connections. The defaulttimeout is 0 seconds signifying an infinite time and meaning idle connections will never be closed.

The pool creation parameter max_lifetime_session also allows pools to shrink. This parameter bounds the total length of time that a connection can exist starting from the time that it was created in the pool. It is mostly used for defensive programming to mitigate against unforeseeable problems that may occur with connections. If a connection was created max_lifetime_session or longer seconds ago, then it will be a candidate for being closed. In the case when timeout and max_lifetime_session are both set, the connection will be terminated if either the idle timeout happens or the maximum lifetime setting is exceeded. Note that when using python-oracledb in Thick mode with Oracle Client libraries prior to 21c, pool shrinkage is only initiated when the pool is accessed so pools in fully dormant applications will not shrink until the application is next used. In Thick mode, Oracle Client libraries 12.1, or later, are needed to use max_lifetime_session.

For pools created with external authentication, withhomogeneous set to False, or when using Database Resident Connection Pooling (DRCP) (in python-oracledb Thick mode), then the number of connections opened at pool creation is zero even if a larger value is specified for min. Also, in these cases the pool increment unit is always 1 regardless of the value ofincrement.

4.8.4. Pool Connection Health

Before ConnectionPool.acquire() returns, python-oracledb does a lightweight check similar to Connection.is_healthy() to see if the network transport for the selected connection is still open. If it is not, then acquire() will clean up the connection and return a different one.

This check will not detect cases such as where the database session has been terminated by the DBA, or reached a database resource manager quota limit. To help in those cases, acquire() will also do a fullround-trip database ping similar toConnection.ping() when it is about to return a connection that was idle in the pool (i.e. not acquired by the application) forConnectionPool.ping_interval seconds. If the ping fails, the connection will be discarded and another one obtained beforeacquire() returns to the application. Theping_timeout parameter to oracledb.create_pool() limits the amount of time that any internal ping is allowed to take. If it is exceeded, perhaps due to a network hang, the connection is considered unusable and a different connection is returned to the application.

Because this full ping is time based and may not occur for eachacquire(), the application may still get an unusable connection. Also, network timeouts and session termination may occur between the calls to acquire() and Cursor.execute(). To handle these cases, applications need to check for errors after eachexecute() and make application-specific decisions about retrying work if there was a connection failure. When using python-oracledb in Thick mode, Oracle Database features like Application Continuity can do this automatically in some cases.

You can explicitly initiate a full round-trip ping at any time withConnection.ping() to check connection liveness but the overuse will impact performance and scalability. To avoid pings hanging due to network errors, use Connection.call_timeout to limit the amount of timeping() is allowed to take.

The Connection.is_healthy() method is an alternative toConnection.ping(). It has lower overheads and may suit some uses, but it does not perform a full connection check.

If the getmode parameter in oracledb.create_pool() is set tooracledb.POOL_GETMODE_TIMEDWAIT, then the maximum amount of time anacquire() call will wait to get a connection from the pool is limited by the value of the ConnectionPool.wait_timeoutparameter. A call that cannot be immediately satisfied will wait no longer than wait_timeout regardless of the value of ping_timeout.

Connection pool health can be impacted by firewalls,resource managers or user profile IDLE_TIME values. For best efficiency, ensure these do not expire idle sessions since this will require connections to be recreated which will impact performance and scalability.

A pool’s internal connection re-establishment after lightweight and full pings can mask performance-impacting configuration issues such as firewalls terminating connections. You should monitor AWRreports for an unexpectedly large connection rate.

4.8.5. Connection Pool Reconfiguration

Some pool settings can be changed dynamically withConnectionPool.reconfigure(). This allows the pool size and other attributes to be changed during application runtime without needing to restart the pool or application.

For example a pool’s size can be changed like:

pool.reconfigure(min=10, max=10, increment=0)

After any size change has been processed, reconfiguration on the other parameters is done sequentially. If an error such as an invalid value occurs when changing one attribute, then an exception will be generated but any already changed attributes will retain their new values.

During reconfiguration of a pool’s size, the behavior ofConnectionPool.acquire() depends on the pool creation getmode value in effect when acquire() is called, seeConnectionPool.reconfigure(). Closing connections or closing the pool will wait until after pool reconfiguration is complete.

Calling reconfigure() is the only way to change a pool’s min, maxand increment values. Other attributes such aswait_timeout can be passed to reconfigure() or they can be set directly, for example:

4.8.6. Session Callbacks for Setting Pooled Connection State

Applications can set “session” state in each connection. Examples of session state are NLS globalization settings from ALTER SESSION statements. Pooled connections will retain their session state after they have been released back to the pool. However, because pools can grow or connections in the pool can be recreated, there is no guarantee a subsequentacquire() call will return a database connection that has any particular state.

The create_pool() parameter session_callback enables efficient setting of session state so that connections have a known session state, without requiring that state to be explicitly set after everyacquire() call. The callback is internally invoked when acquire() is called and runs first.

The session callback can be a Python function or a PL/SQL procedure.

Connections can also be tagged when they are released back to the pool. The tag is a user-defined string that represents the session state of the connection. When acquiring connections, a particular tag can be requested. If a connection with that tag is available, it will be returned. If not, then another session will be returned. By comparing the actual and requested tags, applications can determine what exact state a session has, and make any necessary changes.

Connection tagging and PL/SQL callbacks are only available in python-oracledb Thick mode. Python callbacks can be used in python-oracledb Thin and Thick modes.

There are three common scenarios for session_callback:

• When all connections in the pool should have the same state, use a Python callback without tagging.
• When connections in the pool require different state for different users, use a Python callback with tagging.
• With Database Resident Connection Pooling (DRCP), use a PL/SQL callback with tagging.

4.8.6.1. Python Callback

If the session_callback parameter is a Python procedure, it will be called whenever acquire() will return a newly created database connection that has not been used before. It is also called when connection tagging is being used and the requested tag is not identical to the tag in the connection returned by the pool.

An example is:

Set the NLS_DATE_FORMAT for a session

def init_session(connection, requested_tag): with connection.cursor() as cursor: cursor.execute("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI'")

Create the pool with session callback defined

pool = oracledb.create_pool(user="hr", password=userpwd, dsn="localhost/orclpdb", session_callback=init_session)

Acquire a connection from the pool (will always have the new date format)

connection = pool.acquire()

If needed, the init_session() procedure is called internally beforeacquire() returns. It will not be called when previously used connections are returned from the pool. This means that the ALTER SESSION does not need to be executed after everyacquire() call. This improves performance and scalability.

In this example tagging was not being used, so the requested_tag parameter is ignored.

Note that if you need to execute multiple SQL statements in the callback, use an anonymous PL/SQL block to save round-trips of repeatedexecute() calls. With ALTER SESSION, pass multiple settings in the one statement:

cursor.execute(""" begin execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN'; -- other SQL statements could be put here end;""")

4.8.6.2. Connection Tagging

Connection tagging is used when connections in a pool should have differing session states. In order to retrieve a connection with a desired state, thetag attribute in acquire() needs to be set.

When python-oracledb is using Oracle Client libraries 12.2 or later, then python-oracledb uses ‘multi-property tags’ and the tag string must be of the form of one or more “name=value” pairs separated by a semi-colon, for example"loc=uk;lang=cy".

When a connection is requested with a given tag, and a connection with that tag is not present in the pool, then a new connection, or an existing connection with cleaned session state, will be chosen by the pool and the session callback procedure will be invoked. The callback can then set desired session state and update the connection’s tag. However, if the matchanytag parameter ofacquire() is True, then any other tagged connection may be chosen by the pool and the callback procedure should parse the actual and requested tags to determine which bits of session state should be reset.

The example below demonstrates connection tagging:

def init_session(connection, requested_tag): if requested_tag == "NLS_DATE_FORMAT=SIMPLE": sql = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'" elif requested_tag == "NLS_DATE_FORMAT=FULL": sql = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI'" cursor = connection.cursor() cursor.execute(sql) connection.tag = requested_tag

pool = oracledb.create_pool(user="hr", password=userpwd, dsn="orclpdb", session_callback=init_session)

Two connections with different session state:

connection1 = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE") connection2 = pool.acquire(tag="NLS_DATE_FORMAT=FULL")

See session_callback.py for an example.

4.8.6.3. PL/SQL Callback

When python-oracledb uses Oracle Client 12.2 or later, the session callback can also be the name of a PL/SQL procedure. A PL/SQL callback will be initiated only when the tag currently associated with a connection does not match the tag that is requested. A PL/SQL callback is most useful when using Database Resident Connection Pooling (DRCP)because DRCP does not require a round-trip to invoke a PL/SQL session callback procedure.

The PL/SQL session callback should accept two VARCHAR2 arguments:

PROCEDURE myPlsqlCallback ( requestedTag IN VARCHAR2, actualTag IN VARCHAR2 );

The logic in this procedure can parse the actual tag in the session that has been selected by the pool and compare it with the tag requested by the application. The procedure can then change any state required before the connection is returned to the application fromacquire().

If the matchanytag attribute of acquire() is_True_, then a connection with any state may be chosen by the pool.

Oracle ‘multi-property tags’ must be used. The tag string must be of the form of one or more “name=value” pairs separated by a semi-colon, for example"loc=uk;lang=cy".

In python-oracledb set session_callback to the name of the PL/SQL procedure. For example:

pool = oracledb.create_pool(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb:pooled", session_callback="MyPlsqlCallback")

connection = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE", # DRCP options, if you are using DRCP cclass='MYCLASS', purity=oracledb.PURITY_SELF)

See session_callback_plsql.py for an example.

4.8.7. Heterogeneous and Homogeneous Connection Pools

Homogeneous Pools

By default, connection pools are ‘homogeneous’, meaning that all connections use the same database credentials. Both python-oracledb Thin and Thick modes support homogeneous pools.

Heterogeneous Pools

The python-oracledb Thick mode additionally supports Heterogeneous pools, allowing different user names and passwords to be passed to eachacquire() call.

To create an heterogeneous pool, set the create_pool()parameter homogeneous to False:

pool = oracledb.create_pool(dsn="dbhost.example.com/orclpdb", homogeneous=False) connection = pool.acquire(user="hr", password=userpwd)

4.8.8. Using the PoolParams Builder Class

The PoolParams class allows you to define connection and pool parameters in a single place. The oracledb.PoolParams() function returns a PoolParams object. This is a subclass of theConnectParams class (see Using the ConnectParams Builder Class) with additional pool-specific attributes such as the maximum pool size. APoolParams object can be passed to oracledb.create_pool(). For example:

pp = oracledb.PoolParams(min=1, max=2, increment=1) pool = oracledb.create_pool(user="hr", password=userpw, dsn="dbhost.example.com/orclpdb", params=pp)

The use of the PoolParams class is optional because you can pass the same parameters directly to create_pool(). For example, the code above is equivalent to:

pool = oracledb.create_pool(user="hr", password=userpw, dsn="dbhost.example.com/orclpdb", min=1, max=2, increment=1)

Some values such as the database host name can be specified asoracledb.create_pool() parameters, as part of the dsn connection string, and in the params object. A final connection string is internally constructed from any dsn, individual parameters, and params object values. The precedence is that values in a dsn parameter override values passed as individual parameters, which themselves override values set in theparams object.

Most PoolParams arguments are gettable as properties. They may be set individually using the set() method:

pp = oracledb.PoolParams() pp.set(min=5) print(pp.min) # 5

4.9. Database Resident Connection Pooling (DRCP)

Database Resident Connection Pooling (DRCP) enables database resource sharing for applications which use a large number of connections that run in multiple client processes or run on multiple middle-tier application servers. By default, each connection from Python will use one database server process. DRCP allows pooling of these server processes. This reduces the amount of memory required on the database host. A DRCP pool can be shared by multiple applications.

DRCP is useful for applications which share the same database credentials, have similar session settings (for example date format settings or PL/SQL package state), and where the application gets a database connection, works on it for a relatively short duration, and then releases it.

For efficiency, it is recommended that DRCP connections should be used in conjunction with python-oracledb’s local connection pool. Using DRCP with standalone connections is not as efficient but does allow the database to reuse database server processes which can provide a performance benefit for applications that cannot use a local connection pool. In this scenario, make sure to configure enough DRCP authentication servers to handle the connection load.

Although applications can choose whether or not to use DRCP pooled connections at runtime, care must be taken to configure the database appropriately for the number of expected connections, and also to stop inadvertent use of non-DRCP connections leading to a database server resource shortage. Conversely, avoid using DRCP connections for long-running operations.

For more information about DRCP, see the technical brief Extreme Oracle Database Connection Scalability with Database Resident Connection Pooling (DRCP), the user documentation Oracle Database Concepts Guide, and for DRCP Configuration see Oracle Database Administrator’s Guide.

Using DRCP with python-oracledb applications involves the following steps:

1. Configuring and enabling DRCP in the database
2. Configuring the application to use a DRCP connection
3. Deploying the application

4.9.1. Enabling DRCP in Oracle Database

Every Oracle Database uses a single, default DRCP connection pool. From Oracle Database 21c, each pluggable database can optionally have its own pool. Note that DRCP is already enabled in Oracle Autonomous Database and pool management is different to the steps below.

DRCP pools can be configured and administered by a DBA using theDBMS_CONNECTION_POOL package:

EXECUTE DBMS_CONNECTION_POOL.CONFIGURE_POOL( pool_name => 'SYS_DEFAULT_CONNECTION_POOL', minsize => 4, maxsize => 40, incrsize => 2, session_cached_cursors => 20, inactivity_timeout => 300, max_think_time => 600, max_use_session => 500000, max_lifetime_session => 86400)

Alternatively, the method DBMS_CONNECTION_POOL.ALTER_PARAM() can set a single parameter:

EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM( pool_name => 'SYS_DEFAULT_CONNECTION_POOL', param_name => 'MAX_THINK_TIME', param_value => '1200')

The inactivity_timeout setting terminates idle pooled servers, helping optimize database resources. To avoid pooled servers permanently being held onto by a selfish Python script, the max_think_time parameter can be set. The parameters num_cbrok and maxconn_cbrok can be used to distribute the persistent connections from the clients across multiple brokers. This may be needed in cases where the operating system per-process descriptor limit is small. Some customers have found that having several connection brokers improves performance. The max_use_session and max_lifetime_sessionparameters help protect against any unforeseen problems affecting server processes. The default values will be suitable for most users. See theOracle DRCP documentation for details on parameters.

In general, if pool parameters are changed, then the pool should be restarted. Otherwise, server processes will continue to use old settings.

There is a DBMS_CONNECTION_POOL.RESTORE_DEFAULTS() procedure to reset all values.

When DRCP is used with RAC, each database instance has its own connection broker and pool of servers. Each pool has the identical configuration. For example, all pools start with minsize server processes. A single DBMS_CONNECTION_POOL command will alter the pool of each instance at the same time. The pool needs to be started before connection requests begin. The command below does this by bringing up the broker, which registers itself with the database listener:

EXECUTE DBMS_CONNECTION_POOL.START_POOL()

Once enabled this way, the pool automatically restarts when the database instance restarts, unless explicitly stopped with theDBMS_CONNECTION_POOL.STOP_POOL() command:

EXECUTE DBMS_CONNECTION_POOL.STOP_POOL()

The pool cannot be stopped while connections are open.

4.9.2. Coding Applications to use DRCP

To use DRCP, application connection establishment must request a DRCP pooled server. The best practice is also to specify a user-chosen connection class name when creating a connection pool. A ‘purity’ of the connection session state can optionally be specified. See the Oracle Database documentation onbenefiting from scalability for more information on purity and connection classes.

Note that when using DRCP with a python-oracledb local connection pool in Thick mode, the local connection pool min value is ignored and the pool will be created with zero connections.

Requesting a Pooled Server

To request a DRCP pooled server, you can:

• Use a specific connection string in oracledb.create_pool() ororacledb.connect(). For example with theEasy Connect syntax:
  pool = oracledb.create_pool(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb:pooled",
  min=2, max=5, increment=1,
  cclass="MYAPP")
• Alternatively, add (SERVER=POOLED) to the Connect Descriptor such as used in an Oracle Network configuration filetnsnames.ora:
  customerpool = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
  (HOST=dbhost.example.com)
  (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CUSTOMER)
  (SERVER=POOLED)))
• Another way to use a DRCP pooled server is to set the server_typeparameter during standalone connection creation or python-oracledb connection pool creation. For example:
  pool = oracledb.create_pool(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb",
  min=2, max=5, increment=1,
  server_type="pooled",
  cclass="MYAPP")

DRCP Connection Class Names

The best practice is to specify a cclass class name when creating a python-oracledb connection pool. This user-chosen name provides some partitioning of DRCP session memory so reuse is limited to similar applications. It provides maximum pool sharing if multiple application processes are started. A class name also allows better DRCP usage tracking in the database. In the database monitoring views, the class name shown will be the value specified in the application prefixed with the user name.

If cclass was not specified during pool creation, then the python-oracledb Thin mode generates a unique connection class with the prefix “DPY” while the Thick mode generates a unique connection class with the prefix “OCI”.

To create a connection pool requesting a DRCP pooled server and specifying a class name, you can call:

pool = oracledb.create_pool(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb:pooled", min=2, max=5, increment=1, cclass="MYAPP")

Once the pool has been created, your application can get a connection from it by calling:

connection = pool.acquire()

The python-oracledb connection pool size does not need to match the DRCP pool size. The limit on overall execution parallelism is determined by the DRCP pool size.

Connection class names can also be passed to acquire(), if you want to use a connection with a different class:

pool = oracledb.create_pool(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb:pooled", min=2, max=5, increment=1, cclass="MYAPP")

connection = mypool.acquire(cclass="OTHERAPP")

If a pooled server of a requested class is not available, a server with new session state is used. If the DRCP pool cannot grow, a server with a different class may be used and its session state cleared.

If cclass is not set, then the pooled server sessions will not be reused optimally, and the DRCP statistic views may record large values for NUM_MISSES.

DRCP Connection Purity

DRCP allows the connection session memory to be reused or cleaned each time a connection is acquired from the pool. The pool or connection creationpurity parameter can be one of PURITY_NEW, PURITY_SELF, orPURITY_DEFAULT. The value PURITY_SELF allows reuse of both the pooled server process and session memory, giving maximum benefit from DRCP. By default, python-oracledb pooled connections use PURITY_SELF and standalone connections use PURITY_NEW.

To limit session sharing, you can explicitly require that new session memory be allocated each time acquire() is called:

pool = oracledb.create_pool(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb:pooled", min=2, max=5, increment=1, cclass="MYAPP", purity=oracledb.PURITY_NEW)

Setting the Connection Class and Purity in the Connection String

Using python-oracledb Thin mode with Oracle Database 21c, or later, you can specify the class and purity in the connection string itself. This removes the need to modify an existing application when you want to use DRCP:

dsn = "localhost/orclpdb:pooled?pool_connection_class=MYAPP&pool_purity=self"

For python-oracledb Thick mode, this syntax is supported if you are using Oracle Database 21c (or later) and Oracle Client 19c (or later). However, explicitly specifying the purity as SELF in this way may cause some unusable connections in a python-oracledb Thick mode connection pool to not be terminated. In summary, if you cannot programmatically set the class name and purity, or cannot use python-oracledb Thin mode, then avoid explicitly setting the purity as a connection string parameter when using a python-oracledb connection pooling in Thick mode.

Closing Connections when using DRCP

Similar to using a python-oracledb connection pool, Python scripts where python-oracledb connections do not go out of scope quickly (which releases them), or do not currently use Connection.close() orConnectionPool.release() should be examined to see if the connections can be closed earlier. This allows maximum reuse of DRCP pooled servers by other users:

pool = oracledb.create_pool(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb:pooled", min=2, max=5, increment=1, cclass="MYAPP")

Do some database operations

connection = mypool.acquire() . . . connection.close(); # <- Add this to release the DRCP pooled server

Do lots of non-database work

. . .

Do some more database operations

connection = mypool.acquire() # <- And get a new pooled server only when needed . . . connection.close();

See drcp_pool.pyfor a runnable example of DRCP.

4.9.3. Monitoring DRCP

Data dictionary views are available to monitor the performance of DRCP. Database administrators can check statistics such as the number of busy and free servers, and the number of hits and misses in the pool against the total number of requests from clients. The views include:

• DBA_CPOOL_INFO
• V$PROCESS
• V$SESSION
• V$CPOOL_STATS
• V$CPOOL_CC_STATS
• V$CPOOL_CONN_INFO

DBA_CPOOL_INFO View

DBA_CPOOL_INFO displays configuration information about the DRCP pool. The columns are equivalent to the dbms_connection_pool.configure_pool()settings described in the table of DRCP configuration options, with the addition of a STATUS column. The status is ACTIVE if the pool has been started and INACTIVE otherwise. Note that the pool name column is called CONNECTION_POOL. This example checks whether the pool has been started and finds the maximum number of pooled servers:

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

CONNECTION_POOL STATUS MAXSIZE

SYS_DEFAULT_CONNECTION_POOL ACTIVE 40

V$PROCESS and V$SESSION Views

The V$SESSION view shows information about the currently active DRCP sessions. It can also be joined with V$PROCESS throughV$SESSION.PADDR = V$PROCESS.ADDR to correlate the views.

V$CPOOL_STATS View

The V$CPOOL_STATS view displays information about the DRCP statistics for an instance. The V$CPOOL_STATS view can be used to assess how efficient the pool settings are. This example query shows an application using the pool effectively. The low number of misses indicates that servers and sessions were reused. The wait count shows just over 1% of requests had to wait for a pooled server to become available:

NUM_REQUESTS NUM_HITS NUM_MISSES NUM_WAITS

   10031      99990         40       1055

If cclass was set (allowing pooled servers and sessions to be reused), then NUM_MISSES will be low. If the pool maxsize is too small for the connection load, then NUM_WAITS will be high.

V$CPOOL_CC_STATS View

The view V$CPOOL_CC_STATS displays information about the connection class level statistics for the pool per instance:

SQL> select cclass_name, num_requests, num_hits, num_misses from v$cpool_cc_stats;

CCLASS_NAME NUM_REQUESTS NUM_HITS NUM_MISSES

HR.MYCLASS 100031 99993 38

The class name columns shows the database user name appended with the connection class name.

V$CPOOL_CONN_INFO View

The V$POOL_CONN_INFO view gives insight into client processes that are connected to the connection broker, making it easier to monitor and trace applications that are currently using pooled servers or are idle. This view was introduced in Oracle 11gR2.

You can monitor the view V$CPOOL_CONN_INFO to, for example, identify misconfigured machines that do not have the connection class set correctly. This view maps the machine name to the class name. In python-oracledb Thick mode, the class name will be default to one like shown below:

SQL> select cclass_name, machine from v$cpool_conn_info;

CCLASS_NAME MACHINE

CJ.OCI:SP:wshbIFDtb7rgQwMyuYvodA cjlinux

In this example, you would examine applications on cjlinux and make them set cclass.

When connecting to Oracle Autonomous Database on Shared Infrastructure (ADB-S), the V$CPOOL_CONN_INFO view can be used to track the number of connection hits and misses to show the pool efficiency.

4.10. Implicit Connection Pooling

Implicit connection pooling is useful for applications that cause excess database server load due to the number of standalone connectionsopened. When these applications cannot be rewritten to usepython-oracledb connection pooling, then implicit connection pooling may be an option to reduce the load on the database system.

Implicit connection pooling allows application connections to share pooled servers in DRCP or Oracle Connection Manager in Traffic Director Mode’s (CMAN-TDM) Proxy Resident Connection Pooling (PRCP). Applications do not need to be modified. The feature is enabled by adding a pool_boundary parameter to the application’sconnection string. Applications do not need to explicitly acquire, or release, connections to be able use a DRCP or PRCP pool.

Implicit connection pooling is available in python-oracledb Thin andThick modes. It requires Oracle Database 23ai. Python-oracledb Thick mode additionally requires Oracle Client 23ai libraries.

With implicit connection pooling, connections are internally acquired from the DRCP or PRCP pool when they are actually used by the application to do database work. They are internally released back to pool when not in use. This may occur between the application’s explicit oracledb.connect() call andConnection.close() (or the application’s equivalent connection release at end-of-scope). The internal connection release can be controlled by the value of the pool_boundary connection string parameter, which can be either:

• statement: If this boundary is specified, then the connection is released back to the DRCP or PRCP connection pool when the connection is implicitly stateless. A connection is implicitly stateless when there are no active cursors in the connection (that is, all the rows of the cursors have been internally fetched), no active transactions, no temporary tables, and no temporary LOBs.
• transaction: If this boundary is specified, then the connection is released back to the DRCP or PRCP connection pool when either one of the methodsConnection.commit() or Connection.rollback() are called. It is recommended to not set the Connection.autocommitattribute to true when using implicit connection pooling. If you do set this attribute, then you will be unable to:
  • Fetch any data that requires multiple round-trips to the database
  • Run queries that fetch LOB and JSONdata

Inline with DRCP and PRCP best practices regarding session sharing across differing applications, you should add a connection stringpool_connection_class parameter, using the same value for all applications that are alike.

The DRCP and PRCP “purity” used by Implicit Connection Pooling defaults to SELF, which allows reuse of the server process session memory for best performance. Adding the connection string parameter pool_purity=new will change this and cause each use of a connection to recreate the session memory.

Configuring Implicit Connection Pooling

To use implicit connection pooling in python-oracledb with DRCP:

1. Enable DRCP in the database. For example in SQL*Plus:
   SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL()
2. Specify to use a pooled server in:
   • The dsn parameter of oracledb.connect() ororacledb.create_pool(). For example with theEasy Connect syntax:
     cs = "dbhost.example.com/orclpdb:pooled"
     pool = oracledb.create_pool(user="hr", password=userpwd,
     dsn=cs,
     min=2, max=5, increment=1,
     cclass="MYAPP")
   • Or in the Connect Descriptor used in an Oracle Network configuration file such as tnsnames.ora by adding (SERVER=POOLED). For example:
     customerpool = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
     (HOST=dbhost.example.com)
     (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CUSTOMER)
     (SERVER=POOLED)))
   • Or in the server_type parameter duringstandalone connection creationor connection pool creation. For example:
     pool = oracledb.create_pool(user="hr", password=userpwd,
     host="dbhost.example.com", service_name="orclpdb",
     min=2, max=5, increment=1, server_type="pooled",
     cclass="MYAPP")
3. Set the pool boundary to either statement or transaction in:
   • The Easy Connect string. For example, to use the_statement_ boundary:
     dsn = "localhost:1521/orclpdb:pooled?pool_boundary=statement"
   • Or the CONNECT_DATA section of the Connect Descriptor. For example, to use the transaction boundary:
     tnsalias = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mymachine.example.com)
     (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)
     (SERVER=POOLED)(POOL_BOUNDARY=TRANSACTION)))
   • Or the pool_boundary parameter in oracledb.connect() ororacledb.create_pool()
     Note
     Implicit connection pooling is not enabled if the application sets thepool_boundary attribute to transaction or statement but does not specify to use a pooled server.
4. Set the connection class in:

   • The Easy Connect string. For example, to use a class name ‘myapp’:
     dsn = "localhost:1521/orclpdb:pooled?pool_boundary=statement&pool_connection_class=myapp"
   • Or the CONNECT_DATA section of the Connect Descriptor. For example, to use a class name ‘myapp’:
     tnsalias = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mymachine.example.com)
     (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)

          (SERVER=POOLED)(POOL_BOUNDARY=TRANSACTION)  
          (POOL_CONNECTION_CLASS=myapp)))  

Use the same connection class name for application processes of the same type where you want session memory to be reused for connections.
The pool purity can also optionally be changed by adding POOL_PURITY=NEWto the Easy Connect string or Connect Descriptor.

Similar steps can be used with PRCP. For general information on PRCP, see the technical brief CMAN-TDM — An Oracle Database connection proxy for scalable and highly available applications.

Implicit Pooling Notes

You should thoroughly test your application when using implicit connection pooling to ensure that the internal reuse of database servers does not cause any problems. For example, any session state such as the connection session id and serial number will vary throughout the lifetime of the application connection because different servers may be used at different times. Another example is when using a statement boundary of transaction. In this scenario, any commit can invalidate open cursors.

It is recommended to use python-oracledb’s local Connection Pooling where possible instead of implicit connection pooling. This gives multi-user applications more control over pooled server reuse.

4.11. Connecting Using Proxy Authentication

Proxy authentication allows a user (the “session user”) to connect to Oracle Database using the credentials of a “proxy user”. Statements will run as the session user. Proxy authentication is generally used in three-tier applications where one user owns the schema while multiple end-users access the data. For more information about proxy authentication, see the Oracle documentation.

An alternative to using proxy users is to setConnection.client_identifier after connecting and use its value in statements and in the database, for example for monitoring.

The following proxy examples use these schemas. The mysessionuser schema is granted access to use the password of myproxyuser:

CREATE USER myproxyuser IDENTIFIED BY myproxyuserpw; GRANT CREATE SESSION TO myproxyuser;

CREATE USER mysessionuser IDENTIFIED BY itdoesntmatter; GRANT CREATE SESSION TO mysessionuser;

ALTER USER mysessionuser GRANT CONNECT THROUGH myproxyuser;

After connecting to the database, the following query can be used to show the session and proxy users:

SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

Standalone connection examples:

Basic Authentication without a proxy

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

PROXY_USER: None

SESSION_USER: MYPROXYUSER

Basic Authentication with a proxy

connection = oracledb.connect(user="myproxyuser[mysessionuser]", password="myproxyuserpw", dsn="dbhost.example.com/orclpdb")

PROXY_USER: MYPROXYUSER

SESSION_USER: MYSESSIONUSER

Pooled connection examples:

Basic Authentication without a proxy

pool = oracledb.create_pool(user="myproxyuser", password="myproxyuserpw", dsn="dbhost.example.com/orclpdb") connection = pool.acquire()

PROXY_USER: None

SESSION_USER: MYPROXYUSER

Basic Authentication with proxy

pool = oracledb.create_pool(user="myproxyuser[mysessionuser]", password="myproxyuserpw", dsn="dbhost.example.com/orclpdb", homogeneous=False)

connection = pool.acquire()

PROXY_USER: MYPROXYUSER

SESSION_USER: MYSESSIONUSER

Note the use of a heterogeneous pool in the example above. This is required in this scenario.

4.12. Connecting Using External Authentication

Instead of storing the database username and password in Python scripts or environment variables, database access can be authenticated by an outside system. External Authentication allows applications to validate user access with an external password store (such as anOracle Wallet), with theoperating system, or with an external authentication service.

4.12.1. Using an Oracle Wallet for External Authentication

The following steps give an overview of using an Oracle Wallet. Wallets should be kept securely. Wallets can be managed with Oracle Wallet Manager.

In this example the wallet is created for the myuser schema in the directory/home/oracle/wallet_dir. The mkstore command is available from a full Oracle client or Oracle Database installation. If you have been given wallet by your DBA, skip to step 3.

1. First create a new wallet as the oracle user:
   mkstore -wrl "/home/oracle/wallet_dir" -create
   This will prompt for a new password for the wallet.
2. Create the entry for the database user name and password that are currently hardcoded in your Python scripts. Use either of the methods shown below. They will prompt for the wallet password that was set in the first step.
   Method 1 - Using an Easy Connect string:
   mkstore -wrl "/home/oracle/wallet_dir" -createCredential dbhost.example.com/orclpdb myuser myuserpw
   Method 2 - Using a connect name identifier:
   mkstore -wrl "/home/oracle/wallet_dir" -createCredential mynetalias myuser myuserpw
   The alias key mynetalias immediately following the-createCredential option will be the connect name to be used in Python scripts. If your application connects with multiple different database users, you could create a wallet entry with different connect names for each.
   You can see the newly created credential with:
   mkstore -wrl "/home/oracle/wallet_dir" -listCredential
3. Skip this step if the wallet was created using an Easy Connect String. Otherwise, add an entry in tnsnames.ora for the connect name as follows:
   mynetalias =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.com)(PORT = 1521))
   (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = orclpdb)
   )
   )
   The file uses the description for your existing database and sets the connect name alias to mynetalias, which is the identifier used when adding the wallet entry.
4. Add the following wallet location entry in the sqlnet.ora file, using the DIRECTORY you created the wallet in:
   WALLET_LOCATION =
   (SOURCE =
   (METHOD = FILE)
   (METHOD_DATA =
   (DIRECTORY = /home/oracle/wallet_dir)
   )
   )
   SQLNET.WALLET_OVERRIDE = TRUE
   Examine the Oracle documentation for full settings and values.
5. Ensure the configuration files are in a default location or TNS_ADMIN is set to the directory containing them. See Optional Oracle Net Configuration Files.

With an Oracle wallet configured, and readable by you, your scripts can connect to Oracle Database with:

• Standalone connections by setting the externalauth parameter to _True_in oracledb.connect():
  connection = oracledb.connect(externalauth=True, dsn="mynetalias")
• Or pooled connections by setting the externalauth parameter to _True_in oracledb.create_pool(). Additionally in python-oracledb Thick mode, you must set the homogeneous parameter to False as shown below since heterogeneous pools can only be used with external authentication:
  pool = oracledb.create_pool(externalauth=True, homogeneous=False,
  dsn="mynetalias")

pool.acquire()

The dsn used in oracledb.connect() andoracledb.create_pool() must match the one used in the wallet.

After connecting, the query:

SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

will show:

Note

Wallets are also used to configure Transport Layer Security (TLS) connections. If you are using a wallet like this, you may need a database username and password in oracledb.connect() and oracledb.create_pool() calls.

External Authentication and Proxy Authentication

The following examples show external wallet authentication combined withproxy authentication. These examples use the wallet configuration from above, with the addition of a grant to another user:

ALTER USER mysessionuser GRANT CONNECT THROUGH myuser;

After connection, you can check who the session user is with:

SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

Standalone connection example:

External Authentication with proxy

connection = oracledb.connect(user="[mysessionuser]", dsn="mynetalias")

PROXY_USER: MYUSER

SESSION_USER: MYSESSIONUSER

You can also explicitly set the externalauth parameter to True in standalone connections as shown below. The externalauth parameter is optional.

External Authentication with proxy when externalauth is set to True

connection = oracledb.connect(user="[mysessionuser]", dsn="mynetalias", externalauth=True)

PROXY_USER: MYUSER

SESSION_USER: MYSESSIONUSER

Pooled connection example:

External Authentication with proxy

pool = oracledb.create_pool(externalauth=True, homogeneous=False, dsn="mynetalias") pool.acquire(user="[mysessionuser]")

PROXY_USER: MYUSER

SESSION_USER: MYSESSIONUSER

The following usage is not supported:

pool = oracledb.create_pool(user="[mysessionuser]", externalauth=True, homogeneous=False, dsn="mynetalias") pool.acquire()

4.12.2. Operating System Authentication

With Operating System authentication, Oracle allows user authentication to be performed by the operating system. The following steps give an overview of how to implement OS Authentication on Linux.

1. Log in to your computer. The commands used in these steps assume the operating system user name is “oracle”.
2. Log in to SQL*Plus as the SYSTEM user and verify the value for theOS_AUTHENT_PREFIX parameter:
   SQL> SHOW PARAMETER os_authent_prefix
   NAME TYPE VALUE

os_authent_prefix string ops$ 3. Create an Oracle database user using the os_authent_prefix determined in step 2, and the operating system user name:

CREATE USER ops$oracle IDENTIFIED EXTERNALLY; GRANT CONNECT, RESOURCE TO ops$oracle;

In Python, connect using the following code:

connection = oracledb.connect(dsn="mynetalias")

Your session user will be OPS$ORACLE.

If your database is not on the same computer as Python, you can perform testing by setting the database configuration parameter remote_os_authent=true. Beware of security concerns because this is insecure.

See Oracle Database Security Guide for more information about Operating System Authentication.

4.13. Token-Based Authentication

Token-Based Authentication allows users to connect to a database by using an encrypted authentication token without having to enter a database username and password. The authentication token must be valid and not expired for the connection to be successful. Users already connected will be able to continue work after their token has expired but they will not be able to reconnect without getting a new token.

The two authentication methods supported by python-oracledb areOpen Authorization (OAuth 2.0) and Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM). These authentication methods can use Cloud Native Authentication with the support of the Azure SDK or OCI SDK to generate access tokens and connect to Oracle Database. Alternatively, these methods can use a Python script that contains a class to generate access tokens to connect to Oracle Database.

4.13.1. Connecting Using OAuth 2.0 Token-Based Authentication

Oracle Cloud Infrastructure (OCI) users can be centrally managed in a Microsoft Entra ID (formerly Microsoft Azure Active Directory) service. Open Authorization (OAuth 2.0) token-based authentication allows users to authenticate to Oracle Database using Entra ID OAuth2 tokens. Ensure that you have a Microsoft Azure account and your Oracle Database is registered with Microsoft Entra ID. See Configuring the Oracle Database for Microsoft Entra ID Integration for more information. Both Thin and Thick modes of the python-oracledb driver support OAuth 2.0 token-based authentication.

When using python-oracledb in Thick mode, Oracle Client libraries 19.15 (or later), or 21.7 (or later) are needed.

Standalone connections and pooled connections can be created in python-oracledb Thick and Thin modes using OAuth 2.0 token-based authentication. This can be done or by using a class such as the example TokenHandlerOAuth Class or by using python-oracledb’s Azure Cloud Native Authentication Plugin (azure_tokens). Tokens can be specified using the connection parameter introduced in python-oracledb 1.1. Users of earlier python-oracledb versions can alternatively use OAuth 2.0 Token-Based Authentication Connection Strings.

4.13.1.1. OAuth2 Token Generation And Extraction

There are different ways to retrieve Entra ID OAuth2 tokens. You can use python-oracledb’s azure_tokens plugin to generate tokens. Some of the other ways to retrieve OAuth2 tokens are detailed inExamples of Retrieving Entra ID OAuth2 Tokens. You can also retrieve Entra ID OAuth2 tokens by using Azure Identity client library for Python.

Example of Generating an OAuth2 Token

An example of automating the process of generating and reading Entra ID OAuth2 tokens is:

import json import os

import oracledb import requests

class TokenHandlerOAuth:

def __init__(self,
             file_name="cached_token_file_name",
             api_key="api_key",
             client_id="client_id",
             client_secret="client_secret"):
    self.token = None
    self.file_name = file_name
    self.url = \
        f"https://login.microsoftonline.com/{api_key}/oauth2/v2.0/token"
    self.scope = \
        f"https://oracledevelopment.onmicrosoft.com/{client_id}/.default"
    if os.path.exists(file_name):
        with open(file_name) as f:
            self.token = f.read().strip()
    self.api_key = api_key
    self.client_id = client_id
    self.client_secret = client_secret

def __call__(self, refresh):
    if self.token is None or refresh:
        post_data = dict(client_id=self.client_id,
                         grant_type="client_credentials",
                         scope=self.scope,
                         client_secret=self.client_secret)
        r = requests.post(url=self.url, data=post_data)
        result = json.loads(r.text)
        self.token = result["access_token"]
        with open(self.file_name, "w") as f:
            f.write(self.token)
    return self.token

The TokenHandlerOAuth class uses a callable to generate and read OAuth2 tokens. When the callable in the TokenHandlerOAuth class is invoked for the first time to create a standalone connection or pool, the refresh parameter is False which allows the callable to return a cached token, if desired. The expiry date is then extracted from this token and compared with the current date. If the token has not expired, then it will be used directly. If the token has expired, the callable is invoked the second time with the refreshparameter set to True.

The TokenHandlerOAuth class defined here is used in the examples shown inConnection Creation with OAuth2 Access Tokens.

Example of Using a Curl Command

See using a curl command for an alternative way to generate the tokens.

4.13.1.2. Connection Creation with OAuth2 Access Tokens

For OAuth 2.0 Token-Based Authentication using a class such as the sampleTokenHandlerOAuth class, the access_token connection parameter must be specified. This parameter should be a string (or a callable that returns a string) specifying an Entra ID OAuth2 token. In the examples used below, the access_token parameter is set to a callable.

The examples used in the subsequent sections use theTokenHandlerOAuth class to generate OAuth2 tokens to connect to Oracle Autonomous Database with mutual TLS (mTLS). SeeConnecting to Oracle Cloud Autonomous Databases.

Standalone Connections in Thin Mode Using OAuth2 Tokens

When using a class such as the TokenHandlerOAuth class to generate OAuth2 tokens to connect to Oracle Autonomous Database in Thin mode, you need to explicitly set the access_token, config_dir,wallet_location, and wallet_password parameters ofconnect(). For example:

connection = oracledb.connect( access_token=TokenHandlerOAuth(), dsn=mydb_low, config_dir="path_to_unzipped_wallet", wallet_location="location_of_pem_file", wallet_password=wp)

Connection Pools in Thin Mode Using OAuth2 Tokens

When using a class such as the TokenHandlerOAuth class to generate OAuth2 tokens to connect to Oracle Autonomous Database in Thin mode, you need to explicitly set the access_token, homogeneous,config_dir, wallet_location, and wallet_password parameters ofcreate_pool(). For example:

connection = oracledb.create_pool( access_token=TokenHandlerOAuth(), homogeneous=True, # must always be True for connection pools dsn=mydb_low, config_dir="path_to_unzipped_wallet", wallet_location="location_of_pem_file", wallet_password=wp min=1, max=5, increment=2)

Note that the access_token parameter should be set to a callable. This is useful when the connection pool needs to expand and create new connections but the current token has expired. In such a case, the callable should return a string specifying the new, valid Entra ID OAuth2 token.

Standalone Connections Thick Mode Using OAuth2 Tokens

When using a class such as the TokenHandlerOAuth classto generate OAuth2 tokens to connect to Oracle Autonomous Database in Thick mode, you need to explicitly set the access_token and externalAuthparameters of connect(). For example:

connection = oracledb.connect( access_token=TokenHandlerOAuth(), externalauth=True, # must always be True in Thick mode dsn=mydb_low)

Connection Pools in Thick Mode Using OAuth2 Tokens

When using a class such as the TokenHandlerOAuth class to generate OAuth2 tokens to connect to Oracle Autonomous Database in Thick mode, you need to explicitly set the access_token, externalauth, andhomogeneous parameters of create_pool(). For example:

pool = oracledb.create_pool( access_token=TokenHandlerOAuth(), externalauth=True, # must always be True in Thick mode homogeneous=True, # must always be True in connection pools dsn=mydb_low, min=1, max=5, increment=2)

Note that the access_token parameter should be set to a callable. This is useful when the connection pool needs to expand and create new connections but the current token has expired. In such a case, the callable should return a string specifying the new, valid Entra ID OAuth2 token.

4.13.1.3. OAuth 2.0 Token-Based Authentication Connection Strings

The connection string used by python-oracledb can specify the directory where the token file is located. This syntax is usable with older versions of python-oracledb. However, it is recommended to use connection parameters introduced in python-oracledb 1.1 instead. SeeOAuth 2.0 Token-Based Authentication.

There are different ways to retrieve Entra ID OAuth2 tokens. Some of the ways to retrieve OAuth2 tokens are detailed in Examples of Retrieving Entra ID OAuth2 Tokens. You can also retrieve Entra ID OAuth2 tokens by using Azure Identity client library for Python.

Example of Using a Curl Command

Here, as an example, we are using Curl with a Resource Owner Password Credential (ROPC) Flow, that is, a curl command is used against the Entra ID API to get the Entra ID OAuth2 token:

curl -X POST -H 'Content-Type: application/x-www-form-urlencoded' https://login.microsoftonline.com/your_tenant_id/oauth2/v2.0/token -d 'client_id=your_client_id' -d 'grant_type=client_credentials' -d 'scope=https://oracledevelopment.onmicrosoft.com/your_client_id/.default' -d 'client_secret=your_client_secret'

This command generates a JSON response with token type, expiration, and access token values. The JSON response needs to be parsed so that only the access token is written and stored in a file. You can save the value ofaccess_token generated to a file and set TOKEN_LOCATION to the location of token file. See TokenHandlerOAuth class for an example of generating tokens.

The Oracle Net parameters TOKEN_AUTH and TOKEN_LOCATION must be set when you are using the connection string syntax. Also, the PROTOCOLparameter must be tcps and SSL_SERVER_DN_MATCH should be ON.

You can set TOKEN_AUTH=OAUTH. There is no default location set in this case, so you must set TOKEN_LOCATION to either of the following:

• A directory, in which case, you must create a file named token which contains the token value
• A fully qualified file name, in which case, you must specify the entire path of the file which contains the token value

You can either set TOKEN_AUTH and TOKEN_LOCATION in a sqlnet.ora file or alternatively, you can specify it inside a Connect Descriptor, for example when using a tnsnames.ora file:

db_alias = (DESCRIPTION = (ADDRESS=(PROTOCOL=TCPS)(PORT=1522)(HOST=xxx.oraclecloud.com)) (CONNECT_DATA=(SERVICE_NAME=xxx.adb.oraclecloud.com)) (SECURITY = (SSL_SERVER_CERT_DN="CN=xxx.oraclecloud.com,
O=Oracle Corporation,L=Redwood City,ST=California,C=US") (TOKEN_AUTH=OAUTH) (TOKEN_LOCATION="/home/user1/mytokens/oauthtoken") ) )

The TOKEN_AUTH and TOKEN_LOCATION values in a connection string take precedence over the sqlnet.ora settings.

Standalone connection example:

connection = oracledb.connect(dsn=db_alias, externalauth=True)

Connection pool example:

pool = oracledb.create_pool(dsn=db_alias, externalauth=True, homogeneous=False, min=1, max=2, increment=1)

connection = pool.acquire()

4.13.1.4. Azure Cloud Native Authentication with the azure_tokens Plugin

With Cloud Native Authentication, python-oracledb’s azure_tokens plugin can automatically generate and refresh OAuth2 tokens when required with the support of the Microsoft Authentication Library (MSAL).

The azure_tokens plugin can be imported like:

import oracledb.plugins.azure_tokens

The plugin has a Python package dependency which needs to be installed separately before the plugin can be used, see Install Modules for the Azure Cloud Native Authentication Plugin.

The azure_tokens plugin defines and registers a parameter hook function which uses the connection parameterextra_auth_params passed to oracledb.connect(),oracledb.create_pool(), oracledb.connect_async(), ororacledb.create_pool_async(). Using this parameter’s values, the hook function sets the access_token parameter of a ConnectParams object to a callable which generates an OAuth2 token. Python-oracledb then acquires and uses a token to transparently complete connection or pool creation calls.

For OAuth 2.0 Token-Based Authentication connection and pool creation, theextra_auth_params parameter should be a dictionary with keys as shown in the following table.

Table 4.8 Azure Cloud Native Authentication Configuration Keys

All keys and values other than auth_type are used by the Microsoft Authentication Library (MSAL) API calls in the plugin. The plugin implementation can be seen in plugins/azure_tokens.py.

For information on the Azure specific configuration parameters, see MSAL.

The examples in the subsequent sections use the azure_tokens plugin to generate OAuth2 tokens to connect to Oracle Autonomous Database with mutual TLS (mTLS). See Connecting to Oracle Cloud Autonomous Databases.

Standalone Connections in Thin Mode Using OAuth2 Tokens

When using the azure_tokens plugin to generate OAuth2 tokens to connect to Oracle Autonomous Database in Thin mode, you need to explicitly set the extra_auth_params, config_dir,wallet_location, and wallet_password parameter ofconnect(). For example:

import oracledb.plugins.azure_tokens

token_based_auth = { "auth_type": "AzureServicePrincipal", # Azure specific configuration "authority": , # parameters to be set when using "client_id": , # the azure_tokens plugin "client_credential": , "scopes": }

connection = oracledb.connect( dsn=mydb_low, config_dir="path_to_unzipped_wallet", wallet_location="location_of_pem_file", wallet_password=wp, extra_auth_params=token_based_auth)

Connection Pools in Thin Mode Using OAuth2 Tokens

When using the azure_tokens plugin to generate OAuth2 tokens to connect to Oracle Autonomous Database in Thin mode, you need to explicitly set the homogeneous, extra_auth_params,config_dir, wallet_location, and wallet_password parameters ofcreate_pool(). For example:

import oracledb.plugins.azure_tokens

token_based_auth = { "auth_type": "AzureServicePrincipal", # Azure specific configuration "authority": , # parameters to be set when using "client_id": , # the azure_tokens plugin "client_credential": , "scopes": }

connection = oracledb.create_pool( dsn=mydb_low, config_dir="path_to_unzipped_wallet", homogeneous=true, # must always be True for connection pools wallet_location="location_of_pem_file", wallet_password=wp, extra_auth_params=token_based_auth)

Standalone Connections Thick Mode Using OAuth2 Tokens

When using the azure_tokens plugin to generate OAuth2 tokens to connect to Oracle Autonomous Database in Thick mode, you need to explicitly set the extra_auth_params and externalauthparameter of connect(). For example:

import oracledb.plugins.azure_tokens

token_based_auth = { "auth_type": "AzureServicePrincipal", # Azure specific configuration "authority": , # parameters to be set when using "client_id": , # the azure_tokens plugin "client_credential": , "scopes": }

connection = oracledb.connect( externalauth=True, # must always be True in Thick mode dsn=mydb_low, extra_auth_params=token_based_auth)

Connection Pools in Thick Mode Using OAuth2 Tokens

When using the azure_tokens plugin to generate OAuth2 tokens to connect to Oracle Autonomous Database in Thick mode, you need to explicitly set the extra_auth_params, externalauth, andhomogeneous parameters of create_pool().

import oracledb.plugins.azure_tokens

token_based_auth = { "auth_type": "AzureServicePrincipal", # Azure specific configuration "authority": , # parameters to be set when using "client_id": , # the azure_tokens plugin "client_credential": , "scopes": }

connection = oracledb.create_pool( externalauth=True, # must always be True in Thick mode homogeneous=True, # must always be True for connection pools dsn=mydb_low, extra_auth_params=token_based_auth)

4.13.2. Connecting Using OCI IAM Token-Based Authentication

Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) provides its users with a centralized database authentication and authorization system. Using this authentication method, users can use the database access token issued by OCI IAM to authenticate to the Oracle Autonomous Database. Both Thin and Thick modes of the python-oracledb driver support OCI IAM token-based authentication.

When using python-oracledb in Thick mode, Oracle Client libraries 19.14 (or later), or 21.5 (or later) are needed.

Standalone connections and pooled connections can be created in python-oracledb Thick and Thin modes using OCI IAM token-based authentication. This can be done by using a class like the sample TokenHandlerIAM class or by using python-oracledb’s OCI Cloud Native Authentication Plugin (oci_tokens). Tokens can be specified using the connection parameter introduced in python-oracledb 1.1. Users of earlier python-oracledb versions can alternatively use OCI IAM Token-Based Authentication Connection Strings.

4.13.2.1. OCI IAM Token Generation and Extraction

Authentication tokens can be generated using python-oracledb’soci_tokens plugin.

Alternatively, authentication tokens can be generated through execution of an Oracle Cloud Infrastructure command line interface (OCI-CLI) command

On Linux, a folder .oci/db-token will be created in your home directory. It will contain the token and private key files needed by python-oracledb.

Example of Generating an IAM Token

Here, as an example, we are using a Python script to automate the process of generating and reading OCI IAM tokens.

import os

import oracledb

class TokenHandlerIAM:

def __init__(self,
             dir_name="dir_name",
             command="oci iam db-token get"):
    self.dir_name = dir_name
    self.command = command
    self.token = None
    self.private_key = None

def __call__(self, refresh):
    if refresh:
        if os.system(self.command) != 0:
            raise Exception("token command failed!")
    if self.token is None or refresh:
        self.read_token_info()
    return (self.token, self.private_key)

def read_token_info(self):
    token_file_name = os.path.join(self.dir_name, "token")
    pkey_file_name = os.path.join(self.dir_name, "oci_db_key.pem")
    with open(token_file_name) as f:
        self.token = f.read().strip()
    with open(pkey_file_name) as f:
        if oracledb.is_thin_mode():
            self.private_key = f.read().strip()
        else:
            lines = [s for s in f.read().strip().split("\n")
                     if s not in ('-----BEGIN PRIVATE KEY-----',
                                  '-----END PRIVATE KEY-----')]
            self.private_key = "".join(lines)

The TokenHandlerIAM class uses a callable to generate and read OCI IAM tokens. When the callable in the TokenHandlerIAM class is invoked for the first time to create a standalone connection or pool, the refresh parameter is_False_ which allows the callable to return a cached token, if desired. The expiry date is then extracted from this token and compared with the current date. If the token has not expired, then it will be used directly. If the token has expired, the callable is invoked the second time with the refreshparameter set to True.

The TokenHandlerIAM class defined here is used in the examples shown inConnection Creation with OCI IAM Access Tokens.

4.13.2.2. Connection Creation with OCI IAM Access Tokens

For OCI IAM Token-Based Authentication with a class such as the sampleTokenHandlerIAM class, the access_token connection parameter must be specified. This parameter should be a 2-tuple (or a callable that returns a 2-tuple) containing the token and private key. In the examples used below, the access_token parameter is set to a callable.

The examples used in the subsequent sections use theTokenHandlerIAM class to generate OCI IAM tokens to connect to Oracle Autonomous Database with mutual TLS (mTLS). See Connecting to Oracle Cloud Autonomous Databases.

Standalone Connections in Thin Mode Using OCI IAM Tokens

When using a class such as the TokenHandlerIAM class to generate OCI IAM tokens to connect to Oracle Autonomous Database in Thin mode, you need to explicitly set the access_token, config_dir,wallet_location, and wallet_password parameters ofconnect(). For example:

connection = oracledb.connect( access_token=TokenHandlerIAM(), dsn=mydb_low, config_dir="path_to_unzipped_wallet", wallet_location="location_of_pem_file", wallet_password=wp)

Connection Pools in Thin Mode Using OCI IAM Tokens

When using a class such as TokenHandlerIAM class to generate OCI IAM tokens to connect to Oracle Autonomous Database in Thin mode, you need to explicitly set the access_token, homogeneous,config_dir, wallet_location, and wallet_password parameters ofcreate_pool(). For example:

connection = oracledb.create_pool( access_token=TokenHandlerIAM(), homogeneous=True, # must always be set to True for connection pools dsn=mydb_low, config_dir="path_to_unzipped_wallet", wallet_location="location_of_pem_file", wallet_password=wp min=1, max=5, increment=2)

Note that the access_token parameter should be set to a callable. This is useful when the connection pool needs to expand and create new connections but the current token has expired. In such a case, the callable should return a string specifying the new, valid access token.

Standalone Connections in Thick Mode Using OCI IAM Tokens

When using a class such as TokenHandlerIAM class to generate OCI IAM tokens to connect to Oracle Autonomous Database in Thick mode, you need to explicitly set the access_token and externalAuth parameters of connect(). For example:

connection = oracledb.connect( access_token=TokenHandlerIAM(), externalauth=True, # must always be True in Thick mode dsn=mydb_low)

Connection Pools in Thick Mode Using OCI IAM Tokens

When using a class such as TokenHandlerIAM class to generate OCI IAM tokens to connect to Oracle Autonomous Database in Thick mode, you need to explicitly set the access_token, externalauth, andhomogeneous parameters of oracledb.create_pool(). For example:

pool = oracledb.create_pool( access_token=TokenHandlerIAM(), externalauth=True, # must always be True in Thick mode homogeneous=True, # must always be True in connection pools dsn=mydb_low, min=1, max=5, increment=2)

Note that the access_token parameter should be set to a callable. This is useful when the connection pool needs to expand and create new connections but the current token has expired. In such a case, the callable should return a string specifying the new, valid access token.

4.13.2.3. OCI IAM Token-Based Authentication Connection Strings

The connection string used by python-oracledb can specify the directory where the token and private key files are located. This syntax is usable with older versions of python-oracledb. However, it is recommended to use connection parameters introduced in python-oracledb 1.1 instead. SeeOCI IAM Token-Based Authentication.

The Oracle Cloud Infrastructure command line interface (OCI-CLI) can be used externally to get tokens and private keys from OCI IAM, for example with the OCI-CLI oci iam db-token get command.

The Oracle Net parameter TOKEN_AUTH must be set when you are using the connection string syntax. Also, the PROTOCOL parameter must be tcpsand SSL_SERVER_DN_MATCH should be ON.

You can set TOKEN_AUTH=OCI_TOKEN in a sqlnet.ora file. Alternatively, you can specify it in a Connect Descriptor, for example when using a tnsnames.ora file:

db_alias = (DESCRIPTION = (ADDRESS=(PROTOCOL=TCPS)(PORT=1522)(HOST=xxx.oraclecloud.com)) (CONNECT_DATA=(SERVICE_NAME=xxx.adb.oraclecloud.com)) (SECURITY = (SSL_SERVER_CERT_DN="CN=xxx.oraclecloud.com,
O=Oracle Corporation,L=Redwood City,ST=California,C=US") (TOKEN_AUTH=OCI_TOKEN) ) )

The default location for the token and private key is the same default location that the OCI-CLI tool writes to. For example ~/.oci/db-token/ on Linux.

If the token and private key files are not in the default location then their directory must be specified with the TOKEN_LOCATION parameter in asqlnet.ora file or in a Connect Descriptor, for example when using a tnsnames.orafile:

db_alias = (DESCRIPTION = (ADDRESS=(PROTOCOL=TCPS)(PORT=1522)(HOST=xxx.oraclecloud.com)) (CONNECT_DATA=(SERVICE_NAME=xxx.adb.oraclecloud.com)) (SECURITY = (SSL_SERVER_CERT_DN="CN=xxx.oraclecloud.com,
O=Oracle Corporation,L=Redwood City,ST=California,C=US") (TOKEN_AUTH=OCI_TOKEN) (TOKEN_LOCATION="/path/to/token/folder") ) )

The TOKEN_AUTH and TOKEN_LOCATION values in a connection string take precedence over the sqlnet.ora settings.

Standalone connection example:

connection = oracledb.connect(dsn=db_alias, externalauth=True)

Connection pool example:

pool = oracledb.create_pool(dsn=db_alias, externalauth=True, homogeneous=False, min=1, max=2, increment=1)

connection = pool.acquire()

4.13.2.4. OCI Cloud Native Authentication with the oci_tokens Plugin

With Cloud Native Authentication, python-oracledb’s oci_tokens plugin can automatically generate and refresh OCI IAM tokens when required with the support of the Oracle Cloud Infrastructure (OCI) Software Development Kit (SDK).

The oci_tokens plugin can be imported like:

import oracledb.plugins.oci_tokens

The plugin has a Python package dependency which needs to be installed separately before the plugin can be used, see Install Modules for the OCI Cloud Native Authentication Plugin.

The oci_tokens plugin defines and registers a parameter hook function which uses the connection parameterextra_auth_params passed to oracledb.connect(),oracledb.create_pool(), oracledb.connect_async(), ororacledb.create_pool_async(). Using this parameter’s values, the hook function sets the access_token parameter of a ConnectParams object to a callable which generates an OCI IAM token. Python-oracledb then acquires and uses a token to transparently complete connection or pool creation calls.

For OCI Cloud Native Authentication connection and pool creation, theextra_auth_params parameter should be a dictionary with keys as shown in the following table.

Table 4.9 OCI Cloud Native Authentication Configuration Keys

All keys and values other than auth_type are used by the OCI SDK API calls in the plugin. The plugin implementation can be seen inplugins/oci_tokens.py.

For information on the OCI specific configuration parameters, see OCI SDK.

The examples in the subsequent sections use the oci_tokens plugin to generate OCI IAM tokens to connect to Oracle Autonomous Database with mutual TLS (mTLS). See Connecting to Oracle Cloud Autonomous Databases.

Standalone Connections in Thin Mode Using OCI IAM Tokens

When using the oci_tokens plugin to generate OCI IAM tokens to connect to Oracle Autonomous Database in Thin mode, you need to explicitly set the config_dir, wallet_location, wallet_passwordand extra_auth_params parameters of connect(). For example:

import oracledb.plugins.oci_tokens

token_based_auth = { # OCI specific configuration "auth_type": "ConfigFileAuthentication", # parameters to be set when using "profile": , # the oci_tokens plugin with "file_location": , # configuration file authentication }

connection = oracledb.connect( dsn=mydb_low, config_dir="path_to_unzipped_wallet", wallet_location="location_of_pem_file", wallet_password=wp, extra_auth_params=token_based_auth)

Connection Pools in Thin Mode Using OCI IAM Tokens

When using the oci_tokens plugin to generate OCI IAM tokens to connect to Oracle Autonomous Database in Thin mode, you need to explicitly set the config_dir, homogeneous, wallet_location,wallet_password, and extra_auth_params parameters ofcreate_pool(). For example:

import oracledb.plugins.oci_tokens

token_based_auth = { "auth_type": "SimpleAuthentication", # OCI specific configuration "user": , # parameters to be set when using "key_file": , # the oci_tokens plugin with "fingerprint": , # simple authentication "tenancy": , "region": , "profile": }

connection = oracledb.create_pool( dsn=mydb_low, config_dir="path_to_unzipped_wallet", homogeneous=true, # must always be True for connection pools wallet_location="location_of_pem_file", wallet_password=wp, extra_auth_params=token_based_auth)

Standalone Connections in Thick Mode Using OCI IAM Tokens

When using the oci_tokens plugin to generate OCI IAM tokens to connect to Oracle Autonomous Database in Thick mode, you need to explicitly set the externalauth and extra_auth_params parameters oforacledb.connect(). For example:

import oracledb.plugins.oci_tokens

token_based_auth = { "auth_type": "SimpleAuthentication", # OCI specific configuration "user": , # parameters to be set when using "key_file": , # the oci_tokens plugin with "fingerprint": , # simple authentication "tenancy": , "region": , "profile": } connection = oracledb.connect( externalauth=True, dsn=mydb_low, extra_auth_params=token_based_auth)

Connection Pools in Thick Mode Using OCI IAM Tokens

When using the oci_tokens plugin to generate OCI IAM tokens to connect to Oracle Autonomous Database in Thick mode, you need to explicitly set the externalauth, homogeneous, andextra_auth_params parameters of create_pool(). For example:

import oracledb.plugins.oci_tokens

token_based_auth = { # OCI specific configuration "auth_type": "ConfigFileAuthentication", # parameters to be set when using "profile": , # the oci_tokens plugin with "file_location": , # configuration file authentication }

connection = oracledb.create_pool( externalauth=True, # must always be True in Thick mode homogeneous=True, # must always be True for connection pools dsn=mydb_low, extra_auth_params=token_based_auth)

4.14. Privileged Connections

The mode parameter of the function oracledb.connect() specifies the database privilege that you want to associate with the user.

The example below shows how to connect to Oracle Database as SYSDBA:

connection = oracledb.connect(user="sys", password=syspwd, dsn="dbhost.example.com/orclpdb", mode=oracledb.AuthMode.SYSDBA) # or mode=oracledb.AUTH_MODE_SYSDBA

with connection.cursor() as cursor: cursor.execute("GRANT SYSOPER TO hr")

This is equivalent to executing the following in SQL*Plus:

CONNECT sys/syspwd@dbhost.example.com/orclpdb AS SYSDBA GRANT SYSOPER TO hr;

In python-oracledb Thick mode, when python-oracledb uses Oracle Client libraries from a database software installation, you can use “bequeath” connections to databases that are also using the same Oracle libraries. Do this by setting the standard Oracle environment variables such asORACLE_HOME and ORACLE_SID and connecting in Python like:

oracledb.init_oracle_client()

conn = oracledb.connect(mode=oracledb.AuthMode.SYSDBA)

This is equivalent to executing the following in SQL*Plus:

4.15. Securely Encrypting Network Traffic to Oracle Database

You can encrypt data transferred between the Oracle Database and python-oracledb so that unauthorized parties are not able to view plain text values as the data passes over the network.

Both python-oracledb Thin and Thick modes support TLS. Refer to the Oracle Database Security Guide for more configuration information.

4.15.1. Native Network Encryption

The python-oracledb Thick mode can additionally use Oracle Database’s native network encryption.

With native network encryption, the client and database server negotiate a key using Diffie-Hellman key exchange. This provides protection against man-in-the-middle attacks.

Native network encryption can be configured by editing Oracle Net’s optionalsqlnet.ora configuration file. The file on either the database server and/or on each python-oracledb ‘client’ machine can be configured. Parameters control whether data integrity checking and encryption is required or just allowed, and which algorithms the client and server should consider for use.

As an example, to ensure all connections to the database are checked for integrity and are also encrypted, create or edit the Oracle Database$ORACLE_HOME/network/admin/sqlnet.ora file. Set the checksum negotiation to always validate a checksum and set the checksum type to your desired value. The network encryption settings can similarly be set. For example, to use the SHA512 checksum and AES256 encryption use:

SQLNET.CRYPTO_CHECKSUM_SERVER = required SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA512) SQLNET.ENCRYPTION_SERVER = required SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)

If you definitely know that the database server enforces integrity and encryption, then you do not need to configure python-oracledb separately. However, you can also, or alternatively do so, depending on your business needs. Create a sqlnet.ora on your client machine and locate it with otherOptional Oracle Net Configuration Files:

SQLNET.CRYPTO_CHECKSUM_CLIENT = required SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA512) SQLNET.ENCRYPTION_CLIENT = required SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)

The client and server sides can negotiate the protocols used if the settings indicate more than one value is accepted.

Note that these are example settings only. You must review your security requirements and read the documentation for your Oracle version. In particular, review the available algorithms for security and performance.

The NETWORK_SERVICE_BANNER column of the database viewV$SESSION_CONNECT_INFO can be used to verify the encryption status of a connection. For example with SQL*Plus:

SQL> select network_service_banner from v$session_connect_info;

If the connection is encrypted, then this query prints an output that includes the available encryption service, the crypto-checksumming service, and the algorithms in use, such as:

NETWORK_SERVICE_BANNER

TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production Encryption service for Linux: Version 19.0.1.0.0 - Production AES256 Encryption service adapter for Linux: Version 19.0.1.0.0 - Production Crypto-checksumming service for Linux: Version 19.0.1.0.0 - Production SHA256 Crypto-checksumming service adapter for Linux: Version 19.0.1.0.0 - Production

If the connection is unencrypted, then the query will only print the available encryption and crypto-checksumming services in the output. For example:

NETWORK_SERVICE_BANNER

TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production Encryption service for Linux: Version 19.0.1.0.0 - Production Crypto-checksumming service for Linux: Version 19.0.1.0.0 - Production

For more information about Oracle Data Network Encryption and Integrity, and for information about configuring TLS network encryption, refer to the Oracle Database Security Guide.

4.16. Resetting Passwords

After connecting to Oracle Database, passwords can be changed by callingConnection.changepassword():

Get the passwords from somewhere, such as prompting the user

oldpwd = getpass.getpass(f"Old Password for {username}: ") newpwd = getpass.getpass(f"New Password for {username}: ")

connection.changepassword(oldpwd, newpwd)

When a password has expired and you cannot connect directly, you can connect and change the password in one operation by using the newpassword parameter of the function oracledb.connect() constructor:

Get the passwords from somewhere, such as prompting the user

oldpwd = getpass.getpass(f"Old Password for {username}: ") newpwd = getpass.getpass(f"New Password for {username}: ")

connection = oracledb.connect(user=username, password=oldpwd, dsn="dbhost.example.com/orclpdb", newpassword=newpwd)

4.17. Connecting to Oracle Cloud Autonomous Databases

Python applications can connect to Oracle Autonomous Database (ADB) in Oracle Cloud using one-way TLS (Transport Layer Security) or mutual TLS (mTLS). One-way TLS and mTLS provide enhanced security for authentication and encryption.

A database username and password are still required for your application connections. If you need to create a new database schema so you do not login as the privileged ADMIN user, refer to the relevant Oracle Cloud documentation, for example see Create Database Users in the Oracle Autonomous Database manual.

4.17.1. One-way TLS Connection to Oracle Autonomous Database

With one-way TLS, python-oracledb applications can connect to Oracle ADB without using a wallet. Both Thin and Thick modes of the python-oracledb driver support one-way TLS. Applications that use the python-oracledb Thick mode, can connect to the Oracle ADB through one-way TLS only when using Oracle Client library versions 19.14 (or later) or 21.5 (or later).

To enable one-way TLS for an ADB instance, complete the following steps in an Oracle Cloud console in the Autonomous Database Information section of the ADB instance details:

1. Click the Edit link next to Access Control List to update the Access Control List (ACL). The Edit Access Control List dialog box is displayed.
2. In the Edit Access Control List dialog box, select the type of address list entries and the corresponding values. You can include the required IP addresses, hostnames, or Virtual Cloud Networks (VCNs). The ACL limits access to only the IP addresses or VCNs that have been defined and blocks all other incoming traffic.
3. Navigate back to the ADB instance details page and click the Edit link next to Mutual TLS (mTLS) Authentication. The Edit Mutual TLS Authenticationis displayed.
4. In the Edit Mutual TLS Authentication dialog box, deselect theRequire mutual TLS (mTLS) authentication check box to disable the mTLS requirement on Oracle ADB and click Save Changes.
5. Navigate back to the ADB instance details page and click DB Connection on the top of the page. A Database Connection dialog box is displayed.
6. In the Database Connection dialog box, select TLS from the Connection Stringsdrop-down list.
7. Copy the appropriate Connection String of the database instance used by your application.

Applications can connect to your Oracle ADB instance using the database credentials and the copied Connect Descriptor. For example, to connect as the ADMIN user:

cs = '''(description = (retry_count=20)(retry_delay=3)(address=(protocol=tcps) (port=1522)(host=xxx.oraclecloud.com))(connect_data=(service_name=xxx.adb.oraclecloud.com)) (security=(ssl_server_dn_match=yes)(ssl_server_cert_dn="CN=xxx.oraclecloud.com, O=Oracle Corporation, L=Redwood City, T=California, C=US")))'''

connection = oracledb.connect(user="admin", password=pw, dsn=cs)

You can download the ADB connection wallet using the DB Connection button and extract the tnsnames.ora file, or create one yourself if you prefer to keep connections strings out of application code, seeTNS Aliases for Connection Strings.

You may be interested in the blog post Easy wallet-less connections to Oracle Autonomous Databases in Python.

4.17.2. Mutual TLS (mTLS) Connection to Oracle Autonomous Database

To enable python-oracledb connections to Oracle Autonomous Database in Oracle Cloud using mTLS, a wallet needs to be downloaded from the cloud console. mTLS is sometimes called Two-way TLS.

4.17.2.1. Install the Wallet and Network Configuration Files

From the Oracle Cloud console for the database, download the wallet zip file using the DB Connection button. The zip contains the wallet and network configuration files. When downloading the zip, the cloud console will ask you to create a wallet password. This password is used by python-oracledb in Thin mode, but not in Thick mode.

Note: keep wallet files in a secure location and only share them and the password with authorized users.

In python-oracledb Thin mode

For python-oracledb in Thin mode, only two files from the zip are needed:

• tnsnames.ora - Maps net service names used for application connection strings to your database services
• ewallet.pem - Enables SSL/TLS connections in Thin mode. Keep this file secure

If you do not have a PEM file, see Creating a PEM File for python-oracledb Thin Mode.

Unzip the wallet zip file and move the required files to a location such as/opt/OracleCloud/MYDB.

Connection can be made using your database credentials and setting the dsnparameter to the desired network alias from the tnsnames.ora file. The config_dir parameter indicates the directory containing tnsnames.ora. The wallet_locationparameter is the directory containing the PEM file. In this example the files are in the same directory. The wallet_password parameter should be set to the password created in the cloud console when downloading the wallet. For example, to connect as the ADMIN user using the mydb_low network service name:

connection = oracledb.connect(user="admin", password=pw, dsn="mydb_low", config_dir="/opt/OracleCloud/MYDB", wallet_location="/opt/OracleCloud/MYDB", wallet_password=wp)

In python-oracledb Thick mode

For python-oracledb in Thick mode, only these files from the zip are needed:

• tnsnames.ora - Maps net service names used for application connection strings to your database services
• sqlnet.ora - Configures Oracle Network settings
• cwallet.sso - Enables SSL/TLS connections in Thick mode. Keep this file secure

Unzip the wallet zip file. There are two options for placing the required files:

• Move the three files to the network/admin directory of the client libraries used by your application. For example if you are using Instant Client 19c and it is in $HOME/instantclient_19_15, then you would put the wallet files in $HOME/instantclient_19_15/network/admin/.
  Connection can be made using your database credentials and setting thedsn parameter to the desired network alias from the tnsnames.ora file. For example, to connect as the ADMIN user using themydb_low network service name:
  connection = oracledb.connect(user="admin", password=pw, dsn="mydb_low")
• Alternatively, move the three files to any accessible directory, for example/opt/OracleCloud/MYDB.
  Then edit sqlnet.ora and change the wallet location directory to the directory containing the cwallet.sso file. For example:
  WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/opt/OracleCloud/MYDB")))
  SSL_SERVER_DN_MATCH=yes
  Since the tnsnames.ora and sqlnet.ora files are not in the default location, your application needs to indicate where they are, either with theconfig_dir parameter to oracledb.init_oracle_client(), or using the TNS_ADMIN environment variable. See Optional Oracle Net Configuration Files. (Neither of these settings are needed, and you do not need to edit sqlnet.ora, if you have put all the files in the network/admin directory.)
  For example, to connect as the ADMIN user using the mydb_low network service name:
  oracledb.init_oracle_client(config_dir="/opt/OracleCloud/MYDB")
  connection = oracledb.connect(user="admin", password=pw, dsn="mydb_low")

In python-oracle Thick mode, to create mTLS connections in one Python process to two or more Oracle Autonomous Databases, move each cwallet.sso file to its own directory. For each connection use different connection stringWALLET_LOCATION parameters to specify the directory of each cwallet.ssofile. It is recommended to use Oracle Client libraries 19.17 (or later) when using multiple wallets.

4.17.2.2. Using the Easy Connect Syntax with Oracle Autonomous Database

When python-oracledb is using Oracle Client libraries 19c, or later, you can optionally use Easy Connect syntax to connect to Oracle Autonomous Database.

The mapping from the cloud tnsnames.ora entries to an Easy Connect string is:

protocol://host:port/service_name?wallet_location=/my/dir&retry_count=N&retry_delay=N

For example, if your tnsnames.ora file had an entry:

cjjson_high = (description=(retry_count=20)(retry_delay=3) (address=(protocol=tcps)(port=1522) (host=xxx.oraclecloud.com)) (connect_data=(service_name=abc_cjjson_high.adb.oraclecloud.com)) (security=(ssl_server_cert_dn="CN=xxx.oraclecloud.com,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

Then your applications can connect using the connection string:

dsn = "tcps://xxx.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON&retry_count=20&retry_delay=3" connection = oracledb.connect(user="hr", password=userpwd, dsn=dsn)

The wallet_location parameter needs to be set to the directory containing the cwallet.sso or ewallet.pem file from the wallet zip. The other wallet files, including tnsnames.ora, are not needed when you use the Easy Connect syntax.

You can add other Easy Connect parameters to the connection string, for example:

dsn = dsn + "&https_proxy=myproxy.example.com&https_proxy_port=80"

With python-oracledb Thin mode, the wallet password needs to be passed as a connection parameter.

4.17.2.3. Creating a PEM File for python-oracledb Thin Mode

For mutual TLS in python-oracledb Thin mode, the certificate must be Privacy Enhanced Mail (PEM) format. If you are using Oracle Autonomous Database your wallet zip file will already include a PEM file.

If you have a PKCS12 ewallet.p12 file and need to create PEM file, you can use third party tools or the script below to do a conversion. For example, you can invoke the script by passing the wallet password and the directory containing the PKCS12 file:

python create_pem.py --wallet-password 'xxxxx' /Users/scott/cloud_configs/MYDBDIR

Once the PEM file has been created, you can use it by passing its directory location as the wallet_location parameter to oracledb.connect() ororacledb.create_pool(). These methods also accept awallet_password parameter. See Mutual TLS (mTLS) Connection to Oracle Autonomous Database.

Script to convert from PKCS12 to PEM

create_pem.py

import argparse import getpass import os

from cryptography.hazmat.primitives.serialization
import pkcs12, Encoding, PrivateFormat, BestAvailableEncryption,
NoEncryption

parse command line

parser = argparse.ArgumentParser(description="convert PKCS#12 to PEM") parser.add_argument("wallet_location", help="the directory in which the PKCS#12 encoded " "wallet file ewallet.p12 is found") parser.add_argument("--wallet-password", help="the password for the wallet which is used to " "decrypt the PKCS#12 encoded wallet file; if not " "specified, it will be requested securely") parser.add_argument("--no-encrypt", dest="encrypt", action="store_false", default=True, help="do not encrypt the converted PEM file with the " "wallet password") args = parser.parse_args()

validate arguments and acquire password if one was not specified

pkcs12_file_name = os.path.join(args.wallet_location, "ewallet.p12") if not os.path.exists(pkcs12_file_name): msg = f"wallet location {args.wallet_location} does not contain "
"ewallet.p12" raise Exception(msg) if args.wallet_password is None: args.wallet_password = getpass.getpass()

pem_file_name = os.path.join(args.wallet_location, "ewallet.pem") pkcs12_data = open(pkcs12_file_name, "rb").read() result = pkcs12.load_key_and_certificates(pkcs12_data, args.wallet_password.encode()) private_key, certificate, additional_certificates = result if args.encrypt: encryptor = BestAvailableEncryption(args.wallet_password.encode()) else: encryptor = NoEncryption() with open(pem_file_name, "wb") as f: f.write(private_key.private_bytes(Encoding.PEM, PrivateFormat.PKCS8, encryptor)) f.write(certificate.public_bytes(Encoding.PEM)) for cert in additional_certificates: f.write(cert.public_bytes(Encoding.PEM)) print("PEM file", pem_file_name, "written.")

4.18. Connecting Through a Firewall via a Proxy

If you are behind a firewall, you can tunnel TLS/SSL connections via a proxy by setting connection attributes, or by making HTTPS_PROXY proxy name and HTTPS_PROXY_PORTport parameters available in your connection string.

Note

Oracle does not recommend connecting through a firewall via a proxy when performance is critical.

In python-oracledb Thin mode

• Proxy settings https_proxy and https_proxy_port can be passed during connection or pool creation. Use appropriate values for your proxy:
  connection = oracledb.connect(user="admin", password=pw, dsn="mydb_low",
  config_dir="/opt/OracleCloud/MYDB",
  wallet_location="/opt/OracleCloud/MYDB", wallet_password=wp,
  https_proxy="myproxy.example.com", https_proxy_port=80)
• Alternatively, add the parameters to your Easy Connectstring:
  localhost/orclpdb&https_proxy=myproxy.example.com&https_proxy_port=80
• Alternatively, update the Connect Descriptor (either being passed directly during connection or contained in yourtnsnames.ora file). If you are using atnsnames.ora file, a modified entry might look like:
  mydb_low = (description=
  (address=
  (https_proxy=myproxy.example.com)(https_proxy_port=80)
  (protocol=tcps)(port=1522)(host= . . . )

In python-oracledb Thick mode

• If you are using an Easy Connect string, addHTTPS_PROXY and HTTPS_PROXY_PORT parameters with appropriate values for your proxy. For example, you might pass parameters like:
  localhost/orclpdb&https_proxy=myproxy.example.com&https_proxy_port=80
• Alternatively, update the Connect Descriptor (either being passed directly during connection or contained in yourtnsnames.ora file). If you are using atnsnames.ora file, a modified entry might look like:
  mydb_low = (description=
  (address=
  (https_proxy=myproxy.example.com)(https_proxy_port=80)
  (protocol=tcps)(port=1522)(host= . . . )

Additionally create, or edit, a sqlnet.ora file and add a line:
SQLNET.USE_HTTPS_PROXY=on

4.19. Connecting using Multiple Wallets

You can make multiple connections with different wallets in one Python process.

In python-oracledb Thin mode

To use multiple wallets in python-oracledb Thin mode, pass the different connection strings, wallet locations, and wallet password (if required) in eachoracledb.connect() call or when creating a connection pool:

connection = oracledb.connect(user=user_name, password=userpw, dsn=dsn, config_dir="path_to_unzipped_wallet", wallet_location="location_of_pem_file", wallet_password=walletpw)

The config_dir parameter is the directory containing the tnsnames.ora file. The wallet_location parameter is the directory containing the ewallet.pem file. If you are using Oracle Autonomous Database, both of these paths are typically the same directory where thewallet.zip file was extracted. The dsn should specify a TCPS connection.

In python-oracledb Thick mode

To use multiple wallets in python-oracledb Thick mode, a TCPS connection string containing the MY_WALLET_DIRECTORY option needs to be created:

dsn = "mydb_high" # one of the network aliases from tnsnames.ora params = oracledb.ConnectParams(config_dir="path_to_unzipped_wallet", wallet_location="path_location_of_sso_file") params.parse_connect_string(dsn) dsn = params.get_connect_string() connection = oracledb.connect(user=user_name, password=password, dsn=dsn)

The config_dir parameter should be the directory containing thetnsnames.ora and sqlnet.ora files. Thewallet_location parameter is the directory containing the cwallet.ssofile. If you are using Oracle Autonomous Database, both of these paths are typically the same directory where the wallet.zip file was extracted.

Note

Use Oracle Client libraries 19.17, or later, or use Oracle Client 21c or 23ai. They contain important bug fixes for using multiple wallets in the one process.

4.20. Connecting to Oracle Globally Distributed Database

Oracle Globally Distributed Database is a feature of Oracle Database that lets you automatically distribute and replicate data across a pool of Oracle databases that share no hardware or software. It was previously known as Oracle Sharding. It allows a database table to be split so each database contains a table with the same columns but a different subset of rows. These tables are known as sharded tables. From the perspective of an application, a sharded table in Oracle Globally Distributed Database looks like a single table: the distribution of data across those shards is completely transparent to the application.

Sharding is configured in Oracle Database, see the Oracle Globally Distributed Database manual. It requires Oracle Database and Oracle Client libraries 12.2, or later.

The oracledb.connect() and ConnectionPool.acquire() functions accept shardingkey and supershardingkey parameters that are a sequence of values used to route the connection directly to a given shard. A sharding key is always required. A super sharding key is additionally required when using composite sharding, which is when data has been partitioned by a list or range (the super sharding key), and then further partitioned by a sharding key.

When creating a connection pool, the oracledb.create_pool() attributemax_sessions_per_shard can be set. This is used to balance connections in the pool equally across shards. It requires Oracle Client libraries 18.3 or later.

Shard key values may be of type string (mapping to VARCHAR2 shard keys), number (NUMBER), bytes (RAW), or date (DATE). Multiple types may be used in each array. Sharding keys of TIMESTAMP type are not supported.

When connected to a shard, queries will only return data from that shard. For queries that need to access data from multiple shards, connections can be established to the coordinator shard catalog database. In this case, no shard key or super shard key is used.

As an example of direct connection, if sharding had been configured on a single VARCHAR2 column like:

CREATE SHARDED TABLE customers ( cust_id NUMBER, cust_name VARCHAR2(30), class VARCHAR2(10) NOT NULL, signup_date DATE, cust_code RAW(20), CONSTRAINT cust_name_pk PRIMARY KEY(cust_name)) PARTITION BY CONSISTENT HASH (cust_name) PARTITIONS AUTO TABLESPACE SET ts1;

then direct connection to a shard can be made by passing a single sharding key:

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

Numbers keys can be used in a similar way:

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

When sharding by DATE, you can connect like:

import datetime

d = datetime.datetime(2014, 7, 3)

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

When sharding by RAW, you can connect like:

b = b'\x01\x04\x08';

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

Multiple keys can be specified, for example:

key_list = [70, "SCOTT", "gold", b'\x00\x01\x02']

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

A super sharding key example is:

connection = oracledb.connect(user="hr", password=userpwd, dsn="dbhost.example.com/orclpdb", supershardingkey=["goldclass"], shardingkey=["SCOTT"])