Python and Oracle Database Tutorial: Scripting for the Future

Python cx_Oracle logo



This tutorial is an introduction to using Python with Oracle Database. It contains beginner and advanced material. Sections can be done in any order. Choose the content that interests you and your skill level.

Follow the steps in this document. The tutorial directory has scripts to run and modify. The tutorial/solutions directory has scripts with the suggested code changes.

If you are new to Python review the Appendix: Python Primer to gain an understanding of the language.

When you have finished this tutorial, we recommend reviewing the cx_Oracle documention.


These cx_Oracle tutorial instructions can be found here. The files used in them can be found in the cx_Oracle GitHub repository.

If you are running this tutorial in your own environment, install the required software:

  1. Python. Version 3.6 (or later) is preferred.

  2. cx_Oracle version 7.3, or version 8, or later.

  3. Oracle Client libraries.

  4. SQL*Plus such as from the Oracle Instant Client SQL*Plus Package.

The Advanced Queuing section requires Oracle client 12.2 or later. The SODA section requires Oracle client 18.5, or later, and Oracle Database 18 or later.

To create the schema run:

sqlplus sys/yoursyspassword@localhost/orclpdb1 as sysdba @sql/SetupSamples

If DRCP is not already running, connect to the SYS user again in SQL*Plus and execute the command:

execute dbms_connection_pool.start_pool()

Connection Information

Database credentials and the connection string are set in two files:

The username is "pythonhol" with the password "welcome". The connect string is "localhost/orclpdb1".

If your database is not local, or has a different service, you will need to modify the connection information in these two files. If you are installing your own schema, you can also modify the default username and password in sql/SampleEnv.sql.

The following sections may need adjusting, depending on how you have set up your environment.

Using Python cx_Oracle with Oracle Database

Python is a popular general purpose dynamic scripting language. The cx_Oracle interface provides Python API to access Oracle Database.


In this tutorial, you have learned how to:

For further reading see the cx_Oracle documentation.

Appendix: Python Primer

Python is a dynamically typed scripting language. It is most often used to run command-line scripts but is also used for web applications and web services.

Running Python

You can either:

When you run scripts, Python automatically creates bytecode versions of them in a folder called __pycache__. These improve performance of scripts that are run multiple times. They are automatically recreated if the source file changes.


Whitespace indentation is significant in Python. When copying examples, use the same column alignment as shown. The samples in this lab use spaces, not tabs.

The following indentation prints 'done' once after the loop has completed:

for i in range(5):

But this indentation prints 'done' in each iteration:

for i in range(5):


Python strings can be enclosed in single or double quotes:

'A string constant'
"another constant"

Multi line strings use a triple-quote syntax:



Variables do not need types declared:

count = 1
ename = 'Arnie'


Comments are either single line:

# a short comment

They can be multi-line using the triple-quote token to create a string that does nothing:

a longer


Strings and variables can be displayed with a print() function:

print('Hello, World!')
print('Value:', count)

Data Structures

Associative arrays are called 'dictionaries':

a2 = {'PI':3.1415, 'E':2.7182}

Ordered arrays are called 'lists':

a3 = [101, 4, 67]

Lists can be accessed via indexes.


Tuples are like lists but cannot be changed once they are created. They are created with parentheses:

a4 = (3, 7, 10)

Individual values in a tuple can be assigned to variables like:

v1, v2, v3 = a4

Now the variable v1 contains 3, the variable v2 contains 7 and the variable v3 contains 10.

The value in a single entry tuple like "(13,)"can be assigned to a variable by putting a comma after the variable name like:

v1, = (13,)

If the assignment is:

v1 = (13,)

then v1 will contain the whole tuple "(13,)"


Everything in Python is an object. As an example, given the of the list a3 above, the append() method can be used to add a value to the list.


Now a3 contains [101, 4, 67, 23]

Flow Control

Code flow can be controlled with tests and loops. The if/elif/else statements look like:

if v == 2 or v == 4:
elif v == 1 or v == 3:
    print('Unknown number')

This also shows how the clauses are delimited with colons, and each sub block of code is indented.


A traditional loop is:

for i in range(10):

This prints the numbers from 0 to 9. The value of i is incremented in each iteration.

The 'for' command can also be used to iterate over lists and tuples:

a5 = ['Aa', 'Bb', 'Cc']
for v in a5:

This sets v to each element of the list a5 in turn.


A function may be defined as:

def myfunc(p1, p2):
    "Function documentation: add two numbers"
    print(p1, p2)
    return p1 + p2

Functions may or may not return values. This function could be called using:

v3 = myfunc(1, 3)

Function calls must appear after their function definition.

Functions are also objects and have attributes. The inbuilt __doc__ attribute can be used to find the function description:



Sub-files can be included in Python scripts with an import statement.

import os
import sys

Many predefined modules exist, such as the os and the sys modules.


Copyright © 2017, 2020, Oracle and/or its affiliates. All rights reserved