Step 3: Connect Python to Oracle using cxOracle connect Finally, copy/type the following syntax in Python while adding the needed info based on your Oracle connection: import cxOracle dsntns = cxOracle.makedsn('Host Name', 'Port Number', servicename='Service Name') # if needed, place an 'r' before any parameter in order to address special. CxOracle is a Python extension module that allows access to Oracle databases and conforms to the Python database API specification version cxOracle - Browse /5.1.2 at SourceForge.net Join/Login. First of all, it just seems like doing anything with Oracle is obnoxiously painful for no good reason. It's the nature of the beast I suppose. Cxoracle is a python module that allows you to connect to an Oracle Database and issue queries, inserts, updates.usual jazz.

1. Cx_oracle Download
2. Cx Oracle Example
3. Cx_oracle Connection String

Overview¶

To use cx_Oracle 8 with Python and Oracle Database you need:

• Python 3.5 and higher. Older versions of cx_Oracle may work with olderversions of Python.
• Oracle Client libraries. These can be from the free Oracle InstantClient,or those included in Oracle Database if Python is on the samemachine as the database. Oracle client libraries versions 19, 18, 12,and 11.2 are supported on Linux, Windows and macOS. Users havealso reported success with other platforms. Use the latest client possible:Oracle’s standard client-server version interoperability allows connection toboth older and newer databases.
• An Oracle Database, either local or remote.

The cx_Oracle module loads Oracle Client libraries which communicateover Oracle Net to an existing database. Oracle Net is not a separateproduct: it is how the Oracle Client and Oracle Database communicate.

Quick Start cx_Oracle Installation¶

• Install Python 3, if not alreadyavailable. On macOS you must always install your own Python.

  Python 3.5 and higher are supported by cx_Oracle 8. If you use Python 2,then the older cx_Oracle 7.3 will install.

• Install cx_Oracle from PyPI with:

  Note: if a binary wheel package is not available for your platform,the source package will be downloaded instead. This will be compiledand the resulting binary installed.

  The --user option may be useful, if you don’t have permission to write tosystem directories:

  If you are behind a proxy, add a proxy server to the command, for example add--proxy=http://proxy.example.com:80

• Add Oracle 19, 18, 12 or 11.2 client libraries to your operating systemlibrary search path such as PATH on Windows or LD_LIBRARY_PATH onLinux. On macOS use init_oracle_client() in yourapplication to pass the Oracle Client directory name, see Locating the Oracle Client Libraries.This is also usable on Windows.

  To get the libraries:

  • If your database is on a remote computer, then download and unzip the clientlibraries from the free Oracle Instant Client“Basic” or “Basic Light” package for your operating systemarchitecture.

    Instant Client on Windows requires an appropriate Microsoft WindowsRedistributables, see Installing cx_Oracle on Windows. On Linux, the libaio(sometimes called libaio1) package is needed. Oracle Linux 8 alsoneeds the libnsl package.

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

  Version 19, 18 and 12.2 client libraries can connect to Oracle Database 11.2or greater. Version 12.1 client libraries can connect to Oracle Database 10.2or greater. Version 11.2 client libraries can connect to Oracle Database 9.2or greater.

• Create a script like the one below:

  Locate your Oracle Database username and password, and the databaseconnection string. The connection string is commonly of the formathostname/servicename, using the hostname where the database isrunning, and using the service name of the Oracle Database instance.

  Substitute your username, password and connection string in thecode. Run the Python script, for example:

You can learn how to use cx_Oracle from the API documentationand samples.

If you run into installation trouble, check out the section on Troubleshooting.

Oracle Client and Oracle Database Interoperability¶

cx_Oracle requires Oracle Client libraries. The libraries provide thenecessary network connectivity to access an Oracle Database instance.They also provide basic and advanced connection management and datafeatures to cx_Oracle.

The simplest way to get Oracle Client libraries is to install the freeOracle Instant Client“Basic” or “Basic Light” package. The libraries are also available inany Oracle Database installation or full Oracle Client installation.

Oracle’s standard client-server network interoperability allowsconnections between different versions of Oracle Client libraries andOracle Database. For certified configurations see Oracle Support’sDoc ID 207303.1.In summary, Oracle Client 19, 18 and 12.2 can connect to Oracle Database 11.2 orgreater. Oracle Client 12.1 can connect to Oracle Database 10.2 orgreater. Oracle Client 11.2 can connect to Oracle Database 9.2 orgreater. The technical restrictions on creating connections may bemore flexible. For example Oracle Client 12.2 can successfullyconnect to Oracle Database 10.2.

cx_Oracle uses the shared library loading mechanism available on eachsupported platform to load the Oracle Client libraries at runtime. Itdoes not need to be rebuilt for different versions of the libraries.Since a single cx_Oracle binary can use different client versions andalso access multiple database versions, it is important yourapplication is tested in your intended release environments. NewerOracle clients support new features, such as the oraaccess.xml external configurationfile available with 12.1 or later clients, session pool improvements,improved high availability features, call timeouts, and other enhancements.

The cx_Oracle function clientversion() can be usedto determine which Oracle Client version is in use and the attributeConnection.version can be used to determine which OracleDatabase version a connection is accessing. These can then be used toadjust application behavior accordingly. Attempts to use some Oraclefeatures that are not supported by a particular client/servercombination may result in runtime errors. These include:

• when attempting to access attributes that are not supported by thecurrent Oracle Client library you will get the error “ORA-24315: illegalattribute type”
• when attempting to use implicit results with Oracle Client 11.2against Oracle Database 12c you will get the error “ORA-29481:Implicit results cannot be returned to client”
• when attempting to get array DML row counts with Oracle Client11.2 you will get the error “DPI-1050: Oracle Client library must be atversion 12.1 or higher”

Installing cx_Oracle on Linux¶

This section discusses the generic installation methods on Linux. To use Pythonand cx_Oracle RPM packages from yum on Oracle Linux, see Installing cx_Oracle RPMs on Oracle Linux.

Install cx_Oracle¶

The generic way to install cx_Oracle on Linux is to use Python’s Pip package toinstall cx_Oracle from PyPI:

The --user option may be useful, if you don’t have permission to write tosystem directories:

If you are behind a proxy, add a proxy server to the command, for example add--proxy=http://proxy.example.com:80

This will download and install a pre-compiled binary if one isavailable for yourarchitecture. If a pre-compiled binary is not available, the sourcewill be downloaded, compiled, and the resulting binary installed.Compiling cx_Oracle requires the Python.h header file. If you areusing the default python package, this file is in the python-develpackage or equivalent.

Install Oracle Client¶

Using cx_Oracle requires Oracle Client libraries to be installed.These provide the necessary network connectivity allowing cx_Oracleto access an Oracle Database instance.

• If your database is on a remote computer, then download the free OracleInstant Client“Basic” or “Basic Light” package for your operating systemarchitecture. Use the RPM or ZIP packages, based on yourpreferences.
• Alternatively, use the client libraries already available in alocally installed database such as the free Oracle DatabaseExpress Edition (“XE”)release.

Oracle Instant Client Zip Files¶

To use cx_Oracle with Oracle Instant Client zip files:

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

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

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

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

   On some Linux distributions this package is called libaio1 instead.

   On recent Linux versions, such as Oracle Linux 8, you may also need toinstall the libnsl package.

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

   Alternatively, set the environment variable LD_LIBRARY_PATH tothe appropriate directory for the Instant Client version. Forexample:

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

   Or set the environment variable TNS_ADMIN to that directory name.

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

Oracle Instant Client RPMs¶

To use cx_Oracle with Oracle Instant Client RPMs:

1. Download an Oracle 19, 18, 12, or 11.2 “Basic” or “Basic Light” RPM: 64-bitor 32-bit, matching yourPython architecture.

   Oracle’s yum server has Instant Client RPMs for Oracle Linux 8,Instant Client RPMs for Oracle Linux 7and Instant Client RPMs for Oracle Linux 6that can be downloaded without needing a click-through.

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

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

   Yum will automatically install required dependencies, such as libaio.

   On recent Linux versions, such as Oracle Linux 8, you may need to manuallyinstall the libnsl package.

3. For Instant Client 19, the system library search path isautomatically configured during installation.

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

   Alternatively, for version 18 and earlier, every shell runningPython will need to have the environment variableLD_LIBRARY_PATH set to the appropriate directory for theInstant Client version. For example:

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

   Or set the environment variable TNS_ADMIN to that directory name.

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

Local Database or Full Oracle Client¶

cx_Oracle applications can use Oracle Client 19, 18, 12, or 11.2 librariesfrom a local Oracle Database or full Oracle Client installation.

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

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

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

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 variableTNS_ADMIN to that directory name.

Installing cx_Oracle RPMs on Oracle Linux¶

Python and cx_Oracle RPM packages are available from the Oracle Linux yum server. Various versions of Python are easily installed.Using the yum server makes it easy to keep up to date.

Installation instructions are at Oracle Linux for PythonDevelopers.

Installing cx_Oracle on Windows¶

Install cx_Oracle¶

Use Python’s Pippackage to install cx_Oracle from PyPI:

If you are behind a proxy, specify your proxy server:

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

Install Oracle Client¶

Using cx_Oracle requires Oracle Client libraries to be installed.These provide the necessary network connectivity allowing cx_Oracleto access an Oracle Database instance. Oracle Client versions 19, 18,12 and 11.2 are supported.

• If your database is on a remote computer, then download the free OracleInstant Client“Basic” or “Basic Light” package for your operating systemarchitecture.
• Alternatively, use the client libraries already available in alocally installed database such as the free Oracle DatabaseExpress Edition (“XE”)release.

Oracle Instant Client Zip Files¶

To use cx_Oracle with Oracle Instant Client zip files:

1. Download an Oracle 19, 18, 12, or 11.2 “Basic” or “Basic Light” zipfile: 64-bitor 32-bit, matching yourPython architecture.

   The latest version is recommended. Oracle Instant Client 19 willconnect 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 yourapplication. For example unzipinstantclient-basic-windows.x64-19.9.0.0.0dbru.zip toC:oracleinstantclient_19_9.

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

   • 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 or VS 2005 32-bit

Configure Oracle Instant Client¶

1. There are several alternative ways to tell cx_Oracle where your Oracle Clientlibraries are, see cx_Oracle 8 Initialization.

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

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

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

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

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

2. If you use optional Oracle configuration files such as tnsnames.ora,sqlnet.ora or oraaccess.xml with Instant Client, then put the filesin an accessible directory, for example inC:oracleyour_config_dir. Then use:

   Or set the environment variable TNS_ADMIN to that directory name.

   Alternatively, put the files in a networkadmin subdirectory ofInstant Client, for example inC:oracleinstantclient_19_9networkadmin. This is the defaultOracle configuration directory for executables linked with thisInstant Client.

Local Database or Full Oracle Client¶

cx_Oracle applications can use Oracle Client 19, 18, 12, or 11.2libraries libraries from a local Oracle Database or full OracleClient.

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

1. Set the environment variable PATH to include the path that containsOCI.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 thenetworkadmin subdirectory of the Oracle Database softwareinstallation.

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

Installing cx_Oracle on macOS¶

Install Python¶

Make sure you are not using the bundled Python. This has restrictedentitlements and will fail to load Oracle client libraries. Instead useHomebrew or Python.org.

Install cx_Oracle¶

Use Python’s Pippackage to install cx_Oracle from PyPI:

The --user option may be useful, if you don’t have permission to write tosystem directories:

If you are behind a proxy, add a proxy server to the command, for example add--proxy=http://proxy.example.com:80

The source will be downloaded, compiled, and the resulting binaryinstalled.

Cx_oracle Download

Install Oracle Instant Client¶

Oracle Instant Client provides the network connectivity for accessing OracleDatabase.

Manual Installation¶

• Download the Basic 64-bit DMG from Oracle.

• In Finder, double click on the DMG to mount it.

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

  This copies the contents to $HOME/Downloads/instantclient_19_8.

• In Finder, eject the mounted Instant Client package.

If you have multiple Instant Client DMG packages mounted, you only need to runinstall_ic.sh once. It will copy all mounted Instant Client DMG packages atthe same time.

Scripted Installation¶

Instant Client installation can alternatively be scripted, for example:

The Instant Client directory will be $HOME/Downloads/instantclient_19_8.

Configure Oracle Instant Client¶

1. Call init_oracle_client() once in your application:

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

   Or set the environment variable TNS_ADMIN to that directory name.

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

Cx Oracle Example

Installing cx_Oracle without Internet Access¶

To install cx_Oracle on a computer that is not connected to theinternet, download the appropriate cx_Oracle file from PyPI. Transfer this file tothe offline computer and install it with:

Then follow the general cx_Oracle platform installation instructionsto install Oracle client libraries.

Install Using GitHub¶

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

Note that if you download a source zip file directly from GitHub thenyou will also need to download an ODPI-C source zip file and extract itinside the directory called “odpi”.

cx_Oracle source code is also available from oss.oracle.com. This canbe cloned with:

Install Using Source from PyPI¶

Cx_oracle Connection String

The source package can be downloaded manually fromPyPI and extracted, afterwhich the following commands should be run:

Upgrading from Older Versions¶

Review the release notes for deprecations and modify anyaffected code.

If you are upgrading from cx_Oracle 7 note these changes:

• The default character set used by cx_Oracle 8 is now “UTF-8”. Also, thecharacter set component of the NLS_LANG environment variable isignored. If you need to change the character set, then pass encodingand nendcoding parameters when creating a connection or connectionpool. See Character Sets and Globalization.
• Any uses of type(var) need to be changed to var.type.
• Any uses of var.typeisnotNone need to be changed toisinstance(var.type,cx_Oracle.ObjectType)
• Note that TIMESTAMPWITHTIMEZONE columns will now be reported ascx_Oracle.DB_TYPE_TIMESTAMP_TZ instead ofcx_Oracle.TIMESTAMP in Cursor.description.
• Note that TIMESTAMPWITHLOCALTIMEZONE columns will now be reportedas cx_Oracle.DB_TYPE_TIMESTAMP_LTZ instead ofcx_Oracle.TIMESTAMP in Cursor.description.
• Note that BINARY_FLOAT columns will now be reported ascx_Oracle.DB_TYPE_BINARY_FLOAT instead ofcx_Oracle.NATIVE_DOUBLE in Cursor.description.

If you are upgrading from cx_Oracle 5 note these installation changes:

• When using Oracle Instant Client, you should not set ORACLE_HOME.
• On Linux, cx_Oracle 6 and higher no longer uses Instant Client RPMsautomatically. You must set LD_LIBRARY_PATH or use ldconfig tolocate the Oracle client library.
• PyPI no longer allows Windows installers or Linux RPMs to behosted. Use the supplied cx_Oracle Wheels instead, or use RPMsfrom Oracle, see Installing cx_Oracle RPMs on Oracle Linux.

Installing cx_Oracle in Python 2¶

cx_Oracle 7.3 was the last version with support for Python 2.

If you install cx_Oracle in Python 2 using the commands provided above, thencx_Oracle 7.3 will be installed. This is equivalent to using a command like:

For other installation options such as installing through a proxy, seeinstructions above. Make sure the Oracle Client libraries are in the systemlibrary search path because cx_Oracle 7 does not support thecx_Oracle.init_oracle_client() method and does not support loading theOracle Client libraries from the directory containing the cx_Oracle modulebinary.

Installing cx_Oracle 5.3¶

If you require cx_Oracle 5.3, download a Windows installer from PyPI or use python-mpipinstallcx-oracle5.3 to install from source.

Very old versions of cx_Oracle can be found in the files section atSourceForce.

Troubleshooting¶

If installation fails:

• Use option -v with pip. Review your output and logs. Try to installusing a different method. Google anything that looks like an error.Try some potential solutions.
• Was there a network connection error? Do you need to set theenvironment variables http_proxy and/or https_proxy? Ortry pipinstall--proxy=http://proxy.example.com:80cx_Oracle--upgrade?
• If upgrading gave no errors but the old version is stillinstalled, try pipinstallcx_Oracle--upgrade--force-reinstall
• If you do not have access to modify your system version ofPython, can you use pipinstallcx_Oracle--upgrade--useror venv?
• Do you get the error “Nomodulenamedpip”? The pip module is builtinto Python but is sometimes removed by the OS. Use the venv module(builtin to Python 3.x) or virtualenv module instead.
• Do you get the error “fatalerror:dpi.h:Nosuchfileordirectory”when building from source code? Ensure that your source installation hasa subdirectory called “odpi” containing files. If missing, review thesection on Install Using GitHub.

If using cx_Oracle fails:

• Do you get the error “DPI-1047:OracleClientlibrarycannotbeloaded”?

  • On Windows and macOS, try using init_oracle_client().See Using cx_Oracle.init_oracle_client() to set the Oracle Client directory.

  • Check that Python and your Oracle Client libraries are both 64-bit, orboth 32-bit. The DPI-1047 message will tell you whether the 64-bitor 32-bit Oracle Client is needed for your Python.

  • Set the environment variable DPI_DEBUG_LEVEL to 64 and restartcx_Oracle. The trace messages will show how and where cx_Oracle islooking for the Oracle Client libraries.

    At a Windows command prompt, this could be done with:

    On Linux and macOS, you might use:

  • On Windows, if you used init_oracle_client() and havea full database installation, make sure this database is the currentlyconfigured database.

  • On Windows, if you are not usinginit_oracle_client(), then restart your command promptand use setPATH to check the environment variable has the correctOracle Client listed before any other Oracle directories.

  • On Windows, use the DIR command to verify that OCI.DLL exists inthe directory passed to init_oracle_client() or set in PATH.

  • On Windows, check that the correct Windows Redistributables havebeen installed.

  • On Linux, check the LD_LIBRARY_PATH environment variable containsthe Oracle Client library directory. If you are using Oracle InstantClient, a preferred alternative is to ensure a file in the/etc/ld.so.conf.d directory contains the path to the Instant Clientdirectory, and then run ldconfig.

  • On macOS, make sure you are not using the bundled Python (use Homebrew or Python.org instead). If you are not usinginit_oracle_client(), then put the Oracle InstantClient libraries in ~/lib or /usr/local/lib.

• If you got “DPI-1072:theOracleClientlibraryversionisunsupported”, then review the installation requirements. cx_Oracleneeds Oracle client libraries 11.2 or later. Note that version 19 is notsupported on Windows 7. Similar steps shown above for DPI-1047 mayhelp.

• If you have multiple versions of Python installed, make sure you areusing the correct python and pip (or python3 and pip3) executables.