The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

Python cx_Oracle 6 Production Release: Hitting A Six

Christopher Jones
Senior Principal Product Manager

cx_Oracle 6.0, the extremely popular Oracle Database interface for Python, is now Production on PyPI.

cx_Oracle logo

cx_Oracle is an open source package that covers the Python Database API specification with many additions to support Oracle advanced features. It was begun by Anthony Tuininga in 1998 in the era of Oracle Database 8i and Python 1.5. Since then there have been over 30 releases covering up to Oracle Database 12c and Python 3.

Anthony joined Oracle relatively recently. In his time with us, he has been hard at work in the language API space, leading cx_Oracle changes as well as creating the ODPI-C interface, and also making contributions to node-oracledb.

cx_Oracle 6.0 is the latest and greatest release from our group. It can connect to both on-premise databases and to Oracle Database Cloud Services. It can also be run in the cloud itself.

The use cases for cx_Oracle are endless. Both small and big projects make use of cx_Oracle for all kinds of data manipulation and application development projects.

cx_Oracle has extensive API documentation, solid samples, and a clean test suite. (We also run stress tests for cx_Oracle in the Oracle Database development environment). To complete the link dump, the home page is here and the source code is hosted on GitHub.

cx_Oracle 6 Features

cx_Oracle 6's support for Oracle Database is excellent.

  • Easily installed from PyPI with Wheels.

  • Support for Python 2 and 3.

  • Support for Oracle Client 11.2, 12.1 and 12.2. Oracle's standard cross-version interoperability allows easy upgrades and connectivity to different Oracle Database versions.

  • Connect to Oracle Database 9.2, 10, 11 or 12 (depending on the Oracle Client version used).

  • SQL and PL/SQL Execution. The underlying Oracle Client libraries have significant optimizations including compressed fetch, pre-fetching, client and server result set caching, and statement caching with auto-tuning.

  • Full use of Oracle Network Service infrastructure, including encrypted network traffic, authentication, and security features.

  • Extensive Oracle data type support, including large object support (CLOB and BLOB).

  • Direct binding to SQL objects. One great use case is binding Python objects to Oracle Spatial SDO objects.

  • Array operations for efficient INSERT and UPDATEs.

  • Array row counts and batch error handling for array operations.

  • Fetching of large result sets.

  • REF CURSOR support.

  • Support for scrollable cursors. Go back and forth through your query results.

  • Fetch PL/SQL Implicit Results. Easily return query results from PL/SQL.

  • Row Prefetching. Efficient use of the network.

  • Client Result Caching. Improve performance of frequently executed look-up statements.

  • Support for Advanced Queuing. Use database notifications to build micro-service applications.

  • Continuous Query Notification. Get notified when data changes.

  • Support for Edition Based Redefinition. Easily switch applications to use updated PL/SQL logic.

  • Support for setting application context during the creation of a connection, making application metadata more accessible to the database, including in LOGON triggers.

  • End-to-end monitoring and tracing.

  • Transaction Management.

  • Session Pooling.

  • Database Resident Connection Pooling (DRCP).

  • Privileged Connections.

  • External Authentication.

  • Database startup and shutdown.

  • Oracle Database High Availability Features, such as FAN notifications and Transaction Guard support.

Changes since cx_Oracle 5.3

The main changes in cx_Oracle 6 are:

  • Re-implemented to use our new ODPI-C abstraction layer for Oracle Database. The cx_Oracle API is unchanged. The cx_Oracle design, build and linking process has improved because of ODPI-C.

  • Python Wheels are now available for install. This is made possible by the ODPI-C architecture.

  • Less code in Python's Global Interpreter Lock, giving better scalability.

  • Support for DML RETURNING of multiple rows.

  • Support for Universal ROWIDs.

  • LOB locators are now associated to LOB objects so they are not overwritten on database round trips.

As you can see, the use of ODPI-C was a significant change, leading to code refactoring and simplification. It gives us a springboard to make future improvements to cx_Oracle using a code base that has multiple consumers (and hence testers) including node-oracledb 2.

See What's New in the cx_Oracle documentation for the full change list.

With the release of cx_Oracle 6, no new features are planned for cx_Oracle 5.

Installing cx_Oracle

With the new cx_Oracle 6 Wheels, installing is straightforward as:

python -m pip install cx_Oracle --upgrade

If a binary is not available, the cx_Oracle source will be compiled.

Oracle Client 11.2, 12.1 or 12.2 libraries are needed in your system library path (such as PATH on Windows, or LD_LIBRARY_PATH on Linux). These libraries provide a lot of features such as connection management, query and statement cache management, as well as high availability features. Libraries can be easily installed from the free Oracle Instant Client, an Oracle Database installation, or a full Oracle Client installation. Oracle's standard client-server interoperability applies, e.g. Oracle Client 12.2 can connect to Oracle Database 11.2 onward.

Thanks to ODPI-C, you do not need to update cx_Oracle if you decide to upgrade your Oracle Client from 11.2 or 12.1 to a newer version. You can simply change your library loading path, e.g. PATH or LD_LIBRARY_PATH, to the new version of the Oracle Client libraries.

Once you have cx_Oracle installed, you can connect to your database. For example:

from __future__ import print_function

import cx_Oracle

connection = cx_Oracle.connect("hr", "welcome", "localhost/orclpdb")

cursor = connection.cursor()
        SELECT first_name, last_name
        FROM employees
        WHERE department_id = :did AND employee_id > :eid""",
        did = 50,
        eid = 190)
for fname, lname in cursor:
    print("Values:", fname, lname)

cx_Oracle References

Home page: https://oracle.github.io/python-cx_Oracle/index.html

Installation instructions: http://cx-oracle.readthedocs.io/en/latest/installation.html

Documentation: http://cx-oracle.readthedocs.io/en/latest/index.html

Source Code Repository: https://github.com/oracle/python-cx_Oracle

Last Words

The cx_Oracle interface is the most popular scripting language for Oracle Database. It has a long history, is widely used, and exposes features for building all kinds of database applications. The release of cx_Oracle 6 brings a refactored implementation that is more scalable and makes it easier to bring you great, future Oracle functionality.

Thank you to all our users and testers who are part of a very big & happy cx_Oracle community.

Keep in touch!

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.