Welcome to python-oracledb’s documentation!

Python-oracledb is the new name for the Python cx_Oracle driver. The python-oracledb driver is an open source module that enables Python programs to access Oracle Database. It conforms to the Python Database API v2.0 Specification with a considerable number of additions and a couple of exclusions.

This module is currently tested with Python 3.6, 3.7, 3.8, 3.9, and 3.10 against Oracle Database 21c, 19c, 18c, 12c, and 11.2.

Changes in python-oracledb releases can be found in the release notes.

User Guide

• 1. Introduction to the Python Driver for Oracle Database
  • 1.1. Architecture
    • 1.1.1. python-oracledb Thin Mode Architecture
    • 1.1.2. python-oracledb Thick Mode Architecture
  • 1.2. Feature Highlights of python-oracledb
  • 1.3. Getting Started
  • 1.4. Examples and Tutorial
• 2. Installing python-oracledb
  • 2.1. Quick Start python-oracledb Installation
  • 2.2. Supported Oracle Database Versions
  • 2.3. Installation Requirements
  • 2.4. Installing python-oracledb on Linux
    • 2.4.1. Install python-oracledb
    • 2.4.2. Optionally Install Oracle Client
  • 2.5. Installing python-oracledb on Windows
    • 2.5.1. Install python-oracledb
    • 2.5.2. Optionally Install Oracle Client
  • 2.6. Installing python-oracledb on macOS
    • 2.6.1. Install python-oracledb
    • 2.6.2. Optionally Install Oracle Client
    • 2.6.3. Configure Oracle Instant Client
  • 2.7. Installing python-oracledb without Internet Access
  • 2.8. Installing from Source Code
    • 2.8.1. Install Using GitHub
    • 2.8.2. Install Using Source from PyPI
  • 2.9. Troubleshooting
• 3. Initializing python-oracledb
  • 3.1. Enabling python-oracledb Thick mode
    • 3.1.1. Locating the Oracle Client Libraries
  • 3.2. Optional Oracle Net Configuration Files
  • 3.3. Optional Oracle Client Configuration File
  • 3.4. Oracle Environment Variables for python-oracledb Thick Mode
  • 3.5. Other python-oracledb Thick Mode Initialization
  • 3.6. Changing from python-oracledb Thick Mode to python-oracledb Thin Mode
• 4. Connecting to Oracle Database
  • 4.1. Standalone Connections
    • 4.1.1. Creating a Standalone Connection
    • 4.1.2. Common Connection Errors
  • 4.2. Connection Strings
    • 4.2.1. Easy Connect Syntax for Connection Strings
    • 4.2.2. Net Service Names for Connection Strings
    • 4.2.3. Oracle Net Connect Descriptor Strings
    • 4.2.4. JDBC and Oracle SQL Developer Connection Strings
  • 4.3. Using the ConnectParams Builder Class
  • 4.4. Connection Pooling
    • 4.4.1. Creating a Connection Pool
    • 4.4.2. Connection Pool Sizing
    • 4.4.3. Connection Pool Reconfiguration
    • 4.4.4. Session CallBacks for Setting Pooled Connection State
    • 4.4.5. Heterogeneous and Homogeneous Connection Pools
    • 4.4.6. Using the PoolParams Builder Class
  • 4.5. Database Resident Connection Pooling (DRCP)
    • 4.5.1. Enabling DRCP in Oracle Database
    • 4.5.2. Coding Applications to use DRCP
    • 4.5.3. Monitoring DRCP
  • 4.6. Connecting Using Proxy Authentication
  • 4.7. Connecting Using External Authentication
    • 4.7.1. Using an Oracle Wallet for External Authentication
    • 4.7.2. Operating System Authentication
  • 4.8. Privileged Connections
  • 4.9. Securely Encrypting Network Traffic to Oracle Database
    • 4.9.1. Native Network Encryption
  • 4.10. Resetting Passwords
  • 4.11. Connecting to Oracle Cloud Autonomous Databases
    • 4.11.1. One-way TLS Connection to Oracle Autonomous Database
    • 4.11.2. Mutual TLS (mTLS) Connection to Oracle Autonomous Database
  • 4.12. Connecting to Sharded Databases
• 5. Executing SQL
  • 5.1. SQL Queries
    • 5.1.1. Fetch Methods
    • 5.1.2. Closing Cursors
    • 5.1.3. Query Column Metadata
    • 5.1.4. Fetch Data Types
    • 5.1.5. Changing Fetched Data Types with Output Type Handlers
    • 5.1.6. Fetched Number Precision
    • 5.1.7. Changing Query Results with Outconverters
    • 5.1.8. Changing Query Results with Rowfactories
    • 5.1.9. Scrollable Cursors
    • 5.1.10. Fetching Oracle Database Objects and Collections
    • 5.1.11. Limiting Rows
    • 5.1.12. Client Result Cache
    • 5.1.13. Fetching Raw Data
    • 5.1.14. Querying Corrupt Data
  • 5.2. INSERT and UPDATE Statements
    • 5.2.1. Inserting NULLs
• 6. Executing PL/SQL
  • 6.1. PL/SQL Stored Procedures
  • 6.2. PL/SQL Stored Functions
  • 6.3. Anonymous PL/SQL Blocks
  • 6.4. Creating Stored Procedures and Packages
  • 6.5. Using DBMS_OUTPUT
  • 6.6. Implicit results
  • 6.7. Edition-Based Redefinition (EBR)
• 7. Using Bind Variables
  • 7.1. Binding by Name or Position
  • 7.2. Bind Direction
  • 7.3. Binding Null Values
  • 7.4. Binding ROWID Values
  • 7.5. DML RETURNING Bind Variables
  • 7.6. LOB Bind Variables
  • 7.7. REF CURSOR Bind Variables
  • 7.8. Binding PL/SQL Collections
  • 7.9. Binding PL/SQL Records
  • 7.10. Binding Spatial Data Types
  • 7.11. Changing Bind Data Types using an Input Type Handler
  • 7.12. Binding Multiple Values to a SQL WHERE IN Clause
  • 7.13. Binding Column and Table Names
• 8. Executing Batch Statement and Bulk Loading
  • 8.1. Batch Execution of SQL
    • 8.1.1. Predefining Memory Areas
  • 8.2. Batch Execution of PL/SQL
  • 8.3. Handling Data Errors
  • 8.4. Identifying Affected Rows
  • 8.5. DML RETURNING
  • 8.6. Loading CSV Files into Oracle Database
• 9. Managing Transactions
  • 9.1. Autocommitting
  • 9.2. Explicit Transactions
• 10. Tuning python-oracledb
  • 10.1. Tuning Fetch Performance
    • 10.1.1. Choosing values for arraysize and prefetchrows
    • 10.1.2. Tuning Data Copies between Databases
    • 10.1.3. Tuning Fetching from REF CURSORS
  • 10.2. Database Round-trips
    • 10.2.1. Finding the Number of Round-Trips
  • 10.3. Statement Caching
    • 10.3.1. Setting the Statement Cache
    • 10.3.2. Tuning the Statement Cache
    • 10.3.3. Disabling the Statement Cache
  • 10.4. Client Result Caching (CRC)
• 11. Using CLOB and BLOB Data
  • 11.1. Simple Insertion of LOBs
  • 11.2. Fetching LOBs as Strings and Bytes
  • 11.3. Streaming LOBs (Read)
  • 11.4. Streaming LOBs (Write)
  • 11.5. Temporary LOBs
• 12. Using JSON Data
  • 12.1. Using the Oracle Database 21c JSON Type in python-oracledb Thick Mode
  • 12.2. Using the Oracle Database 21c JSON Type and python-oracledb Thin Mode
  • 12.3. Using the Oracle 12c JSON type in python-oracledb
  • 12.4. IN Bind Type Mapping
  • 12.5. Query and OUT Bind Type Mapping
  • 12.6. SQL/JSON Path Expressions
  • 12.7. Accessing Relational Data as JSON
• 13. Using XMLTYPE Data
• 14. Working with Simple Oracle Document Access (SODA)
  • 14.1. SODA Examples
  • 14.2. Using the SODA Metadata Cache
  • 14.3. Committing SODA Work
• 15. Using Oracle Advanced Queuing (AQ)
  • 15.1. Creating a Queue
  • 15.2. Enqueuing Messages
  • 15.3. Dequeuing Messages
  • 15.4. Using Object Queues
  • 15.5. Using Recipient Lists
  • 15.6. Changing Queue and Message Options
  • 15.7. Bulk Enqueue and Dequeue
• 16. Working with Continuous Query Notification (CQN)
  • 16.1. Requirements
  • 16.2. Creating a Subscription
  • 16.3. Registering Queries
• 17. Using Two-Phase Commits (TPC)
• 18. Starting and Stopping Oracle Database
  • 18.1. Starting Oracle Database Up
  • 18.2. Shutting Oracle Database Down
• 19. Using High Availability with python-oracledb
  • 19.1. General HA Recommendations
  • 19.2. Network Configuration
  • 19.3. Fast Application Notification (FAN)
  • 19.4. Application Continuity (AC)
  • 19.5. Transaction Guard
• 20. Tracing python-oracledb
  • 20.1. Application Tracing
    • 20.1.1. Oracle Database End-to-End Tracing
    • 20.1.2. Subclassing Connections
    • 20.1.3. Debugging PL/SQL with the Java Debug Wire Protocol
    • 20.1.4. Low Level SQL Tracing
  • 20.2. Finding the Python-oracledb Mode
  • 20.3. Database Views
    • 20.3.1. V$SESSION_CONNECT_INFO
    • 20.3.2. V$SESSION
  • 20.4. Low Level Python-oracledb Driver Tracing
• 21. Character Sets and Globalization
  • 21.1. Character Sets
    • 21.1.1. Database Character Set
    • 21.1.2. Database National Character Set
    • 21.1.3. Setting the Client Character Set
  • 21.2. Setting the Client Locale
    • 21.2.1. Thick Mode Oracle Database National Language Support (NLS)
    • 21.2.2. Thin Mode Locale-aware Number and Date Conversions
• 22. Catching Exceptions
  • 22.1. Error Handling in Thin and Thick Modes
• 23. Appendix A: Oracle Database Features Supported by python-oracledb
  • 23.1. Supported Oracle Database Data Types
• 24. Appendix B: Differences between python-oracledb Thin and Thick Modes
  • 24.1. Connection Handling Differences between Thin and Thick Modes
    • 24.1.1. Connections to a Local Database
    • 24.1.2. Oracle Net Services and Client Configuration Files
    • 24.1.3. Connection Strings
    • 24.1.4. Transport Layer Security (TLS) Support
    • 24.1.5. Native Network Encryption and Checksumming
  • 24.2. Connection Pooling Differences between Thin and Thick Modes
  • 24.3. Supported Database Data Types in Thin and Thick Modes
  • 24.4. Query Metadata in Thin and Thick Modes
  • 24.5. Statement Caching in Thin and Thick Modes
  • 24.6. Fetching JSON in Thin and Thick Modes
  • 24.7. Error Handling in Thin and Thick Modes
  • 24.8. Globalization in Thin and Thick Modes
  • 24.9. Tracing in Thin and Thick Modes
• 25. Appendix C: The python-oracledb and cx_Oracle Drivers
  • 25.1. Differences between the python-oracledb and cx_Oracle Drivers
    • 25.1.1. Mode differences from cx_Oracle
    • 25.1.2. Oracle Client Library Loading Differences from cx_Oracle
    • 25.1.3. Connection Differences from cx_Oracle
    • 25.1.4. Pooling Differences from cx_Oracle
    • 25.1.5. Cursor Object Differences from cx_Oracle
    • 25.1.6. Advanced Queuing (AQ) Differences from cx_Oracle
    • 25.1.7. Error Handling Differences from cx_Oracle
  • 25.2. Upgrading from cx_Oracle 8.3 to python-oracledb
    • 25.2.1. Things to Know Before the Upgrade
    • 25.2.2. Steps to Upgrade to python-oracledb
    • 25.2.3. Code to Aid the Upgrade to python-oracledb
    • 25.2.4. Python Frameworks, SQL Generators, and ORMs

API Manual

• 1. API: python-oracledb Module
  • 1.1. Oracledb Methods
    • __future__
    • Binary()
    • clientversion()
    • connect()
    • ConnectParams()
    • create_pool()
    • Cursor()
    • Date()
    • DateFromTicks()
    • init_oracle_client()
    • makedsn()
    • PoolParams()
    • Time()
    • TimeFromTicks()
    • Timestamp()
    • TimestampFromTicks()
  • 1.2. Oracledb Constants
    • 1.2.1. General
    • 1.2.2. Advanced Queuing: Delivery Modes
    • 1.2.3. Advanced Queuing: Dequeue Modes
    • 1.2.4. Advanced Queuing: Dequeue Navigation Modes
    • 1.2.5. Advanced Queuing: Dequeue Visibility Modes
    • 1.2.6. Advanced Queuing: Dequeue Wait Modes
    • 1.2.7. Advanced Queuing: Enqueue Visibility Modes
    • 1.2.8. Advanced Queuing: Message States
    • 1.2.9. Advanced Queuing: Other
    • 1.2.10. Connection Authorization Modes
    • 1.2.11. Database Shutdown Modes
    • 1.2.12. Event Types
    • 1.2.13. Operation Codes
    • 1.2.14. Connection Pool Get Modes
    • 1.2.15. Connection Pool Purity Constants
    • 1.2.16. Subscription Grouping Classes
    • 1.2.17. Subscription Grouping Types
    • 1.2.18. Subscription Namespaces
    • 1.2.19. Subscription Protocols
    • 1.2.20. Subscription Quality of Service
    • 1.2.21. DB API Types
    • 1.2.22. Database Types
    • 1.2.23. Database Type Synonyms
    • 1.2.24. Other Types
    • 1.2.25. Two-Phase Commit (TPC) Constants
  • 1.3. Oracledb Exceptions
    • Warning
    • Error
    • InterfaceError
    • DatabaseError
    • DataError
    • OperationalError
    • IntegrityError
    • InternalError
    • ProgrammingError
    • NotSupportedError
  • 1.4. Oracledb._Error Objects
    • _Error.code
    • _Error.offset
    • _Error.full_code
    • _Error.message
    • _Error.context
    • _Error.isrecoverable
• 2. API: Defaults Object
  • 2.1. Defaults Attributes
    • defaults.arraysize
    • defaults.config_dir
    • defaults.fetch_decimals
    • defaults.fetch_lobs
    • defaults.prefetchrows
    • defaults.stmtcachesize
• 3. API: Connection Objects
  • 3.1. Connection Methods
    • Connection.__enter__()
    • Connection.__exit__()
    • Connection.begin()
    • Connection.cancel()
    • Connection.changepassword()
    • Connection.close()
    • Connection.commit()
    • Connection.createlob()
    • Connection.cursor()
    • Connection.getSodaDatabase()
    • Connection.gettype()
    • Connection.is_healthy()
    • Connection.msgproperties()
    • Connection.ping()
    • Connection.prepare()
    • Connection.queue()
    • Connection.rollback()
    • Connection.shutdown()
    • Connection.startup()
    • Connection.subscribe()
    • Connection.tpc_begin()
    • Connection.tpc_commit()
    • Connection.tpc_end()
    • Connection.tpc_forget()
    • Connection.tpc_prepare()
    • Connection.tpc_recover()
    • Connection.tpc_rollback()
    • Connection.unsubscribe()
    • Connection.xid()
  • 3.2. Connection Attributes
    • Connection.action
    • Connection.autocommit
    • Connection.call_timeout
    • Connection.client_identifier
    • Connection.clientinfo
    • Connection.current_schema
    • Connection.dbop
    • Connection.dsn
    • Connection.econtext_id
    • Connection.edition
    • Connection.encoding
    • Connection.external_name
    • Connection.handle
    • Connection.inputtypehandler
    • Connection.internal_name
    • Connection.ltxid
    • Connection.maxBytesPerCharacter
    • Connection.module
    • Connection.nencoding
    • Connection.outputtypehandler
    • Connection.stmtcachesize
    • Connection.tag
    • Connection.thin
    • Connection.tnsentry
    • Connection.username
    • Connection.version
• 4. API: ConnectParams Objects
  • 4.1. ConnectParams Methods
    • ConnectParams.copy()
    • ConnectParams.get_connect_string()
    • ConnectParams.parse_connect_string()
    • ConnectParams.set()
  • 4.2. ConnectParams Attributes
    • ConnectParams.appcontext
    • ConnectParams.cclass
    • ConnectParams.config_dir
    • ConnectParams.debug_jdwp
    • ConnectParams.disable_oob
    • ConnectParams.edition
    • ConnectParams.events
    • ConnectParams.expire_time
    • ConnectParams.host
    • ConnectParams.https_proxy
    • ConnectParams.https_proxy_port
    • ConnectParams.matchanytag
    • ConnectParams.mode
    • ConnectParams.port
    • ConnectParams.protocol
    • ConnectParams.proxy_user
    • ConnectParams.purity
    • ConnectParams.retry_count
    • ConnectParams.retry_delay
    • ConnectParams.server_type
    • ConnectParams.service_name
    • ConnectParams.shardingkey
    • ConnectParams.sid
    • ConnectParams.ssl_server_cert_dn
    • ConnectParams.ssl_server_dn_match
    • ConnectParams.stmtcachesize
    • ConnectParams.supershardingkey
    • ConnectParams.tag
    • ConnectParams.tcp_connect_timeout
    • ConnectParams.user
    • ConnectParams.wallet_location
• 5. API: ConnectionPool Objects
  • 5.1. ConnectionPool Methods
    • ConnectionPool.acquire()
    • ConnectionPool.close()
    • ConnectionPool.drop()
    • ConnectionPool.reconfigure()
    • ConnectionPool.release()
  • 5.2. ConnectionPool Attributes
    • ConnectionPool.busy
    • ConnectionPool.dsn
    • ConnectionPool.getmode
    • ConnectionPool.homogeneous
    • ConnectionPool.increment
    • ConnectionPool.max
    • ConnectionPool.max_lifetime_session
    • ConnectionPool.max_sessions_per_shard
    • ConnectionPool.min
    • ConnectionPool.name
    • ConnectionPool.opened
    • ConnectionPool.ping_interval
    • ConnectionPool.soda_metadata_cache
    • ConnectionPool.stmtcachesize
    • ConnectionPool.thin
    • ConnectionPool.timeout
    • ConnectionPool.tnsentry
    • ConnectionPool.username
    • ConnectionPool.wait_timeout
• 6. API: PoolParams Objects
  • 6.1. PoolParams Methods
    • PoolParams.copy()
    • PoolParams.get_connect_string()
    • PoolParams.parse_connect_string()
    • PoolParams.set()
  • 6.2. PoolParams Attributes
    • PoolParams.connectiontype
    • PoolParams.externalauth
    • PoolParams.getmode
    • PoolParams.homogeneous
    • PoolParams.increment
    • PoolParams.min
    • PoolParams.max
    • PoolParams.max_lifetime_session
    • PoolParams.max_sessions_per_shard
    • PoolParams.ping_interval
    • PoolParams.session_callback
    • PoolParams.soda_metadata_cache
    • PoolParams.timeout
    • PoolParams.wait_timeout
• 7. API: Cursor Objects
  • 7.1. Cursor Methods
    • Cursor.__enter__()
    • Cursor.__exit__()
    • Cursor.arrayvar()
    • Cursor.bindnames()
    • Cursor.callfunc()
    • Cursor.callproc()
    • Cursor.close()
    • Cursor.execute()
    • Cursor.executemany()
    • Cursor.fetchall()
    • Cursor.fetchmany()
    • Cursor.fetchone()
    • Cursor.getarraydmlrowcounts()
    • Cursor.getbatcherrors()
    • Cursor.getimplicitresults()
    • Cursor.__iter__()
    • Cursor.parse()
    • Cursor.prepare()
    • Cursor.scroll()
    • Cursor.setinputsizes()
    • Cursor.setoutputsize()
    • Cursor.var()
  • 7.2. Cursor Attributes
    • Cursor.arraysize
    • Cursor.bindvars
    • Cursor.connection
    • Cursor.description
    • Cursor.fetchvars
    • Cursor.inputtypehandler
    • Cursor.lastrowid
    • Cursor.outputtypehandler
    • Cursor.prefetchrows
    • Cursor.rowcount
    • Cursor.rowfactory
    • Cursor.scrollable
    • Cursor.statement
• 8. API: Variable Objects
  • 8.1. Variable Methods
    • Variable.getvalue()
    • Variable.setvalue()
  • 8.2. Variable Attributes
    • Variable.actual_elements
    • Variable.buffer_size
    • Variable.inconverter
    • Variable.num_elements
    • Variable.outconverter
    • Variable.size
    • Variable.type
    • Variable.values
• 9. API: Subscription Objects
  • 9.1. Subscription Methods
    • Subscription.registerquery()
  • 9.2. Subscription Attributes
    • Subscription.callback
    • Subscription.connection
    • Subscription.id
    • Subscription.ip_address
    • Subscription.name
    • Subscription.namespace
    • Subscription.operations
    • Subscription.port
    • Subscription.protocol
    • Subscription.qos
    • Subscription.timeout
    • 9.2.1. Message Objects
    • 9.2.2. MessageTable Objects
    • 9.2.3. MessageRow Objects
    • 9.2.4. MessageQuery Objects
• 10. API: LOB Objects
  • 10.1. LOB Methods
    • LOB.close()
    • LOB.fileexists()
    • LOB.getchunksize()
    • LOB.getfilename()
    • LOB.isopen()
    • LOB.open()
    • LOB.read()
    • LOB.setfilename()
    • LOB.size()
    • LOB.trim()
    • LOB.write()
  • 10.2. LOB Attributes
    • LOB.type
• 11. API: DbObjectType Objects
  • 11.1. DbObjectType Methods
    • DbObjectType()
  • 11.2. DbObjectType Attributes
    • DbObjectType.attributes
    • DbObjectType.iscollection
    • DbObjectType.name
    • DbObjectType.element_type
    • DbObjectType.schema
  • 11.3. DbObject Objects
    • 11.3.1. DbObject Methods
    • 11.3.2. DbObject Attributes
  • 11.4. DbObjectAttribute Objects
    • DbObjectAttribute.name
    • DbObjectAttribute.type
• 12. API: Advanced Queuing (AQ)
  • 12.1. Queue Objects
    • 12.1.1. Queue Methods
    • 12.1.2. Queue Attributes
  • 12.2. Dequeue Options
    • DeqOptions.condition
    • DeqOptions.consumername
    • DeqOptions.correlation
    • DeqOptions.deliverymode
    • DeqOptions.mode
    • DeqOptions.msgid
    • DeqOptions.navigation
    • DeqOptions.transformation
    • DeqOptions.visibility
    • DeqOptions.wait
  • 12.3. Enqueue Options
    • EnqOptions.deliverymode
    • EnqOptions.transformation
    • EnqOptions.visibility
  • 12.4. Message Properties
    • MessageProperties.attempts
    • MessageProperties.correlation
    • MessageProperties.delay
    • MessageProperties.deliverymode
    • MessageProperties.enqtime
    • MessageProperties.exceptionq
    • MessageProperties.expiration
    • MessageProperties.msgid
    • MessageProperties.payload
    • MessageProperties.priority
    • MessageProperties.state
    • Messageproperties.recipient
• 13. API: SODA
  • 13.1. SODA Requirements
  • 13.2. SodaDatabase Objects
    • 13.2.1. SodaDatabase Methods
  • 13.3. SodaCollection Objects
    • 13.3.1. SodaCollection Methods
    • 13.3.2. SodaCollection Attributes
  • 13.4. SodaDoc Objects
    • 13.4.1. SodaDoc Methods
    • 13.4.2. SodaDoc Attributes
  • 13.5. SodaDocCursor Objects
    • 13.5.1. SodaDocCursor Methods
  • 13.6. SodaOperation Objects
    • 13.6.1. SodaOperation Methods
• 14. Deprecations

Indices and tables

• Index

• Module Index

• Search Page