Use Python and cx_Oracle to build Oracle Database applications

Python is a general-purpose open source language. It is interpreted, and it is object-oriented with class-based inheritance. Everything in Python is an object, including classes, functions, and modules, so anything can have methods and attributes and can be passed as arguments to functions. Python is dynamically typed, which means that the types of variables do not need to be declared and can be changed easily. On the other hand, Python is strongly typed, which means that the type of an object cannot be changed once it is created.

Python is supported on all major operating systems, including embedded systems, and implementations beyond the reference C implementation enable integration with other ecosystems. The C (primary) implementation of Python can be extended easily in C to provide access to other libraries written in C or to improve performance, which enables Python to excel at integration tasks. The cx_Oracle driver for Oracle Database is one example of a C extension. It enables fast, full-featured, and efficient access to Oracle Database. This article introduces Python and the cx_Oracle driver and demonstrates how to use them to write applications for Oracle Database.

Python 3 was released in 2008, and over the last eight years, it has been enhanced considerably. The examples shown in this article were written with Python 3.5 but can be used with Python 2.7 with minimal changes.

Install Python

The official source of the Python language is the Python website. The site includes binary packages for Windows and macOS and source packages for use on other platforms. (Most Linux distributions come with at least one version of Python preinstalled.) The Python site also offers excellent documentation on the language, an extensive standard library, and tutorials to help you get started. If the standard library doesn’t have what you need, chances are very good that one of the tens of thousands of packages available on PyPI (the Python Package Index) will satisfy your requirements. You can download and install these packages directly with the built-in package manager, “pip,” if you know the name of the package you want to install, or you can browse or search the list of packages. Mailing lists are available for Python user questions, and there is a large Python community on Stack Overflow for questions and answers. In addition, Python conferences are held on every continent except Antarctica.

Included in the many packages on PyPI is support for all the major database platforms available today. To keep the modules for all the database platforms behaving as similarly as possible, a database API specification was developed. cx_Oracle, the module developed to support the Oracle Database platform, fully implements this database API specification, and it includes support for properties and methods that enable you to take advantage of additional Oracle Database–specific features.

Before installing and using cx_Oracle, install an Oracle client. The Oracle client software enables the database and the application to reside on different machines (even completely different platforms), and it seamlessly handles network traffic between the two. The easiest Oracle client package to install is called Oracle Instant Client, and you can download it for free from Oracle Technology Network for any of the platforms supported by Oracle. Installation instructions are included at the bottom of the download page. Several packages are available, but using cx_Oracle requires only the Basic or Basic Lite package. The SDK package is also required, however, if you plan to build cx_Oracle yourself.

With an Oracle client installed, install cx_Oracle. On Windows, the simplest approach is to download an executable installer directly from PyPI. On Oracle Linux or similar Linux distributions, RPM package files are available for download from the same location. For all other platforms, use the pip package manager to perform the download, compilation, and installation of cx_Oracle with one command:

pip install cx_Oracle

By default, pip is installed in Python 2 starting at Python 2.7.9 and in Python 3 starting at Python 3.4. If you are using an older version of Python that doesn’t have pip installed, you can also download the source package for cx_Oracle directly from PyPI, extract the files from the package, and then run the following commands:

python setup.py build
python setup.py install

 

Test and Explore

After you complete the Python, Oracle client, and cx_Oracle installation steps without errors, test the installations by importing the cx_Oracle module into the Python interpreter and connecting to an Oracle Database instance, as in the following script:

import cx_Oracle

connection = cx_Oracle.connect("user/password@server/ServiceName")
cursor = connection.cursor()
cursor.execute("select sysdate from dual")
today, = cursor.fetchone()
print("The current date is", today)

Note that the connection string passed to the cx_Oracle.connect() function is the same one you use when connecting with SQL*Plus to Oracle Database. Knowing this can be useful for troubleshooting installation issues.

The test query returns an Oracle date, and cx_Oracle maps that data to a Python datetime object from the built-in datetime module. Table 1 shows the mappings of Oracle, cx_Oracle, and Python types.

Oracle Type cx_Oracle Variable Type Python Type
VARCHAR2 cx_Oracle.STRING str
NVARCHAR2 cx_Oracle.NCHAR str
CHAR cx_Oracle.FIXED_CHAR str
NCHAR cx_Oracle.FIXED_NCHAR str
ROWID cx_Oracle.ROWID str
RAW cx_Oracle.BINARY bytes
LONG cx_Oracle.LONG_STRING str
LONG RAW cx_Oracle.LONG_BINARY bytes
NUMBER cx_Oracle.NUMBER int or float
DATE cx_Oracle.DATETIME datetime.datetime
TIMESTAMP cx_Oracle.TIMESTAMP datetime.datetime
TIMESTAMP WITH TIME ZONE cx_Oracle.TIMESTAMP datetime.datetime
TIMESTAMP WITH LOCAL TIME ZONE cx_Oracle.TIMESTAMP datetime.datetime
CLOB cx_Oracle.CLOB cx_Oracle.LOB
BLOB cx_Oracle.BLOB cx_Oracle.LOB
NCLOB cx_Oracle.NCLOB cx_Oracle.LOB
BFILE cx_Oracle.BFILE cx_Oracle.LOB
CURSOR cx_Oracle.CURSOR cx_Oracle.Cursor

Table 1

When cx_Oracle executes a query, it examines the metadata Oracle Database makes available and creates a variable of the type indicated in the second column of Table 1 for each column that is fetched. Although these types can be changed with advanced cx_Oracle features (input and output type handlers), the default types are usually more than adequate. As rows are fetched, a tuple is created for each row, as shown in the script in Listing 1.

Code Listing 1: Script that creates tuple plus output

# drop table if it exists
try:
    cursor.execute("drop table TestQuery purge")
except:
    pass

# create table to demonstrate queries
cursor.execute("""
    create table TestQuery (
        IntCol number(9) not null,
        StrCol varchar2(30) not null,
        DateCol date not null
    )""")

# populate table with a few sample rows
for i in range(3):
    cursor.execute("""
        insert into TestQuery (IntCol, StrCol, DateCol)
        values (:intCol, :strCol, :dateCol)""",
        intCol = i + 1,
        strCol = "Test String %d" % (i + 1),
        dateCol = datetime.date(2017, i + 1, 1))

# perform query
cursor.execute("select IntCol, StrCol, DateCol from TestQuery")
for row in cursor:
    print("Row:", row)


Row: (1, 'Test String 1', datetime.datetime(2017, 1, 1, 0, 0))
Row: (2, 'Test String 2', datetime.datetime(2017, 2, 1, 0, 0))
Row: (3, 'Test String 3', datetime.datetime(2017, 3, 1, 0, 0))

cx_Oracle uses array fetching internally to reduce the number of times the Oracle client must communicate with Oracle Database, which is particularly important when the database and the client are not together on a fast network. The default array size is 50, which is adequate for most queries. You can change this easily, however, by modifying the arraysize property of the cursor before executing the query, as in the following:

 

Next Steps

TRY Oracle Database Cloud services. 

LEARN more about Oracle Database support for Python. 

LEARN more about cx_Oracle. 

cursor.arraysize = 200

Inserts, as shown in the script in Listing 1, can use an array instead of executing the insert statement once for each row. The following script performs the same inserts as the script in Listing 1 but inserts them all at once, instead of one at a time, thereby improving performance.

data = [(i + 1, "Test String %d" % (i + 1), 
                datetime.date(2017, i + 1, 1)) \
        for i in range(3)]
cursor.executemany("""
        insert into TestQuery (IntCol, StrCol, DateCol)
        values (:intCol, :strCol, :dateCol)""", data)

One other type that deserves further mention is the large object (LOB). You can use LOBs to store images, videos, text documents, or anything else that requires more space than what is supported in the basic string and raw types. One other use for LOBs is JSON data. Oracle Database 12c Release 1 (Oracle Database 12.1) added support for JSON, which enables the database to store, index, and query JSON data—without the need for a schema that defines the data. Listing 2 demonstrates cx_Oracle support for JSON in Oracle Database 12c Release 2 (Oracle Database 12.2).

Code Listing 2: cx_Oracle accessing JSON in Oracle Database 12.2 via character large object (CLOB)

# drop table if it exists
try:
    cursor.execute("drop table TestJSON purge")
except:
    pass

# create table to demonstrate use of LOBs and JSON
cursor.execute("""
    create table TestJSON (
        JSONCol clob not null,
        constraint TestJSON_ck check (TestJSON is JSON)
    )""")

# populate table with a few rows
inputData = [
    dict(x = 1, y = 2, z = 3),
    dict(x = 4, y = 5, z = 6),
    dict(x = 7, y = 8, z = 9)
]
for row in inputData:
    cursor.execute("insert into TestJSON (JsonCol) values (:jsonData)",
            jsonData = json.dumps(row))

# perform query; output should match input
cursor.execute("select JsonCol from TestJSON")
outputData = [json.loads(lob.read()) for lob, in cursor]
print("Data matches?", outputData == data)

As you can see from this brief introduction, you can use Python with the cx_Oracle module to quickly and easily connect to Oracle Database, perform queries, and insert data. For more information on cx_Oracle, consult the documentation and additional sample code in the source samples directory.


Anthony Tuininga is a member of the data access group at Oracle. He has more than 25 years of experience with Oracle Database and is the creator and maintainer of the cx_Oracle driver.

Send us your comments

SOurce :http://www.oracle.com/technetwork/issue-archive/2017/17-mar/o27python-3618400.html