2. Installing python-oracledb

The python-oracledb driver allows Python 3 applications to connect to Oracle Database.

Python-oracledb is the new name for the Python cx_Oracle driver. If you are upgrading from cx_Oracle, see Upgrading from cx_Oracle 8.3 to python-oracledb.

Fig. 2.1 Architecture of the python-oracledb driver

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.

2.1. Quick Start python-oracledb Installation

This section contains the steps needed to install python-oracledb.

1. Install Python 3 if it is not already available. Use any version from Python 3.7 through 3.12.

2. Install python-oracledb from PyPI, for example:

python -m pip install oracledb --upgrade

On some platforms the binary may be called python3 instead of python.

If a python-oracledb binary package is not available for your platform, the source package will be downloaded. This will be compiled and the resulting binary installed.

The --user option may be useful if you do not have permission to write to system directories:

python -m pip install oracledb --upgrade --user

If you are behind a proxy, use the --proxy option. For example:

python -m pip install oracledb --upgrade --proxy=http://proxy.example.com:80

By default, python-oracledb connects directly to Oracle Database. This lets it be used immediately without needing any additional installation of Oracle Client libraries.

3. Create a file test.py such as:

import getpass

import oracledb

un = 'scott'
cs = 'localhost/orclpdb'
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        sql = """select sysdate from dual"""
        for r in cursor.execute(sql):
            print(r)

4. Edit test.py and set the un and cs variables to your own database username and the database connection string, respectively.

   A simple connection to the database requires an Oracle Database user name and password and a database connection string. For python-oracledb, a common connection string format is hostname:port/servicename, using the host name where the database is running, the Oracle Database service name of the database instance, and the port that the database is using. If the default port 1521 is being used, then this component of the connection string is often omitted.

   The database can be on-premises or in the Cloud. The python-oracledb driver does not include a database.

5. Run the program as shown below:

   python test.py
   

   Enter the database password when prompted and the queried date will be shown, for example:

   Enter password for cj@localhost/orclpdb: xxxxxxxxxx
   (datetime.datetime(2023, 9, 21, 8, 24, 4),)
   

If you run into installation trouble, refer to detailed instructions below, or see Troubleshooting Errors.

You can learn more about python-oracledb from the python-oracledb documentation and samples.

2.2. Supported Oracle Database Versions

When python-oracledb is used in the default Thin mode, it connects directly to the Oracle Database and does not require Oracle Client libraries. Connections in this mode can be made to Oracle Database 12.1 or later.

To connect to older Oracle Database releases you must have Oracle Client libraries installed, and enable python-oracledb’s Thick mode. Connections in this mode can be made to Oracle Database 9.2, or later, depending on the Oracle Client library version.

In python-oracledb Thick mode, Oracle Database’s standard client-server network interoperability allows connections between different versions of Oracle Client libraries and Oracle Database. For current or previously certified configurations, see Oracle Support’s Doc ID 207303.1. In summary:

• Oracle Client 21 can connect to Oracle Database 12.1 or later

• Oracle Client 19, 18 and 12.2 can connect to Oracle Database 11.2 or later

• Oracle Client 12.1 can connect to Oracle Database 10.2 or later

• Oracle Client 11.2 can connect to Oracle Database 9.2 or later

The technical restrictions on creating connections may be more flexible. For example, Oracle Client 12.2 can successfully connect to Oracle Database 10.2.

The python-oracledb attribute Connection.thin can be used to see what mode a connection is in. In the Thick mode, the function oracledb.clientversion() can be used to determine which Oracle Client version is in use. The attribute Connection.version can be used to determine which Oracle Database version a connection is accessing. These can then be used to adjust the application behavior accordingly. Any attempt to use Oracle features that are not supported by a particular mode or client library/database combination will result in runtime errors.

2.3. Installation Requirements

To use python-oracledb, you need:

• Python 3.7, 3.8, 3.9, 3.10, 3.11 or 3.12

• The Python cryptography package. This package is automatically installed as a dependency of python-oracledb. It is strongly recommended that you keep the cryptography package up to date whenever new versions are released. If the cryptography package is not available, you can still install python-oracledb but can only use it in Thick mode, see Installing python-oracledb without the Cryptography Package.

• Optionally, Oracle Client libraries can be installed to enable some additional advanced functionality. These can be from the free Oracle Instant Client, from a full Oracle Client installation (such as installed by Oracle’s GUI installer), or from those included in Oracle Database if Python is on the same machine as the database. Oracle Client libraries versions 21, 19, 18, 12, and 11.2 are supported where available on Linux, Windows and macOS (Intel x86). Oracle’s standard client-server version interoperability allows connection to both older and newer databases.

• An Oracle Database either local or remote, on-premises or in the Cloud.

2.4. Installing python-oracledb on Linux

This section discusses the generic installation methods on Linux.

2.4.1. Install python-oracledb

The generic way to install python-oracledb on Linux is to use Python’s pip package to install from Python’s package repository PyPI:

python -m pip install oracledb --upgrade

This will download and install a pre-compiled binary from PyPI if one is available for your architecture. Otherwise, the source will be downloaded, compiled, and the resulting binary installed. Compiling python-oracledb requires the Python.h header file. If you are using the default python package, this file is in the python-devel package or equivalent.

On Oracle Linux 8, to use the default Python 3.6 installation, install with:

python3 -m pip install oracledb --upgrade --user

The --user option is useful when you do not have permission to write to system directories.

Other versions of Python can be used on Oracle Linux, see Python for Oracle Linux.

If you are behind a proxy, use the --proxy option. For example:

python -m pip install oracledb --upgrade --proxy=http://proxy.example.com:80

2.4.2. Optionally Install Oracle Client

By default, python-oracledb runs in a Thin mode which connects directly to Oracle Database so no further installation steps are required. However, to use additional features available in Thick mode you need Oracle Client libraries installed. Oracle Client versions 21, 19, 18, 12 and 11.2 are supported.

• If your database is on a remote computer, then download the free Oracle Instant Client “Basic” or “Basic Light” package for your operating system architecture.

• Alternatively, use the client libraries already available in a locally installed database such as the free Oracle Database 23c Free release.

To use python-oracledb in Thick mode you must call oracledb.init_oracle_client() in your application, see Enabling python-oracledb Thick mode. For example:

import oracledb

oracledb.init_oracle_client()

On Linux, do not pass the lib_dir parameter in the call: the Oracle Client libraries on Linux must be in the system library search path before the Python process starts.

2.4.2.1. Oracle Instant Client Zip Files

To use python-oracledb Thick mode with Oracle Instant Client zip files:

1. Download an Oracle 21, 19, 18, 12, or 11.2 “Basic” or “Basic Light” zip file matching your Python 64-bit or 32-bit architecture:

• x86-64 64-bit

• x86 32-bit

• ARM (aarch64) 64-bit

Oracle Database 19c is a Long Term Support Release whereas Oracle Database 21c is an Innovation Release. It is recommended to keep up to date with the latest Oracle Instant Client release updates of your desired major version.

Oracle Instant Client 19c will connect to Oracle Database 11.2 or later. Oracle Instant Client 21c will connect to Oracle Database 12.1 or later.

2. Unzip the package into a single directory that is accessible to your application. For example:

   mkdir -p /opt/oracle
   cd /opt/oracle
   unzip instantclient-basic-linux.x64-21.6.0.0.0.zip
   

   Note OS restrictions may prevent the opening of Oracle Client libraries installed in unsafe paths, such as from a user directory. You may need to install under a directory like /opt or /usr/local.

3. Install the libaio package with sudo or as the root user. For example:

   sudo yum install libaio
   

   On some Linux distributions this package is called libaio1 instead.

   On recent Linux versions such as Oracle Linux 8, you may also need to install the libnsl package when using Oracle Instant Client 19.

4. If there is no other Oracle software on the machine that will be impacted, permanently add Instant Client to the runtime link path. For example, with sudo or as the root user:

   sudo sh -c "echo /opt/oracle/instantclient_21_6 > /etc/ld.so.conf.d/oracle-instantclient.conf"
   sudo ldconfig
   

   Alternatively, set the environment variable LD_LIBRARY_PATH to the appropriate directory for the Instant Client version. For example:

   export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_6:$LD_LIBRARY_PATH
   

Make sure this is set in each shell that invokes Python. Web servers and other daemons commonly reset environment variables so using ldconfig is generally preferred instead.

5. If you use optional Oracle configuration files such as tnsnames.ora, sqlnet.ora, or oraaccess.xml with Instant Client, then put the files in an accessible directory, for example in /opt/oracle/your_config_dir. Then use:

   import oracledb
   
   oracledb.init_oracle_client(config_dir="/home/your_username/oracle/your_config_dir")
   

   or set the environment variable TNS_ADMIN to that directory name.

   Alternatively, put the files in the network/admin subdirectory of Instant Client, for example in /opt/oracle/instantclient_21_6/network/admin. This is the default Oracle configuration directory for executables linked with this Instant Client.

6. Call oracledb.init_oracle_client() in your application, if it is not already used.

2.4.2.2. Oracle Instant Client RPMs

To use python-oracledb with Oracle Instant Client RPMs:

1. Download an Oracle 21, 19, 18, 12, or 11.2 “Basic” or “Basic Light” RPM matching your Python architecture:

• x86-64 64-bit

• x86 32-bit

• ARM (aarch64) 64-bit

Oracle’s yum server has convenient repositories:

• Instant Client 21 RPMs for Oracle Linux x86-64 8, Older Instant Client RPMs for Oracle Linux x86-64 8

• Instant Client 21 RPMs for Oracle Linux x86-64 7, Older Instant Client RPMs for Oracle Linux x86-64 7

• Instant Client RPMs for Oracle Linux x86-64 6

• Instant Client RPMs for Oracle Linux ARM (aarch64) 8

• Instant Client RPMs for Oracle Linux ARM (aarch64) 7

Oracle Database 19c is a Long Term Support Release whereas Oracle Database 21c is an Innovation Release. It is recommended to keep up to date with the latest Oracle Instant Client release updates of your desired major version.

Oracle Instant Client 19c will connect to Oracle Database 11.2 or later. Oracle Instant Client 21c will connect to Oracle Database 12.1 or later.

2. Install the downloaded RPM with sudo or as the root user. For example:

   sudo yum install oracle-instantclient-basic-21.6.0.0.0-1.x86_64.rpm
   

   Yum will automatically install required dependencies, such as libaio.

   On recent Linux versions such as Oracle Linux 8, you may need to manually install the libnsl package when using Oracle Instant Client 19.

3. For Instant Client 19 or later, the system library search path is automatically configured during installation.

   For older versions, if there is no other Oracle software on the machine that will be impacted, permanently add Instant Client to the runtime link path. For example, with sudo or as the root user:

   sudo sh -c "echo /usr/lib/oracle/18.5/client64/lib > /etc/ld.so.conf.d/oracle-instantclient.conf"
   sudo ldconfig
   

   Alternatively, for version 18 and earlier, every shell running Python will need to have the environment variable LD_LIBRARY_PATH set to the appropriate directory for the Instant Client version. For example:

   export LD_LIBRARY_PATH=/usr/lib/oracle/18.5/client64/lib:$LD_LIBRARY_PATH
   

Web servers and other daemons commonly reset environment variables so using ldconfig is generally preferred instead.

4. If you use optional Oracle configuration files such as tnsnames.ora, sqlnet.ora or oraaccess.xml with Instant Client, then put the files in an accessible directory, for example in /opt/oracle/your_config_dir. Then use:

   import oracledb
   
   oracledb.init_oracle_client(config_dir="/opt/oracle/your_config_dir")
   

   or set the environment variable TNS_ADMIN to that directory name.

   Alternatively, put the files in the network/admin subdirectory of Instant Client, for example in /usr/lib/oracle/21/client64/lib/network/admin. This is the default Oracle configuration directory for executables linked with this Instant Client.

5. Call oracledb.init_oracle_client() in your application, if it is not already used.

2.4.2.3. Local Database or Full Oracle Client

Python-oracledb applications can use Oracle Client 21, 19, 18, 12, or 11.2 libraries from a local Oracle Database or full Oracle Client installation (such as installed by Oracle’s GUI installer).

The libraries must be either 32-bit or 64-bit, matching your Python architecture.

1. Set required Oracle environment variables by running the Oracle environment script. For example:

   source /usr/local/bin/oraenv
   

   For Oracle Database Express Edition (“XE”) 11.2, run:

   source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
   

2. Optional Oracle configuration files such as tnsnames.ora, sqlnet.ora, or oraaccess.xml can be placed in $ORACLE_HOME/network/admin.

   Alternatively, Oracle configuration files can be put in another, accessible directory. Then set the environment variable TNS_ADMIN to that directory name.

3. Call oracledb.init_oracle_client() in your application, if it is not already used.

2.5. Installing python-oracledb on Windows

2.5.1. Install python-oracledb

Use Python’s pip package to install python-oracledb from Python’s package repository PyPI:

python -m pip install oracledb --upgrade

If you are behind a proxy, use the --proxy option. For example:

python -m pip install oracledb --upgrade --proxy=http://proxy.example.com:80

This will download and install a pre-compiled binary if one is available for your architecture. If a pre-compiled binary is not available, the source will be downloaded, compiled, and the resulting binary installed.

2.5.2. Optionally Install Oracle Client

By default, python-oracledb runs in a Thin mode which connects directly to Oracle Database so no further installation steps are required. However, to use additional features available in Thick mode you need Oracle Client libraries installed. Oracle Client versions 21, 19, 18, 12, and 11.2 are supported.

• If your database is on a remote computer, then download the free Oracle Instant Client “Basic” or “Basic Light” package for your operating system architecture.

• Alternatively, use the client libraries already available in a locally installed database such as the free Oracle Database Express Edition (“XE”) release.

To use python-oracledb in Thick mode you must call oracledb.init_oracle_client() in your application, see Enabling python-oracledb Thick mode. For example:

import oracledb

oracledb.init_oracle_client()

On Windows, you may prefer to pass the lib_dir parameter in the call as shown below.

2.5.2.1. Oracle Instant Client Zip Files

To use python-oracledb in Thick mode with Oracle Instant Client zip files:

1. Download an Oracle 21, 19, 18, 12, or 11.2 “Basic” or “Basic Light” zip file: 64-bit or 32-bit, matching your Python architecture.

   The latest version is recommended. Oracle Instant Client 19 will connect to Oracle Database 11.2 or later.

   Windows 7 users: Note that Oracle 19c is not supported on Windows 7.

2. Unzip the package into a directory that is accessible to your application. For example unzip instantclient-basic-windows.x64-19.11.0.0.0dbru.zip to C:\oracle\instantclient_19_11.

3. Oracle Instant Client libraries require a Visual Studio redistributable with a 64-bit or 32-bit architecture to match Instant Client’s architecture. Each Instant Client version requires a different redistributable version:

• For Instant Client 21, install VS 2019 or later

• For Instant Client 19, install VS 2017

• For Instant Client 18 or 12.2, install VS 2013

• For Instant Client 12.1, install VS 2010

• For Instant Client 11.2, install VS 2005 64-bit

2.5.2.1.1. Configure Oracle Instant Client

1. There are several alternative ways to tell python-oracledb where your Oracle Client libraries are, see Initializing python-oracledb.

• With Oracle Instant Client you can use oracledb.init_oracle_client() in your application, for example:

  import oracledb
  
  oracledb.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_14")
  

  Note that a ‘raw’ string is used because backslashes occur in the path.

• Alternatively, add the Oracle Instant Client directory to the PATH environment variable. The directory must occur in PATH before any other Oracle directories. Restart any open command prompt windows.

  Update your application to call init_oracle_client(), which enables python-oracledb Thick mode:

  import oracledb
  
  oracledb.init_oracle_client()
  

• Another way to set PATH is to use a batch file that sets it before Python is executed, for example:

  REM mypy.bat
  SET PATH=C:\oracle\instantclient_19_14;%PATH%
  python %*
  

  Invoke this batch file every time you want to run Python.

  Update your application to call init_oracle_client(), which enables python-oracledb Thick mode:

  import oracledb
  
  oracledb.init_oracle_client()
  

2. If you use optional Oracle configuration files such as tnsnames.ora, sqlnet.ora, or oraaccess.xml with Instant Client, then put the files in an accessible directory, for example in C:\oracle\your_config_dir. Then use:

   import oracledb
   
   oracledb.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_14",
                               config_dir=r"C:\oracle\your_config_dir")
   

   or set the environment variable TNS_ADMIN to that directory name.

   Alternatively, put the files in a network\admin subdirectory of Instant Client, for example in C:\oracle\instantclient_19_11\network\admin. This is the default Oracle configuration directory for executables linked with this Instant Client.

2.5.2.2. Local Database or Full Oracle Client

Python-oracledb Thick mode applications can use Oracle Client 21, 19, 18, 12, or 11.2 libraries from a local Oracle Database or full Oracle Client (such as installed by Oracle’s GUI installer).

The Oracle libraries must be either 32-bit or 64-bit, matching your Python architecture.

1. Set the environment variable PATH to include the path that contains OCI.DLL, if it is not already set.

   Restart any open command prompt windows.

2. Optional Oracle configuration files such as tnsnames.ora, sqlnet.ora, or oraaccess.xml can be placed in the network\admin subdirectory of the Oracle Database software installation.

   Alternatively, pass config_dir to oracledb.init_oracle_client() as shown in the previous section, or set TNS_ADMIN to the directory name.

3. To use python-oracledb in Thick mode you must call oracledb.init_oracle_client() in your application, see Enabling python-oracledb Thick mode.

   import oracledb
   
   oracledb.init_oracle_client()
   

2.6. Installing python-oracledb on macOS

Python-oracledb is available as a Universal binary for Python 3.8, or later, on Apple Intel and M1 architectures. A binary is also available for Python 3.7 on Apple Intel.

2.6.1. Install python-oracledb

Use Python’s pip package to install python-oracledb from Python’s package repository PyPI:

python -m pip install oracledb --upgrade

The --user option may be useful if you do not have permission to write to system directories:

python -m pip install oracledb --upgrade --user

If you are behind a proxy, use the --proxy option. For example:

python -m pip install oracledb --upgrade --proxy=http://proxy.example.com:80

To install into the system Python, you may need to use /usr/bin/python3 instead of python:

/usr/bin/python3 -m pip install oracledb --upgrade --user

2.6.2. Optionally Install Oracle Client

By default, python-oracledb runs in a Thin mode which connects directly to Oracle Database so no further installation steps are required. However, to use additional features available in Thick mode you need Oracle Client libraries installed. Note that to use Thick mode on the M1 architecture you will need to use Rosetta with Python 64-bit Intel and the Instant Client (Intel x86) libraries.

2.6.2.1. Manual Installation

• Download the Basic 64-bit DMG from Oracle.

• In Finder, double-click DMG to mount it.

• Open a terminal window and run the install script in the mounted package, for example:

  /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru/install_ic.sh
  

  This copies the contents to $HOME/Downloads/instantclient_19_8. Applications may not have access to the Downloads directory, so you should move Instant Client somewhere convenient.

• In Finder, eject the mounted Instant Client package.

If you have multiple Instant Client DMG packages mounted, you only need to run install_ic.sh once. It will copy all mounted Instant Client DMG packages at the same time.

2.6.2.2. Scripted Installation

Instant Client installation can alternatively be scripted, for example:

cd $HOME/Downloads
curl -O https://download.oracle.com/otn_software/mac/instantclient/198000/instantclient-basic-macos.x64-19.8.0.0.0dbru.dmg
hdiutil mount instantclient-basic-macos.x64-19.8.0.0.0dbru.dmg
/Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru/install_ic.sh
hdiutil unmount /Volumes/instantclient-basic-macos.x64-19.8.0.0.0dbru

The Instant Client directory will be $HOME/Downloads/instantclient_19_8. Applications may not have access to the Downloads directory, so you should move Instant Client somewhere convenient.

2.6.3. Configure Oracle Instant Client

1. Call oracledb.init_oracle_client() in your application:

   import oracledb
   
   oracledb.init_oracle_client(lib_dir="/Users/your_username/Downloads/instantclient_19_8")
   

2. If you use optional Oracle configuration files such as tnsnames.ora, sqlnet.ora, or oraaccess.xml with Oracle Instant Client, then put the files in an accessible directory, for example in /Users/your_username/oracle/your_config_dir. Then use:

   import oracledb
   
   oracledb.init_oracle_client(lib_dir="/Users/your_username/Downloads/instantclient_19_8",
                               config_dir="/Users/your_username/oracle/your_config_dir")
   

   Or set the environment variable TNS_ADMIN to that directory name.

   Alternatively, put the files in the network/admin subdirectory of Oracle Instant Client, for example in /Users/your_username/Downloads/instantclient_19_8/network/admin. This is the default Oracle configuration directory for executables linked with this Instant Client.

2.7. Installing python-oracledb without Internet Access

To install python-oracledb on a computer that is not connected to the internet, download the appropriate python-oracledb file from Python’s package repository PyPI. Transfer this file to the offline computer and install it with:

python -m pip install "<file_name>"

Then follow the general python-oracledb platform installation instructions to install Oracle client libraries.

2.8. Installing python-oracledb without the Cryptography Package

If the Python cryptography package is not available, python-oracledb can still be installed but can only be used in Thick mode.

To install without the cryptography package, use pip’s --no-deps option, for example:

python -m pip install oracledb --no-deps

Oracle Client libraries must then be installed. See previous sections.

To use python-oracledb in Thick mode you must call oracledb.init_oracle_client() in your application, see Enabling python-oracledb Thick mode. Without this, your application will get the error DPY-3016: python-oracledb thin mode cannot be used because the cryptography package is not installed.

2.9. Installing from Source Code

The following dependencies are required to build python-oracledb from source code:

• Cython Package: Cython is a standard Python package from PyPI.

• The Python cryptography package. This will need to be installed manually before building python-oracledb. For example install with pip.

• C Compiler: A C99 compiler is needed.

2.9.1. Install Using GitHub

In order to install using the source on GitHub, use the following commands:

git clone --recurse-submodules https://github.com/oracle/python-oracledb.git
cd python-oracledb
python setup.py build
python setup.py install

If you do not have access to system directories, the --user option can be used to install into a local directory:

python setup.py install --user

Note that if you download a source zip file directly from GitHub then you will also need to download an ODPI-C source zip file and put the extracted contents inside the “odpi” subdirectory, for example in “python-oracledb-main/src/oracledb/impl/thick/odpi”.

Python-oracledb source code is also available from opensource.oracle.com. This can be installed with:

git clone --recurse-submodules https://opensource.oracle.com/git/oracle/python-oracledb.git
cd python-oracledb
python setup.py build
python setup.py install

2.9.2. Install Using Source from PyPI

The source package can be downloaded manually from PyPI and extracted, after which the following commands should be run:

python setup.py build
python setup.py install

If you do not have access to system directories, the --user option can be used to install into a local directory:

python setup.py install --user