Oracle Database is the world's most popular database, providing tremendous power and flexibility for efficient data management. Python, in turn, is an easy-to-use, open source and multiparadigm programming language with a wide variety of focus areas, including web development, data analysis, building games, system administration software development, and spatial and scientific applications. So, the omnipresence of Python and the power of Oracle Database, when combined, enable developing high-performance, data-driven applications in a broad range of fields.
This article provides a brief introduction to cx_Oracle—a Python extension module that enables combining the best-in-class data management solution from Oracle with the ease-of-use and productivity of the Python language—and includes key getting-started information, covering the following topics:
- A brief overview of cx_Oracle
- Preparing your working environment
- Installing the cx_Oracle Python package
- Connecting to Oracle Database from Python with cx_Oracle
- A simple query example
- Using bind variables
- Handling exceptions
- Oracle Database and cx_Oracle data type mappings
Many modules designed to access different data sources are freely available for Python, each extending the language with advanced or specialized features. Once the necessary module is imported into your script, you can make use of the functionality it provides, performing necessary operations against the underlying data source.
Unlike many other data sources, however, Oracle Database provides much more than just a simple means for storing and retrieving restricted amounts of data in a certain format. Thus, it can be used to store, process, and move in and out large volumes of data in different formats in a number of different ways. This means a Python module that bridges Python and Oracle Database is supposed to be fast and reliable, allowing Python coders to make best use of the database functionality.
Meet cx_Oracle, a Python extension module that enables access to Oracle Database. To interact with Oracle Database, cx_Oracle uses Oracle Client libraries behind the scenes. Figure 1 illustrates this architecture:
The cx_Oracle module conforms to the Python DB API specification with many additions to support Oracle Database advanced features. Each release of cx_Oracle is tested against the latest releases of Oracle Instant Client and Oracle Database. For the latest cx_Oracle release and links to documentation and other resources, check the cx_Oracle homepage.
The examples provided in this article series were tested with the following software:
Note: Some examples may work with older releases.
If you do not have access to Oracle Database, you can obtain it in one of the following ways:
- Download and install a prebuilt virtual machine (VM) for Oracle VM VirtualBox containing preconfigured Oracle Database software.
- Download Oracle Database software from Oracle Technology Network (OTN) and install it in your system.
- Choose one of the Oracle Database Cloud services that provide access to Oracle Database in the cloud.
- Deploy Oracle Database as a Docker container, either in the cloud or on premises.
Probably the simplest way is the first one. You install Oracle VM VirtualBox and then import into it the Database App Development VM appliance, which provides a preinstalled and preconfigured Oracle database. This appliance also comes with ready-to-use Oracle SQL Developer. If you choose to download and install Oracle Database software in your system, you will need to download and install Oracle SQL Developer separately. If you are using an Oracle Database instance running in the cloud, you can access it from a local Oracle SQL Developer.
As mentioned, cx_Oracle requires Oracle Database client software to connect and interact with an Oracle Database instance.
Note: If your Python installation is on the same machine as the database, a separate Oracle Instant Client installation is not required, because Oracle Database Client libraries come with an Oracle Database installation.
If your database is remote, you can download and install either the Oracle Instant Client Basic package or its smaller version, the Oracle Instant Client Basic Light package, from the Oracle Instant Client Downloads page, using the Oracle Instant Client packages specific to your system.If you are a Microsoft Windows user, download the Basic or Basic Light package zip file and then unzip it into a single folder in your system, for example:
Use the correct 32-bit or 64-bit architecture to match your Python installation.
You might also want to install the additional Oracle Instant Client SQL*Plus Package, which contains libraries and executable for running the SQL*Plus command line query tool. This tool will allow you to test a database connection immediately to verify that your Instant Client and network are working correctly. Download and then unzip the SQL*Plus package to the same folder where you unzipped the Basic (or Basic Light) package.
Then, edit your
PATH environment variable to include the folder that contains the Oracle Instant Client files:
Finally, make sure you download and install Visual Studio Redistributable from Microsoft that matches your Oracle Instant Client's architecture. For example, Oracle Instant Client 12.2 requires Visual Studio Redistributable 2013.
If you are on Linux, the simplest way to get and install an Oracle Instant Client package is to download its .rpm file from the Instant Client Downloads for Linux page, and then install it using yum to automatically resolve missing dependencies. Here is what the command looks like:
$ sudo yum install oracle-instantclient12.2-basic-188.8.131.52.0-1.x86_64.rpm
You can download and install the SQL*Plus Instant Client Package RPM the same way:
$ sudo yum install oracle-instantclient12.2-sqlplus-184.108.40.206.0-1.x86_64.rpm
For Linux distributions that use the
.deb package format, install and run
alien on the RPMs before installing them with
dpkg or, alternatively, install the Oracle Instant Client
Now that you have installed the Oracle Instant Client libraries, you need to add them to the runtime link path. If Oracle Instant Client is the only Oracle software installed on the machine, update the runtime link path as follows:
$ sudo sh -c "echo /usr/lib/oracle/12.2/client64/lib > /etc/ld.so.conf.d/oracle-instantclient.conf" $ sudo ldconfig
Otherwise, or as an alternative, add the path of the Oracle Instant Client libraries to the
LD_LIBRARY_PATH environment variable:
Once you have completed the Oracle Instant Client installation on your system, you can perform the following test to make sure everything works properly:
1. Launch SQL*Plus and connect to the database server, specifying a connection string in the following format:
dbschema/password@dbhostnameorIP[:port]/dbservice. For example, it might look like
firstname.lastname@example.org/orcl, as in the example below. Note that you can omit the port if it is 1521, because that is the default.
2. Issue a query against the database to make sure you can interact with it.
These simple steps can be implemented as shown in Figure 2 below:
Note: The password for the
hr sample schema may differ from the one used in the example depicted in the Figure 2. In this particular example, a connection is being made to the database running within a VM installed from the prebuilt Database App Development VM appliance mentioned earlier in this section. All the passwords in this appliance are initially set to
The next software component to install is Python. Although you most likely already have it on your system, you might want to use a separate installation to follow the examples provided in this series of articles.
You can download the latest version of Python (for this series, you will need Python 3) from the Download Python page at Python.org. If you are on Linux or UNIX, you must download and compile the source code. For detailed steps, check out the "Installing Python on Oracle Linux" section in the "Oracle REST Data Services for Python Developers—Part 1" article. For Windows and macOS users, installers are available.
You're just one step away from completing the preparation of your working environment. The only component still left to be installed is cx_Oracle. Starting with release 6.0, cx_Oracle can be installed from wheels, the new standard of Python distribution.
Under all platforms, you can install cx_Oracle with the
pip installer program:
# python -m pip install cx_Oracle --upgrade
See the installation instructions for detailed steps.
At the moment, cx_Oracle wheel installation files are available only for Windows and Linux systems. If you're using another platform, the source package will be downloaded and compiled.
If you have more than one Python installation on your system, you must select the Python executable associated with the Python installation that you want to use. For example, the installation of cx_Oracle on a particular system might be performed with the following command:
# python3.6 -m pip install cx_Oracle --upgrade
pip will automatically download and install from the cx_Oracle's PyPI page a precompiled binary wheel for your architecture. The table in Figure 3 lists all the wheels available at the time of this writing.
Upon successful completion of cx_Oracle, the
pip command gives the following message:
Successfully installed cx-Oracle-6.2.1
To make sure that cx_Oracle is functioning correctly, perform some simple tests. For this, you can use the Python interactive shell, an ideal tool when it comes to testing, debugging, evaluating, and so on. To invoke it, just enter the name of the Python executable at a terminal prompt:
At the Python prompt, to begin with, try to import cx_Oracle:
>>> import cx_Oracle >>>
Then, you can check the Oracle Instant Client library version:
>>> cx_Oracle.clientversion() (12, 2, 0, 1, 0)
Your next test is to check the database connectivity:
>>> conn = cx_Oracle.connect("hr", "oracle", "dbhostname/orcl")
If the database instance is in the middle of being started, you may receive an error message. If so, try connecting again in a minute or so.
When you are connected, run this command:
>>> conn.version '220.127.116.11.0'
Note: As you will see in the examples later in this series, performing a check to see the version of Oracle Client and Oracle Database being used can be appropriate when you are going to employ a new feature that is not supported by older client or database versions.
Finally, quit the shell using Control-D or using the
quit() method. This will close the connection for you.
>>> quit() $
With cx_Oracle installed in your Python environment, the powerful world of Oracle Database is open to you. In practical terms, this means you can access and manipulate data in an Oracle database from within your Python code, employing a very wide set of features available in Oracle Database.
Let's start with a simple script that illustrates how you can issue a query against the database and then print out the retrieved results using only a few lines of code. The query in this particular script is issued against the hr.departments and hr.locations tables (standard Oracle Database sample tables), retrieving information about departments located in Seattle.
import cx_Oracle try: conn = cx_Oracle.connect("hr", "oracle", "dbhostname/orcl") sql="""SELECT d.* FROM departments d, locations l WHERE l.city = :city AND d.location_id = l.location_id""" column_length=[5, 22, 6, 5] cursor = conn.cursor() for row in cursor.execute(sql, city = 'Seattle'): for i in range (len(row)): print(str(row[i]).ljust(column_length[i]), end='') print() except cx_Oracle.DatabaseError as exc: err, = exc.args print("Oracle-Error-Code:", err.code) print("Oracle-Error-Message:", err.message) finally: cursor.close() conn.close()
Note the use of indentation in the above code. If you are coming from another language, don't forget that indentation in Python is required to indicate what block of code a line belongs to. Be sure that your text editor allows you to see differences between spaces and tabs, because even visually indented lines may not be actually indented from the point of view of Python. For further details, refer to the documentation.
Save the above script to a file called
simplequery.py and then invoke it with
$ python simplequery.py
When invoked, the script should generate the following output:
10 Administration 200 1700 30 Purchasing 114 1700 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 120 Treasury None 1700 ...
Turning back to the script code, note that string
Seattle is passed to the query through a bind variable. Using bind variables is recommended, because no additional parsing is required for identical queries (the value of a bind variable is supplied at query execution time), which ultimately improves overall performance. In the third part of this series, you will see how you can implement object binding with cx_Oracle, binding named object types such as SDO and PL/SQL objects.
Another interesting feature used in the above script is the
try-except-finally block, showing how exception handling can be implemented using cx_Oracle exception objects. In this particular example, only the
cx_Oracle.DatabaseError exception, raised for errors related to the database, is handled. Error handling will be covered in more detail in the next part of this series.
Upon fetching, data retrieved from the database is implicitly converted to Python data types. cx_Oracle supports its own set of data types to mediate in this process. The cx_Oracle data types are described in detail in the "Types" section of the cx_Oracle documentation.
You can see data types mapping in action with the help of the following test. Suppose you want to know how the Oracle Database data types used in the definition of the hr.departments table columns will be mapped to cx_Oracle data types.
First, to see the cx_Oracle data types, you might create and execute the following script:
import cx_Oracle conn = cx_Oracle.connect("hr", "oracle", "dbhostname/orcl") sql = "SELECT * FROM departments" cursor = conn.cursor() cursor.execute(sql) for each in cursor.description: print(each[0:2])
This script shows you the cx_Oracle data types to which the departments table columns have been converted:
('DEPARTMENT_ID', <class 'cx_Oracle.NUMBER'>) ('DEPARTMENT_NAME', <class 'cx_Oracle.STRING'>) ('MANAGER_ID', <class 'cx_Oracle.NUMBER'>) ('LOCATION_ID', <class 'cx_Oracle.NUMBER'>)
Then, you can look at their Oracle Database counterparts using the following script:
import cx_Oracle conn = cx_Oracle.connect("hr", "oracle", "dbhostname/orcl") sql = """SELECT column_name, data_type FROM all_tab_cols WHERE table_name = 'DEPARTMENTS' AND owner = 'HR'""" cursor = conn.cursor() for each in cursor.execute(sql): print(each)
This script should produce the following output:
('DEPARTMENT_ID', 'NUMBER') ('DEPARTMENT_NAME', 'VARCHAR2') ('MANAGER_ID', 'NUMBER') ('LOCATION_ID', 'NUMBER')
Now that you have tested your Python/Oracle Database environment, the next parts of this series will dive deeper into Python/Oracle Database development.
- Python Developer Center on OTN
- cx_Oracle's documentation
- cx_Oracle on GitHub
- "Introduction to Python for PL/SQL Developers" tutorial series