Welcome to cx_Oracle’s documentation!¶
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.
cx_Oracle is a module that enables access to Oracle Database and conforms to the Python database API specification. This module is currently tested against Oracle Client 21c, 19c, 18c, 12c, and 11.2, and Python 3.6, 3.7, 3.8, 3.9 and 3.10. Older versions of cx_Oracle may be used with previous Python releases.
cx_Oracle is distributed under an open-source license (the BSD license). A detailed description of cx_Oracle changes can be found in the release notes .
User Guide¶
- Introduction to cx_Oracle
- Architecture
- Features
- Getting Started
- Examples and Tutorials
- Overview
- Quick Start cx_Oracle Installation
- Oracle Client and Oracle Database Interoperability
- Installing cx_Oracle on Linux
- Install cx_Oracle
- Install Oracle Client
- Install cx_Oracle
- Install Oracle Client
- Install Python
- Install cx_Oracle
- Install Oracle Instant Client
- Configure Oracle Instant Client
- Locating the Oracle Client Libraries
- Using cx_Oracle.init_oracle_client() to set the Oracle Client directory
- Establishing Database Connections
- Closing Connections
- Connection Strings
- Easy Connect Syntax for Connection Strings
- Oracle Net Connect Descriptor Strings
- Net Service Names for Connection Strings
- JDBC and Oracle SQL Developer Connection Strings
- Connection Pool Sizing
- Connection Pool Reconfiguration
- Session CallBacks for Setting Pooled Connection State
- Heterogeneous and Homogeneous Connection Pools
- Using an Oracle Wallet for External Authentication
- Operating System Authentication
- Install the Wallet and Network Configuration Files
- Run Your Application
- Access Through a Proxy
- Using the Easy Connect Syntax with Autonomous Database
- SQL Queries
- Fetch Methods
- Closing Cursors
- Query Column Metadata
- Fetch Data Types
- Changing Fetched Data Types with Output Type Handlers
- Fetched Number Precision
- Changing Query Results with Outconverters
- Changing Query Results with Rowfactories
- Scrollable Cursors
- Fetching Oracle Database Objects and Collections
- Limiting Rows
- Client Result Cache
- Fetching Raw Data
- Querying Corrupt Data
- Inserting NULLs
- PL/SQL Stored Procedures
- PL/SQL Stored Functions
- Anonymous PL/SQL Blocks
- Creating Stored Procedures and Packages
- Using DBMS_OUTPUT
- Implicit results
- Edition-Based Redefinition (EBR)
- Binding By Name or Position
- Bind Direction
- Binding Null Values
- Binding ROWID Values
- DML RETURNING Bind Variables
- LOB Bind Variables
- REF CURSOR Bind Variables
- Binding PL/SQL Collections
- Binding PL/SQL Records
- Binding Spatial Datatypes
- Changing Bind Data Types using an Input Type Handler
- Binding Multiple Values to a SQL WHERE IN Clause
- Binding Column and Table Names
- Simple Insertion of LOBs
- Fetching LOBs as Strings and Bytes
- Streaming LOBs (Read)
- Streaming LOBs (Write)
- Temporary LOBs
- IN Bind Type Mapping
- Query and OUT Bind Type Mapping
- SQL/JSON Path Expressions
- Accessing Relational Data as JSON
- Overview
- SODA Examples
- Using the SODA Metadata Cache
- Committing SODA Work
- Batch Execution of SQL
- Batch Execution of PL/SQL
- Handling Data Errors
- Identifying Affected Rows
- DML RETURNING
- Predefining Memory Areas
- Loading CSV Files into Oracle Database
- Creating a Queue
- Enqueuing Messages
- Dequeuing Messages
- Using Object Queues
- Changing Queue and Message Options
- Bulk Enqueue and Dequeue
- Requirements
- Creating a Subscription
- Registering Queries
- Autocommitting
- Explicit Transactions
- Tuning Fetch Performance
- Choosing values for arraysize and prefetchrows
- Tuning REF CURSORS
- Finding the Number of Round-Trips
- Setting the Client Character Set
- Character Set Example
- Finding the Database and Client Character Set
- Starting Oracle Database Up
- Shutting Oracle Database Down
- General HA Recommendations
- Network Configuration
- Fast Application Notification (FAN)
- Application Continuity (AC)
- Transaction Guard
- Subclass Connections
- Oracle Database End-to-End Tracing
- Low Level SQL Tracing in cx_Oracle
API Manual¶
- Module Interface
- Constants
- General
- Advanced Queuing: Delivery Modes
- Advanced Queuing: Dequeue Modes
- Advanced Queuing: Dequeue Navigation Modes
- Advanced Queuing: Dequeue Visibility Modes
- Advanced Queuing: Dequeue Wait Modes
- Advanced Queuing: Enqueue Visibility Modes
- Advanced Queuing: Message States
- Advanced Queuing: Other
- Connection Authorization Modes
- Database Shutdown Modes
- Event Types
- Operation Codes
- Session Pool Get Modes
- Session Pool Purity
- Subscription Grouping Classes
- Subscription Grouping Types
- Subscription Namespaces
- Subscription Protocols
- Subscription Quality of Service
- DB API Types
- Database Types
- Database Type Synonyms
- Other Types
- Message Objects
- Message Table Objects
- Message Row Objects
- Message Query Objects
- Object Objects
- Object Attribute Objects
- Queues
- Dequeue Options
- Enqueue Options
- Message Properties
- SODA Requirements
- SODA Database Object
- SODA Collection Object
- SODA Document Object
- SODA Document Cursor Object
- SODA Operation Object
Indices and tables¶
© Copyright 2016, 2020, Oracle and/or its affiliates. All rights reserved. Portions Copyright © 2007-2015, Anthony Tuininga. All rights reserved. Portions Copyright © 2001-2007, Computronix (Canada) Ltd., Edmonton, Alberta, Canada. All rights reserved. Revision a05b9a52 . Last updated on Jun 13, 2023.
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.
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)
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.
- Constants
