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.
3.1. Enabling python-oracledb Thick mode
If you are upgrading a cx_Oracle application to python-oracledb, then refer to Upgrading from cx_Oracle 8.3 to python-oracledb for changes that may be needed.
To change from the default Thin mode to the Thick mode:
Oracle Client libraries must be available to handle communication to your database. These need to be installed separately, see Installing python-oracledb.
Various versions of Oracle Client libraries can be used. They do not have to match the version of Oracle Database. Python-oracledb can use the Client Libraries from:
an installation of Oracle Instant Client
or a full Oracle Client installation (installed by running the Oracle Universal installer
runInstaller
)or an Oracle Database installation, if Python is running on the same machine as the database
Your application must call the function
oracledb.init_oracle_client()
. For example, if the Oracle Instant Client libraries are inC:\oracle\instantclient_19_17
on Windows or$HOME/Downloads/instantclient_19_8
on macOS (Intel x86), then you can use:import os import platform import oracledb d = None # default suitable for Linux if platform.system() == "Darwin" and platform.machine() == "x86_64": # macOS d = os.environ.get("HOME")+("/Downloads/instantclient_19_8") elif platform.system() == "Windows": d = r"C:\oracle\instantclient_19_18" oracledb.init_oracle_client(lib_dir=d)
The use of a ‘raw’ string
r"..."
on Windows means that backslashes are treated as directory separators.More details and options are shown in the later sections Enabling python-oracledb Thick Mode on Windows, Enabling python-oracledb Thick Mode on macOS, and Enabling python-oracledb Thick Mode on Linux and Related Platforms.
All connections in an application use the same mode.
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.
See Finding the python-oracledb Mode to verify which mode is in use.
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 alib_dir
parameter, the Oracle Client libraries are loaded immediately from that directory. If you callinit_oracle_client()
but do not set thelib_dir
parameter, the Oracle Client libraries are loaded immediately using the search heuristics discussed in later sections.If Oracle Client libraries cannot be loaded then
init_oracle_client()
will raise an errorDPI-1047: Oracle Client library cannot be loaded
. To resolve this, review the platform-specific instructions below or see Error Messages. Alternatively, remove the call toinit_oracle_client()
and use Thin mode. The features supported by Thin mode can be found in Appendix B: Differences between python-oracledb Thin and Thick Modes.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.On any operating system, if you set
lib_dir
to the library directory of a full database or full client installation (such as from runningrunInstaller
), you will need to have previously set the Oracle environment, for example by setting theORACLE_HOME
environment variable. Otherwise you will get errors likeORA-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 toinit_oracle_client()
, for example:import oracledb oracledb.init_oracle_client(lib_dir=r"C:\instantclient_19_18")
On Windows, when the path contains backslashes, use a ‘raw’ string like
r"C:\instantclient_19_18"
.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 alib_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 toinit_oracle_client()
, for example:import oracledb oracledb.init_oracle_client(lib_dir="/Users/your_username/Downloads/instantclient_19_8")
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 alib_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 typingoracledb
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 modulethick_impl.cpython-39-darwin.so
you could then run these commands in a terminal window:CLIENT_DIR=~/Downloads/instantclient_19_8 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_19_8" 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 inDYLD_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 Libraries 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. Optional Oracle Configuration Files
3.2.1. Optional Oracle Net Configuration Files
Optional Oracle Net configuration files may be read by python-oracledb. These files affect connections and applications. The common files are:
tnsnames.ora
: A configuration file that defines databases addresses for establishing connections. See Net Service Name for Connection Strings.sqlnet.ora
: A profile configuration file that may contain information on features such as connection failover, network encryption, logging, and tracing. The files should be in a directory accessible to Python, not on the database server host. See Oracle Net Services Reference for more information.The
sqlnet.ora
file is only used in the python-oracledb Thick mode. See Enabling python-oracledb Thick mode. In the python-oracledb Thin mode, many of the equivalent settings can be defined as connection time parameters, for example by using the ConnectParams Class.
See Using Optional Oracle Configuration Files to understand how python-oracledb locates the files.
3.2.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 if the application cannot be altered. The
file is read from the same directory as the Optional Oracle Net Configuration
Files.
Note
The oraaccess.xml
file is only used in the python-oracledb Thick mode.
See Enabling python-oracledb Thick mode.
A sample oraaccess.xml
file that sets the Oracle client ‘prefetch’ value to
1000 rows. This value affects every SQL query in the application:
<?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>
See Tuning Fetch Performance for information about prefetching.
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 files.
3.2.3. Using Optional Oracle Configuration Files
If you use optional Oracle configuration files such as tnsnames.ora
,
sqlnet.ora
or oraaccess.xml
, then put the files in an accessible
directory and follow the Thin or Thick mode instructions below.
The files should be in a directory accessible to Python, not on the database server host.
For python-oracledb Thin mode
In python-oracledb Thin mode, you must specify the directory that contains the
tnsnames.ora
file by either:
Setting the TNS_ADMIN environment variable to the directory containing the file
Or setting
defaults.config_dir
to the directory containing the file. For example:import oracledb oracledb.defaults.config_dir = "/opt/oracle/config"
Or setting the
config_dir
parameter to the directory containing the file whenconnecting
or creating aconnection pool
. For example:connection = oracledb.connect(user="hr", password=userpwd, dsn="orclpdb", config_dir="/opt/oracle/config")
On Windows, when the path contains backslashes, use a ‘raw’ string like
r"C:\instantclient_19_18"
.
Note
In Thin mode, you must explicitly set the directory because traditional
“default” locations such as the Instant Client network/admin/
subdirectory, or $ORACLE_HOME/network/admin/
, or
$ORACLE_BASE/homes/XYZ/network/admin/
(in a read-only Oracle Database
home) are not automatically looked in.
For python-oracledb Thick mode
In python-oracledb Thick mode, the directory containing the optional files can be explicitly specified or a default location will be used. Do one of:
Set the
config_dir
parameter to the directory containing the files in theoracledb.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_19_18"
.
Note
In python-oracledb Thick mode, once an application has created its first connection, trying to change the configuration directory will not have any effect.
If
init_oracle_client()
is called to enable Thick mode butconfig_dir
is not specified, then default directories are searched for the configuration files. They include: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_19_18/network/admin
.For Oracle Instant RPMs, the
network/admin
subdirectory of Instant Client, for example/usr/lib/oracle/19.18/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
.
Note that the Easy Connect Syntax for Connection Strings can set many common configuration options
without needing tnsnames.ora
or sqlnet.ora
files.
The section Network Configuration has additional information about Oracle Net configuration.
3.3. Oracle Environment Variables for python-oracledb Thick Mode
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. System environment variables like
LD_LIBRARY_PATH
must be set before Python starts.
Note
The variables listed below are only supported in the python-oracledb Thick
mode, with the exception of TNS_ADMIN
and ORA_SDTZ
which are also
supported in the python-oracledb Thin mode.
Oracle Environment Variables |
Purpose |
---|---|
LD_LIBRARY_PATH |
The library search path for platforms like Linux should include the
Oracle libraries, for example |
PATH |
The library search path for Windows should include the location where
|
TNS_ADMIN |
The directory of optional Oracle Client configuration files such as
|
ORA_SDTZ |
The default session time zone. |
ORA_TZFILE |
The name of the Oracle time zone file to use. See below. |
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. |
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_DATE_FORMAT, NLS_TIMESTAMP_FORMAT |
Often set in Python applications to force a consistent date format
independent of the locale. The variables are ignored if the environment
variable |
Oracle Instant Client includes a small and big time zone file, for example
timezone_32.dat
and timezlrg_32.dat
. The versions can be shown by running
the utility genezi -v
located in the Instant Client directory. The small file
contains only the most commonly used time zones. By default, the larger
timezlrg_n.dat
file is used. If you want to use the smaller timezone_n.dat
file, then set the ORA_TZFILE
environment variable to the name of the file
without any directory prefix. For example export ORA_TZFILE=timezone_32.dat
.
With Oracle Instant Client 12.2 or later, you can also use an external time zone
file. Create a subdirectory oracore/zoneinfo
under the Instant Client
directory, and move the file into it. Then set ORA_TZFILE
to the file name,
without any directory prefix. The genezi -v
utility will show the time zone
file in use.
If python-oracledb Thick mode is using Oracle Client libraries from an Oracle
Database or full Oracle Client software installation (such as installed with
Oracle’s GUI installer), and you want to use a non-default time zone file, then
set ORA_TZFILE
to the file name with a directory prefix. For example:
export ORA_TZFILE=/opt/oracle/myconfig/timezone_31.dat
.
The Oracle Database documentation contains more information about time zone files, see Choosing a Time Zone File.
3.4. 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 a value like python-oracledb thk : 1.2.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.5. Changing from python-oracledb Thick Mode to python-oracledb Thin Mode
Changing an application that currently uses Thin mode requires the removal of
calls to oracledb.init_oracle_client()
and an application restart.
Other small changes may be required.
All connections in a python-oracledb application must use the same mode.
If you have been using python-oracledb in Thick mode, you can use Thin mode by:
Reviewing 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. Also read Toggling between Drivers.
Removing all calls to
oracledb.init_oracle_client()
from the application.Make other necessary changes identified in step 1.
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 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.