3. Initializing python-oracledb

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. Both modes have comprehensive functionality supporting the Python Database API v2.0 Specification.

All connections in an application use the same mode. See Finding the python-oracledb Mode to verify which mode is in use.

If you are upgrading from the obsolete cx_Oracle driver to python-oracledb, then refer to Upgrading from cx_Oracle 8.3 to python-oracledb for changes that may be needed.

3.1. Enabling python-oracledb Thick mode

To change from the default python-oracledb Thin mode to Thick mode:

Oracle Client libraries must be available to handle communication to your database. These need to be installed separately, see Installing python-oracledb.

Oracle Client libraries from one of the following can be used:

An Oracle Instant Client Basic or Basic Light package. This is generally the easiest if you do not already have Oracle software installed.

A full Oracle Client installation (installed by running the Oracle Universal installer runInstaller)

An Oracle Database installation, if Python is running on the same machine as the database

The Client library version does not always have to match the Oracle Database version.

Your application must call the function oracledb.init_oracle_client() to load the client libraries. For example, if the Oracle Instant Client libraries are in C:\oracle\instantclient_23_5 on Windows or $HOME/Downloads/instantclient_23_3 on macOS, then you can use:

import os
import platform

import oracledb

d = None                               # On Linux, no directory should be passed
if platform.system() == "Darwin":      # macOS
  d = os.environ.get("HOME")+("/Downloads/instantclient_23_3")
elif platform.system() == "Windows":   # Windows
  d = r"C:\oracle\instantclient_23_5"
oracledb.init_oracle_client(lib_dir=d)

The use of a ‘raw’ string r"..." on Windows means that backslashes are treated as directory separators. On Linux, the libraries must be in the system library search path before the Python process starts, preferably configured with ldconfig.

More details and options are shown in the later sections:

Notes on calling init_oracle_client()

The init_oracle_client() function must be called before any standalone connection or connection pool is created. If a connection or pool is first created, then the Thick mode cannot be enabled.
If you call init_oracle_client() with a lib_dir parameter, the Oracle Client libraries are loaded immediately from that directory. If you call init_oracle_client() but do not set the lib_dir parameter, the Oracle Client libraries are loaded immediately using the search heuristics discussed in later sections. Note if you set lib_dir on Linux and related platforms, you must still have configured the system library search path to include that directory before starting Python.
Once the Thick mode is enabled, you cannot go back to the Thin mode except by removing calls to init_oracle_client() and restarting the application.
If Oracle Client libraries cannot be loaded then init_oracle_client() will raise an error DPI-1047: Oracle Client library cannot be loaded. To resolve this, review the platform-specific instructions below or see DPI-1047. Alternatively, remove the call to init_oracle_client() and use Thin mode. The features supported by Thin mode can be found in Appendix A: Oracle Database Features Supported by python-oracledb.
On any operating system, if you set lib_dir to the library directory of a full database or full client installation (such as from running runInstaller), you will need to have previously set the Oracle environment, for example by setting the ORACLE_HOME environment variable. Otherwise you will get errors like ORA-1804. You should set this variable, and other Oracle environment variables, before starting Python, as shown in Oracle Environment Variables.
The init_oracle_client() function may be called multiple times in your application but must always pass the same arguments.

3.1.1. Enabling python-oracledb Thick Mode on Windows

On Windows, the alternative ways to enable Thick mode are:

By passing the lib_dir parameter in a call to init_oracle_client(), for example:
```
import oracledb

oracledb.init_oracle_client(lib_dir=r"C:\instantclient_23_5")
```
On Windows, when the path contains backslashes, use a ‘raw’ string like r"C:\instantclient_23_5".

This directory should contain the libraries from an unzipped Instant Client ‘Basic’ or ‘Basic Light’ package. If you pass the library directory from a full client or database installation, such as Oracle Database “XE” Express Edition, then you will need to have previously set your environment to use that same software installation. Otherwise, files such as message files will not be located and you may have library version clashes.

If the Oracle Client libraries cannot be loaded, then an exception is raised.
Alternatively, you can call init_oracle_client() without passing a lib_dir parameter:
```
import oracledb

oracledb.init_oracle_client()
```
In this case, Oracle Client libraries are first looked for in the directory where the python-oracledb binary module is installed. This directory should contain the libraries from an unzipped Instant Client ‘Basic’ or ‘Basic Light’ package.

If the libraries are not found there, the search looks at the directories on the system library search path, for example, the PATH environment variable.

If the Oracle Client libraries cannot be loaded, then an exception is raised.

3.1.2. Enabling python-oracledb Thick Mode on macOS

On macOS, the alternative ways to enable Thick mode are:

By passing the lib_dir parameter in a call to init_oracle_client(), for example:
```
import oracledb

oracledb.init_oracle_client(lib_dir="/Users/your_username/Downloads/instantclient_23_3")
```
This directory should contain the libraries from an unzipped Instant Client ‘Basic’ or ‘Basic Light’ package.
Alternatively, you can call init_oracle_client() without passing a lib_dir parameter:
```
import oracledb

oracledb.init_oracle_client()
```
In this case, the Oracle Client libraries are first looked for in the directory where the python-oracledb Thick mode binary module is installed. This directory should contain the libraries from an unzipped Instant Client ‘Basic’ or ‘Basic Light’ package, or a symbolic link to the main Oracle Client library if Instant Client is in a different directory.

You can find the directory containing the Thick mode binary module by calling the python CLI without specifying a Python script, executing import oracledb, and then typing oracledb at the prompt. For example this might show /Users/yourname/.pyenv/versions/3.9.6/lib/python3.9/site-packages/oracledb/__init__.py. After checking that /Users/yourname/.pyenv/versions/3.9.6/lib/python3.9/site-packages/oracledb contains the binary module thick_impl.cpython-39-darwin.so you could then run these commands in a terminal window:
```
CLIENT_DIR=~/Downloads/instantclient_23_3
DPY_DIR=~/.pyenv/versions/3.9.6/lib/python3.9/site-packages/oracledb
ln -s $CLIENT_DIR/libclntsh.dylib $DPY_DIR
```
This can be automated in Python with:
```
CLIENT_DIR = "~/Downloads/instantclient_23_3"
LIB_NAME = "libclntsh.dylib"

import os
import oracledb

target_dir = oracledb.__path__[0]
os.symlink(os.path.join(CLIENT_DIR, LIB_NAME),
           os.path.join(target_dir, LIB_NAME))
```
If python-oracledb does not find the Oracle Client library in that directory, the directories on the system library search path may be used, for example, ~/lib/ and /usr/local/lib, or in $DYLD_LIBRARY_PATH. These paths will vary with macOS version and Python version. Any value in DYLD_LIBRARY_PATH will not propagate to a sub-shell, so do not rely on setting it.

If the Oracle Client libraries cannot be loaded, then an exception is raised.

Ensure that the Python process has directory and file access permissions for the Oracle Client libraries.

3.1.4. Tracing Oracle Client Library Loading

To trace the loading of Oracle Client libraries, the environment variable DPI_DEBUG_LEVEL can be set to 64 before starting Python. At a Windows command prompt, this could be done with:

set DPI_DEBUG_LEVEL=64

On Linux and macOS, you might use:

export DPI_DEBUG_LEVEL=64

When your python-oracledb application is run, logging output is shown on the terminal.

3.2. Explicitly Enabling python-oracledb Thin Mode

Python-oracledb defaults to Thin mode but can be changed to use Thick mode. In one special case, you may wish to explicitly enable Thin mode by calling oracledb.enable_thin_mode() which will prevent Thick mode from ever being used. Most applications will not need to call this method.

To allow application portability, the driver’s internal logic allows applications to initially attempt standalone connection creation in Thin mode, but then lets them enable Thick mode if that connection is unsuccessful. An example is when trying to connect to an Oracle Database that turns out to be an old version that requires Thick mode. This heuristic means Thin mode is not enforced until the initial connection is successful. Since all connections must be the same mode, any second and subsequent concurrent Thin mode connection attempt will wait for the initial standalone connection to succeed, meaning the driver mode is no longer potentially changeable to Thick mode, thus letting those additional connections be established in Thin mode.

If you have multiple threads concurrently creating standalone Thin mode connections, you may wish to call oracledb.enable_thin_mode() as part of your application initialization. This is not required but avoids the mode determination delay.

The mode determination delay does not affect the following cases, so calling enable_thin_mode() is not needed for them:

Single-threaded applications using standalone connections.
Single or multi-threaded applications using connection pools (even with min of 0).

The delay also does not affect applications that have already called oracledb.init_oracle_client() to enable Thick mode.

To explicitly enable Thin mode, call enable_thin_mode(), for example:

import oracledb

oracledb.enable_thin_mode()

Once this method is called, then python-oracledb Thick mode cannot be enabled. If you call oracledb.init_oracle_client(), you will get the following error:

DPY-2019: python-oracledb thick mode cannot be used because thin mode has
already been enabled or a thin mode connection has already been created

If you have already enabled Thick mode by calling oracledb.init_oracle_client() and then call oracledb.enable_thin_mode(), you will get the following error:

DPY-2053: python-oracledb thin mode cannot be used because thick mode has
already been enabled

3.3. Optional Oracle Configuration Files

3.3.1. Optional Oracle Net Configuration Files

Optional Oracle Net configuration files may be read when connecting or creating connection pools. These files affect connection behavior. The common files are:

tnsnames.ora: A configuration file that defines databases aliases and their related connection configuration information used for establishing connections. See TNS Aliases for Connection Strings.
sqlnet.ora: A configuration file that contains settings for features such as connection failover, network encryption, logging, and tracing. The sqlnet.ora file is only used in python-oracledb Thick mode. See Enabling python-oracledb Thick mode. In python-oracledb Thin mode, many of the equivalent settings can be defined as connection time parameters.

See Using Optional Oracle Configuration Files to understand how python-oracledb locates the files.

3.3.2. Optional Oracle Client Configuration File

When python-oracledb Thick mode uses Oracle Client libraries version 12.1 or later, an optional client parameter file called oraaccess.xml can be used to configure some behaviors of those libraries, such as statement caching and prefetching. This can be useful to change application behavior if the application code cannot be altered.

A sample oraaccess.xml file that sets the Oracle client ‘prefetch’ value to 1000 rows for every query in the application is:

<?xml version="1.0"?>
 <oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
  xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
  schemaLocation="http://xmlns.oracle.com/oci/oraaccess
  http://xmlns.oracle.com/oci/oraaccess.xsd">
  <default_parameters>
    <prefetch>
      <rows>1000</rows>
    </prefetch>
  </default_parameters>
</oraaccess>

The oraaccess.xml file has other uses including:

Changing the value of Fast Application Notification FAN events which affects notifications and Runtime Load Balancing (RLB).
Configuring Client Result Caching parameters.
Turning on Client Statement Cache Auto-tuning.

Refer to the documentation on oraaccess.xml for more details.

See Using Optional Oracle Configuration Files to understand how python-oracledb locates the file.

For another way to set some python-oracledb behaviors without changing application code, see Python-oracledb Parameters Settable in Easy Connect Strings or Centralized Configuration Providers.

3.3.3. Using Optional Oracle Configuration Files

If you use optional Oracle configuration files such as tnsnames.ora, sqlnet.ora, or oraaccess.xml to configure your connections, then put the files in a directory accessible to python-oracledb and follow steps shown below.

Note that the Easy Connect syntax can set many common configuration options without needing tnsnames.ora, sqlnet.ora, or oraaccess.xml files.

Locating tnsnames.ora in python-oracledb Thin mode

Python-oracledb will read a tnsnames.ora file when a TNS Alias is used for the dsn parameter of oracledb.connect(), oracledb.create_pool(), oracledb.connect_async(), or oracledb.create_pool_async(). Only one tnsnames.ora file is read. If the TNS Alias is not found in that file, then connection will fail. Thin mode does not read other configuration files such as sqlnet.ora or oraaccess.xml.

In python-oracledb Thin mode, you should explicitly specify the directory because some traditional “default” locations such as $ORACLE_BASE/homes/XYZ/network/admin/ (in a read-only Oracle Database home) or the Windows registry are not automatically used.

The directory used to locate tnsnames.ora is determined as follows (first one wins):

the value of the method parameter config_dir

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

the value in the config_dir attribute of the method parameter params

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

the value of defaults.config_dir, which may have been set explicitly to a directory, or internally set during initialization to $TNS_ADMIN or $ORACLE_HOME/network/admin.
```
oracledb.defaults.config_dir = "/opt/oracle/config"
connection = oracledb.connect(user="hr", password=userpwd, dsn="orclpdb")
```

This order also applies to python-oracledb Thick mode when oracledb.defaults.thick_mode_dsn_passthrough is False.

Locating tnsnames.ora, sqlnet.ora or oraaccess.xml in python-oracledb Thick mode

In python-oracledb Thick mode, the directory containing the optional Oracle Client configuration files such as tnsnames.ora, sqlnet.ora, and oraaccess.xml can be explicitly specified, otherwise the Oracle Client libraries will use a heuristic to locate the directory.

If oracledb.defaults.thick_mode_dsn_passthrough is False, then the following applies to all files except tnsnames.ora.

The configuration file directory is determined as follows:

From the config_dir parameter in the oracledb.init_oracle_client() call:
```
oracledb.init_oracle_client(config_dir="/opt/oracle/config")
```
On Windows, when the path contains backslashes, use a ‘raw’ string like r"C:\instantclient_23_5".
If init_oracle_client() is called to enable Thick mode but config_dir is not specified, then default directories are searched for the configuration files. This is platform specific and controlled by Oracle Client. Directories include:
- Your home directory, using $HOME/.tnsnames.ora and $HOME/.sqlnet.ora
- The directory /var/opt/oracle on Solaris, and /etc on other UNIX platforms.
- The directory specified by the TNS_ADMIN environment variable.
- For Oracle Instant Client ZIP files, the network/admin subdirectory of Instant Client, for example /opt/oracle/instantclient_23_5/network/admin.
- For Oracle Instant Client RPMs, the network/admin subdirectory of Instant Client, for example /usr/lib/oracle/23.5/client64/lib/network/admin.
- When using libraries from a local Oracle Database or full client installation, in $ORACLE_HOME/network/admin or $ORACLE_BASE_HOME/network/admin.

On Windows, in a full database install, the Windows registry may be also be consulted by Oracle Client.

For information about the search path see Oracle Net Services Reference for more information.

The documentation Network Configuration has additional information about some specific Oracle Net configuration useful for applications.

Setting thick_mode_dsn_passthrough

When oracledb.defaults.thick_mode_dsn_passthrough is True, it is the Oracle Client libraries that locate and read any optional tnsnames.ora configuration. This was always the behavior of python-oracledb Thick mode in versions prior to 3.0, and is the default in python-oracledb 3.0 and later.

Setting oracledb.defaults.thick_mode_dsn_passthrough to False makes Thick mode use the same heuristics as Thin mode regarding connection string parameter handling and reading any optional tnsnames.ora configuration file.

Files such as sqlnet.ora and oraaccess.xml are only used by Thick mode. They are always located and read by Oracle Client libraries regardless of the oracledb.defaults.thick_mode_dsn_passthrough value. The directory search heuristic is determined by the Oracle Client libraries at the time oracledb.init_oracle_client() is called, as shown above.

The oracledb.defaults.thick_mode_dsn_passthrough value is ignored in Thin mode.

3.4. Oracle Environment Variables for python-oracledb

Some common environment variables that influence python-oracledb are shown below. The variables that may be needed depend on how Python is installed, how you connect to the database, and what optional settings are desired. It is recommended to set Oracle variables in the environment before calling Python. However, they may also be set in the application with os.putenv() before the first connection is established.

Note

System environment variables such as LD_LIBRARY_PATH must be set before Python starts.

The common environment variables listed below are supported in python-oracledb.

Table 3.1 Common Oracle environment variables supported by python-oracledb
Oracle Environment Variable	Purpose	Python-oracledb Mode
LD_LIBRARY_PATH	The library search path for platforms like Linux should include the Oracle libraries, for example `$ORACLE_HOME/lib` or `/opt/instantclient_23_5`. This variable is not needed if the libraries are located by an alternative method, such as with `ldconfig`. On other UNIX platforms, you may need to set an OS specific equivalent such as `LIBPATH` or `SHLIB_PATH`.	Thick
NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT	Often set in Python applications to force a consistent date format independent of the locale. These variables are ignored if the environment variable `NLS_LANG` is not set.	Thick
NLS_LANG	Determines the ‘national language support’ globalization options for python-oracledb. Note that from cx_Oracle 8, the character set component is ignored and only the language and territory components of `NLS_LANG` are used. The character set can instead be specified during connection or connection pool creation. See Character Sets and Globalization.	Thick
ORA_SDTZ	The default session time zone.	Both
ORA_TZFILE	The name of the Oracle time zone file to use. See Time Zone Files.	Thick
ORACLE_HOME	The directory containing the Oracle Database software. The directory and various configuration files must be readable by the Python process. This variable should not be set if you are using Oracle Instant Client.	Thick
PATH	The library search path for Windows should include the location where `OCI.DLL` is found. This variable is not needed if you set `lib_dir` in a call to `oracledb.init_oracle_client()`.	Thick
TNS_ADMIN	The directory of optional Oracle Client configuration files such as `tnsnames.ora` and `sqlnet.ora`. Generally not needed if the configuration files are in a default location, or if `config_dir` was not used in `oracledb.init_oracle_client()`. See Optional Oracle Net Configuration Files.	Both

3.5. Other python-oracledb Thick Mode Initialization

The oracledb.init_oracle_client() function allows driver_name and error_url parameters to be set. These are useful for applications whose end-users are not aware that python-oracledb is being used. An example of setting the parameters is:

oracledb.init_oracle_client(driver_name="My Great App : 3.1.4",
                            error_url="https://example.com/MyInstallInstructions.html")

The convention for driver_name is to separate the product name from the product version by a colon and single blank characters. The value will be shown in Oracle Database views like V$SESSION_CONNECT_INFO. If this parameter is not specified, then the value specified in the oracledb.defaults.driver_name attribute is used. If the value of this attribute is None, then a value like python-oracledb thk : 3.0.0 is shown, see Finding the python-oracledb Mode.

The error_url string will be shown in the exception raised if init_oracle_client() cannot load the Oracle Client libraries. This allows applications that use python-oracledb in Thick mode to refer users to application-specific installation instructions. If this value is not specified, then the Installing python-oracledb URL is used.

3.6. Migrating from python-oracledb Thick Mode to python-oracledb Thin Mode

Changing an application that currently uses Thick mode to use Thin mode requires the removal of calls to oracledb.init_oracle_client() and an application restart. Other small changes may be required:

Remove all calls to oracledb.init_oracle_client() from the application.
Review Appendix A: Oracle Database Features Supported by python-oracledb and Appendix B: Differences between python-oracledb Thin and Thick Modes for code changes that may be needed.
Restart your application.
Test and validate your application behavior.

When you are satisfied, you can optionally remove Oracle Client libraries. For example, delete your Oracle Instant Client directory.

You can validate the python-oracledb mode by checking Connection.thin, ConnectionPool.thin, or by querying the CLIENT_DRIVER column of V$SESSION_CONNECT_INFO and verifying if the value of the column begins with the text python-oracledb thn. See Finding the python-oracledb Mode.

Note all connections in a python-oracledb application must use the same mode.