- CxOracle is a Python extension module that enables access to Oracle Database. It conforms to the Python database API 2.0 specification with a considerable number of additions and a couple of exclusions. See the homepage for a feature list. CxOracle 8.2 has been tested with Python versions 3.6 through 3.9.
- Solved: using cx.oracle.LOB.read method to retrive LOB data. As per the documentation if your LOB (CLOB or BLOB) is smaller than 1GB you might directly obtain a string using a Cursor.outputtypehandler: CLOBs and BLOBs smaller than 1 GB can queried from the database directly as strings and bytes. This can be much faster than streaming.
- In order to provide cross- compatibility under Python 2, the SQLAlchemy cxOracle dialect will add Unicode-conversion to string data under Python 2 as well. Historically, this made use of converters that were supplied by cxOracle but were found to be non-performant; SQLAlchemy’s own converters are used for the string to Unicode conversion.
- CxOracle – Python Tutorials. September 12, 2018. Leave a Comment. Here I am providing the list of cxOracle tutorials I have given on this blog for the Python programs. You will learn how to use the Oracle database as a backend for your Python applications by using the cxOracle library. The following is the list of posts.
Install cxOracle on Windows These installation instructions assume that you are on 64-bit Windows and have a Windows x64 Oracle 11gR2 database running on your machine. The Oracle database can be on any edition of Oracle (Express, Standard, Enterprise).
cx_Oracle is a Python extension module that enables Python access to OracleDatabase. It conforms to the Python Database API v2.0 Specification with a considerable number ofadditions and a couple of exclusions.
Architecture¶
Python programs call cx_Oracle functions. Internally cx_Oracle dynamicallyloads Oracle Client libraries to access Oracle Database. The database can be onthe same machine as Python, or it can be remote.
cx_Oracle is typically installed from PyPI using pip. The Oracle Client librariesneed to be installed separately. The libraries can be from an installation ofOracle Instant Client, from afull Oracle Client installation, or even from an Oracle Database installation(if Python is running on the same machine as the database). Oracle’s standardclient-server version interoperability allows connection to both older andnewer databases from different Client library versions, see cx_OracleInstallation.
Some behaviors of the Oracle Client libraries can optionally be configured withan oraaccess.xml file, for example to enable auto-tuning of a statementcache. See Optional Oracle Client Configuration Files.
The Oracle Net layer can optionally be configured with files such astnsnames.ora and sqlnet.ora, for example to enable networkencryption. See Optional Oracle Net Configuration Files.
Oracle environment variables that are set before cx_Oracle first creates adatabase connection will affect cx_Oracle behavior. Optional variables includeNLS_LANG, NLS_DATE_FORMAT and TNS_ADMIN. See Oracle Environment Variables.
Features¶
The cx_Oracle feature highlights are:
- Easily installed 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 andBLOB) and binding of SQL objects
- Connection management, including connection pooling
- Oracle Database High Availability features
- Full use of Oracle Network Service infrastructure, including encryptednetwork 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:
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 andpassword and the databaseconnection string, and use them in query.py. Forcx_Oracle the connection string is commonly of the formathostname/servicename, using the host name where the database is running andthe Oracle Database service name of the database instance.
The cursor is the object that allows statements to beexecuted and results (if any) fetched.
The data values in managerId and firstName are ‘bound’ to the statementplaceholder ‘bind variables’ :mid and :fn when the statement isexecuted. This separates the statement text from the data, which helps avoidSQL Injection security risks. Binding is also important forperformance and scalability.
The cursor allows rows to be iterated over and displayed.
Cx_oracle Python Connection
Run the script:
The output is:
Examples and Tutorials¶
The Quick Start: Developing Python Applications for Oracle Databaseand Quick Start: Developing Python Applications for Oracle Autonomous Databaseinstructions have steps for Windows, Linux, and macOS.
Cx_oracle Python
Runnable examples are in the GitHub samples directory. A Pythoncx_Oracle tutorialis also available.