3.Initializing python-oracledb

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

All connections in an application use the same mode. SeeFinding the python-oracledb Modeto verify which mode is in use.

If you are upgrading from the obsolete cx_Oracle driver to python-oracledb,then refer toUpgrading 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 yourdatabase. These need to be installed separately, seeInstalling python-oracledb.
Oracle Client libraries from one of the following can be used:

AnOracle Instant Client Basicor Basic Light package. This is generally the easiest if you do not alreadyhave Oracle software installed.
A full Oracle Client installation (installed by running the OracleUniversal installerrunInstaller)
An Oracle Database installation, if Python is running on the samemachine as the database
The Client library version does not always have to match the Oracle Databaseversion.

Your applicationmust call the functionoracledb.init_oracle_client() to load the client libraries. Forexample, if the Oracle Instant Client libraries are inC:\oracle\instantclient_23_5 on Windows or$HOME/Downloads/instantclient_23_3 on macOS, then you can use:

importosimportplatformimportoracledbd=None# On Linux, no directory should be passedifplatform.system()=="Darwin":# macOSd=os.environ.get("HOME")+("/Downloads/instantclient_23_3")elifplatform.system()=="Windows":# Windowsd=r"C:\oracle\instantclient_23_5"oracledb.init_oracle_client(lib_dir=d)

The use of a ‘raw’ stringr"..." on Windows means that backslashes aretreated as directory separators. On Linux, the libraries must be in thesystem library search pathbefore the Python process starts, preferablyconfigured withldconfig.

More details and options are shown in the later sections:

Notes on calling init_oracle_client()

Theinit_oracle_client() function must be called beforeanystandalone connection orconnection pool is created. If a connection or poolis first created, then the Thick mode cannot be enabled.
If you callinit_oracle_client() with alib_dirparameter, the Oracle Client libraries are loaded immediately from thatdirectory. If you callinit_oracle_client() but donotset thelib_dir parameter, the Oracle Client libraries are loadedimmediately using the search heuristics discussed in later sections. Note ifyou setlib_dir on Linux and related platforms, you must still haveconfigured the system library search path to include that directorybeforestarting Python.
Once the Thick mode is enabled, you cannot go back to the Thin mode except byremoving calls toinit_oracle_client() and restarting theapplication.
If Oracle Client libraries cannot be loaded theninit_oracle_client() will raise an errorDPI-1047:OracleClientlibrarycannotbeloaded. To resolve this, review theplatform-specific instructions below or seeDPI-1047.Alternatively, remove the call toinit_oracle_client() anduse Thin mode. The features supported by Thin mode can be found inAppendix A: Oracle Database Features Supported by python-oracledb.
On any operating system, if you setlib_dir to the library directory of afull 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 youwill get errors likeORA-1804. You should set this variable, and otherOracle environment variables, before starting Python, as shown inOracleEnvironment Variables.
Theinit_oracle_client() function may be called multipletimes 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 thelib_dir parameter in a call toinit_oracle_client(), for example:
```
importoracledboracledb.init_oracle_client(lib_dir=r"C:\instantclient_23_5")
```
On Windows, when the path contains backslashes, use a ‘raw’ string liker"C:\instantclient_23_5".
This directory should contain the libraries from an unzippedInstant Client‘Basic’ or ‘Basic Light’ package. If you pass the library directory from afull client or database installation, such asOracle Database “XE” ExpressEdition,then you will need to have previously set your environment to use that samesoftware installation. Otherwise, files such as message files will not belocated and you may have library version clashes.
If the Oracle Client libraries cannot be loaded, then an exception israised.
Alternatively, you can callinit_oracle_client() withoutpassing alib_dir parameter:
```
importoracledboracledb.init_oracle_client()
```
In this case, Oracle Client libraries are first looked for in the directorywhere the python-oracledb binary module is installed. This directory shouldcontain the libraries from an unzippedInstant Client ‘Basic’ or ‘BasicLight’ package.
If the libraries are not found there, the search looks at the directorieson the system library search path, for example, thePATH environmentvariable.
If the Oracle Client libraries cannot be loaded, then an exception israised.

3.1.2.Enabling python-oracledb Thick Mode on macOS

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

By passing thelib_dir parameter in a call toinit_oracle_client(), for example:
```
importoracledboracledb.init_oracle_client(lib_dir="/Users/your_username/Downloads/instantclient_23_3")
```
This directory should contain the libraries from an unzippedInstant Client‘Basic’ or ‘Basic Light’ package.
Alternatively, you can callinit_oracle_client() withoutpassing alib_dir parameter:
```
importoracledboracledb.init_oracle_client()
```
In this case, the Oracle Client libraries are first looked for in thedirectory where the python-oracledb Thick mode binary module is installed.This directory should contain the libraries from an unzippedInstant Client‘Basic’ or ‘Basic Light’package, or a symbolic link to the main Oracle Client library if InstantClient is in a different directory.
You can find the directory containing the Thick mode binary module by callingthe python CLI without specifying a Python script, executingimportoracledb, and then typingoracledb at the prompt. For example thismight 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/oracledbcontains the binary modulethick_impl.cpython-39-darwin.so you could thenrun these commands in a terminal window:
```
CLIENT_DIR=~/Downloads/instantclient_23_3DPY_DIR=~/.pyenv/versions/3.9.6/lib/python3.9/site-packages/oracledbln -s $CLIENT_DIR/libclntsh.dylib $DPY_DIR
```
This can be automated in Python with:
```
CLIENT_DIR="~/Downloads/instantclient_23_3"LIB_NAME="libclntsh.dylib"importosimportoracledbtarget_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 pathswill vary with macOS version and Python version. Any value inDYLD_LIBRARY_PATH will not propagate to a sub-shell, so do not rely onsetting it.
If the Oracle Client libraries cannot be loaded, then an exception israised.

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

3.1.3.Enabling python-oracledb Thick Mode on Linux and Related Platforms

On Linux and related platforms, enable Thick mode by callinginit_oracle_client() without passing alib_dirparameter.

importoracledboracledb.init_oracle_client()

Oracle Client libraries are looked for in the operating system library searchpath, such as configured withldconfig or set in the environment variableLD_LIBRARY_PATH. This must be configuredprior to running the Pythonprocess. Web servers and other daemons commonly reset environment variables sousingldconfig is generally preferred instead. On some UNIX platforms anOS specific equivalent, such asLIBPATH orSHLIB_PATH, is used insteadofLD_LIBRARY_PATH.

If libraries are not found in the system library search path, then librariesin$ORACLE_HOME/lib will be used. Note that the environment variableORACLE_HOME should only ever be set when you have a full databaseinstallation or full client installation (such as installed with the OracleGUI installer). It should not be set if you are usingOracle Instant Client. Ifbeing used,ORACLE_HOME and other necessary Oracle environment variablesshould be set before starting Python. SeeOracle Environment Variables for python-oracledb.

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

On Linux, python-oracledb Thick mode will not automatically load Oracle Clientlibrary files from the directory where the python-oracledb binary module islocated. One of the above methods should be used instead.

Ensure that the Python process has directory and file access permissions forthe Oracle Client libraries. OS restrictions may prevent the opening of OracleClient libraries installed in unsafe paths, such as from a user directory. Youmay need to install the Oracle Client libraries under a directory like/optor/usr/local.

3.1.4.Tracing Oracle Client Library Loading

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

setDPI_DEBUG_LEVEL=64

On Linux and macOS, you might use:

exportDPI_DEBUG_LEVEL=64

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

3.2.Explicitly Enabling python-oracledb Thin Mode

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

To allow application portability, the driver’s internal logic allowsapplications to initially attemptstandalone connection creation in Thin mode, but then lets themenableThick mode if that connection is unsuccessful. An example iswhen trying to connect to an Oracle Database that turns out to be an oldversion that requires Thick mode. This heuristic means Thin mode is notenforced until the initial connection is successful. Since all connectionsmust be the same mode, any second and subsequent concurrent Thin modeconnection attempt will wait for the initial standalone connection to succeed,meaning the driver mode is no longer potentially changeable to Thick mode, thusletting those additional connections be established in Thin mode.

If you have multiple threads concurrently creating standalone Thin modeconnections, you may wish to calloracledb.enable_thin_mode() as partof your application initialization. This is not required but avoids the modedetermination delay.

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

Single-threaded applications usingstandalone connections.
Single or multi-threaded applications usingconnection pools (even withmin of 0).

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

To explicitly enable Thin mode, callenable_thin_mode(), forexample:

importoracledboracledb.enable_thin_mode()

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

DPY-2019:python-oracledbthickmodecannotbeusedbecausethinmodehasalreadybeenenabledorathinmodeconnectionhasalreadybeencreated

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

DPY-2053:python-oracledbthinmodecannotbeusedbecausethickmodehasalreadybeenenabled

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 creatingconnection pools. These files affect connection behavior. The common files are:

tnsnames.ora: A configuration file that defines databases aliases andtheir related connection configuration information used for establishingconnections. SeeTNS Aliases for Connection Strings.
sqlnet.ora: A configuration file that contains settings for features suchas connection failover, network encryption, logging, and tracing. Thesqlnet.ora file is only used in python-oracledb Thick mode. SeeEnabling python-oracledb Thick mode. In python-oracledb Thin mode, many of the equivalentsettings can be defined as connection time parameters.

SeeUsing Optional Oracle Configuration Files to understand how python-oracledb locates thefiles.

3.3.2.Optional Oracle Client Configuration File

When python-oracledb Thick mode uses Oracle Client libraries version 12.1 orlater, an optional client parameter file calledoraaccess.xml can be usedto configure some behaviors of those libraries, such as statement caching andprefetching. This can be useful to change application behavior if theapplication code cannot be altered.

A sampleoraaccess.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>

Theoraaccess.xml file has other uses including:

Changing the value of Fast Application NotificationFAN eventswhich affects notifications and Runtime Load Balancing (RLB).
ConfiguringClient Result Caching parameters.
Turning onClient Statement Cache Auto-tuning.

Refer to the documentation onoraaccess.xmlfor more details.

SeeUsing Optional Oracle Configuration Files to understand how python-oracledb locates thefile.

For another way to set some python-oracledb behaviors without changingapplication code, seePython-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 astnsnames.ora,sqlnet.ora, ororaaccess.xml to configure your connections, then putthe files in a directory accessible to python-oracledb and follow steps shownbelow.

Note that theEasy Connect syntax can set many commonconfiguration options without needingtnsnames.ora,sqlnet.ora, ororaaccess.xml files.

Locating tnsnames.ora in python-oracledb Thin mode

Python-oracledb will read atnsnames.ora file when aTNS Alias is used for thedsn parameter oforacledb.connect(),oracledb.create_pool(),oracledb.connect_async(), ororacledb.create_pool_async(). Only onetnsnames.ora file isread. If the TNS Alias is not found in that file, then connection will fail.Thin mode does not read other configuration files such assqlnet.ora ororaaccess.xml.

In python-oracledb Thin mode, you should explicitly specify the directorybecause 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 locatetnsnames.ora is determined as follows (firstone wins):

the value of the method parameterconfig_dir

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

the value in theconfig_dir attribute of the method parameterparams

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

the value ofdefaults.config_dir, which may have been set explicitlyto 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 whenoracledb.defaults.thick_mode_dsn_passthrough isFalse.

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

In python-oracledb Thick mode, the directory containing the optional OracleClient configuration files such astnsnames.ora,sqlnet.ora, andoraaccess.xml can be explicitly specified, otherwise the Oracle Clientlibraries will use a heuristic to locate the directory.

Iforacledb.defaults.thick_mode_dsn_passthrough isFalse, then thefollowing applies to all files excepttnsnames.ora.

The configuration file directory is determined as follows:

From theconfig_dir parameter 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 liker"C:\instantclient_23_5".
Ifinit_oracle_client() is called to enable Thick mode butconfig_dir is not specified, then default directories are searched forthe configuration files. This is platform specific and controlled by OracleClient. Directories include:
- Your home directory, using$HOME/.tnsnames.ora and$HOME/.sqlnet.ora
- The directory/var/opt/oracle on Solaris, and/etc on other UNIXplatforms.
- The directory specified by theTNS_ADMINenvironment variable.
- For Oracle Instant Client ZIP files, thenetwork/admin subdirectory ofInstant Client, for example/opt/oracle/instantclient_23_5/network/admin.
- For Oracle Instant Client RPMs, thenetwork/admin subdirectory ofInstant Client, for example/usr/lib/oracle/23.5/client64/lib/network/admin.
- When using libraries from a local Oracle Database or full clientinstallation, in$ORACLE_HOME/network/admin or$ORACLE_BASE_HOME/network/admin.

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

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

The documentationNetwork Configuration has additionalinformation about some specific Oracle Net configuration useful forapplications.

Setting thick_mode_dsn_passthrough

Whenoracledb.defaults.thick_mode_dsn_passthrough isTrue,it is the Oracle Client libraries that locate and read any optionaltnsnames.ora configuration. This was always the behavior of python-oracledbThick mode in versions prior to 3.0, and is the default in python-oracledb 3.0and later.

Settingoracledb.defaults.thick_mode_dsn_passthrough toFalse makes Thick mode use the same heuristics as Thin mode regardingconnection string parameter handling and reading any optionaltnsnames.oraconfiguration file.

Files such assqlnet.ora andoraaccess.xml are only used by Thickmode. They are always located and read by Oracle Client libraries regardless oftheoracledb.defaults.thick_mode_dsn_passthrough value. Thedirectory search heuristic is determined by the Oracle Client libraries at thetimeoracledb.init_oracle_client() is called, as shown above.

Theoracledb.defaults.thick_mode_dsn_passthrough value isignored in Thin mode.

3.4.Oracle Environment Variables for python-oracledb

Some common environment variables that influence python-oracledb are shownbelow. The variables that may be needed depend on how Python is installed, howyou connect to the database, and what optional settings are desired. It isrecommended to set Oracle variables in the environment before calling Python.However, they may also be set in the application withos.putenv() before thefirst connection is established.

Note

System environment variables such asLD_LIBRARY_PATH must be set beforePython starts.

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

Table 3.1Common 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. SeeCharacter Sets and Globalization.	Thick
ORA_SDTZ	The default session time zone.	Both
ORA_TZFILE	The name of the Oracle time zone file to use. SeeTime 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()`. SeeOptional Oracle Net Configuration Files.	Both

3.5.Other python-oracledb Thick Mode Initialization

Theoracledb.init_oracle_client() function allowsdriver_name anderror_url parameters to be set. These are useful for applications whoseend-users are not aware that python-oracledb is being used. An example ofsetting the parameters is:

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

The convention fordriver_name is to separate the product name from theproduct version by a colon and single blank characters. The value will beshown in Oracle Database views like V$SESSION_CONNECT_INFO. If thisparameter is not specified, then the value specified in theoracledb.defaults.driver_name attribute is used.If the value of this attribute is None, then a value likepython-oracledbthk:3.0.0 is shown, seeFinding the python-oracledb Mode.

Theerror_url string will be shown in the exception raised ifinit_oracle_client() cannot load the Oracle Client libraries. This allowsapplications that use python-oracledb in Thick mode to refer users toapplication-specific installation instructions. If this value is notspecified, then theInstalling python-oracledb URL is used.

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

Changing an application that currently usesThick modeto use Thin mode requires the removal of calls tooracledb.init_oracle_client() and an application restart. Other smallchanges may be required:

Removeall calls tooracledb.init_oracle_client() from theapplication.
ReviewAppendix A: Oracle Database Features Supported by python-oracledb andAppendix B: Differences between python-oracledb Thin and Thick Modes for code changes thatmay be needed.
Restart your application.
Test and validate your application behavior.

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

You can validate the python-oracledb mode by checkingConnection.thin,ConnectionPool.thin, or by querying the CLIENT_DRIVER column ofV$SESSION_CONNECT_INFO and verifying if the value of the column begins with thetextpython-oracledbthn. SeeFinding the python-oracledb Mode.

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

Movatterモバイル変換