cx_Oracle 6.0, the extremely popular Oracle Database interface for Python, is now Production on PyPI.
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'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.
Database Resident Connection Pooling (DRCP).
Database startup and shutdown.
Oracle Database High Availability Features, such as FAN notifications and Transaction Guard support.
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.
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() cursor.execute(""" 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)
Installation instructions: http://cx-oracle.readthedocs.io/en/latest/installation.html
Source Code Repository: https://github.com/oracle/python-cx_Oracle
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!