Introduction to cx_Oracle¶
cx_Oracle is a Python extension module that enables Python access to Oracle Database. It conforms to the Python Database API v2.0 Specification with a considerable number of additions and a couple of exclusions.
Note
cx_Oracle has a major new release under a new name and homepage python-oracledb.
New projects should install python-oracledb instead of cx_Oracle.
Architecture¶
Python programs call cx_Oracle functions. Internally cx_Oracle dynamically loads Oracle Client libraries to access Oracle Database. The database can be on the same machine as Python, or it can be remote.
cx_Oracle is typically installed from PyPI using pip. The Oracle Client libraries need to be installed separately. The libraries can be from an installation of Oracle Instant Client, from a full Oracle Client installation, or even from an Oracle Database installation (if Python is running on the same machine as the database). Oracle’s standard client-server version interoperability allows connection to both older and newer databases from different Client library versions, see cx_Oracle Installation.
Some behaviors of the Oracle Client libraries can optionally be configured with
an oraaccess.xml
file, for example to enable auto-tuning of a statement
cache. See Optional Oracle Client Configuration Files.
The Oracle Net layer can optionally be configured with files such as
tnsnames.ora
and sqlnet.ora
, for example to enable network
encryption. See Optional Oracle Net Configuration Files.
Oracle environment variables that are set before cx_Oracle first creates a database connection will affect cx_Oracle behavior. Optional variables include NLS_LANG, NLS_DATE_FORMAT and TNS_ADMIN. See Oracle Environment Variables.
Features¶
The cx_Oracle feature highlights are:
Easy installation from PyPI
Support for multiple Oracle Client and Database versions
Execution of SQL and PL/SQL statements
Extensive Oracle data type support, including large objects (CLOB and BLOB) and binding of SQL objects
Connection management, including connection pooling
Oracle Database High Availability features
Full use of Oracle Network Service infrastructure, including encrypted network traffic and security features
A complete list of supported features can be seen here.
Getting Started¶
Install cx_Oracle using the installation steps.
Create a script query.py
as shown below:
# query.py
import cx_Oracle
# Establish the database connection
connection = cx_Oracle.connect(user="hr", password=userpwd,
dsn="dbhost.example.com/orclpdb1")
# Obtain a cursor
cursor = connection.cursor()
# Data for binding
manager_id = 145
first_name = "Peter"
# Execute the query
sql = """SELECT first_name, last_name
FROM employees
WHERE manager_id = :mid AND first_name = :fn"""
cursor.execute(sql, mid=manager_id, fn=first_name)
# Loop over the result set
for row in cursor:
print(row)
This uses Oracle’s sample HR schema.
Simple connection to the database requires a username,
password and connection string. Locate your Oracle Database user name and
password and the database
connection string, and use them in query.py
. For
cx_Oracle, the connection string is commonly of the format
hostname/servicename
, using the host name where the database is running and
the Oracle Database service name of the database instance.
The cursor is the object that allows statements to be executed and results (if any) fetched.
The data values in managerId
and firstName
are ‘bound’ to the statement
placeholder ‘bind variables’ :mid
and :fn
when the statement is
executed. This separates the statement text from the data, which helps avoid
SQL Injection security risks. Binding is also important for
performance and scalability.
The cursor allows rows to be iterated over and displayed.
Run the script:
python query.py
The output is:
('Peter', 'Hall')
('Peter', 'Tucker')
Examples and Tutorials¶
Runnable examples are in the GitHub samples directory. A Python cx_Oracle tutorial is also available.