Python and Oracle Database Tutorial: Scripting for the Future

Python cx_Oracle logo

Contents

Overview

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.

Setup

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 7 with Oracle Database

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

Summary

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.

Indentation

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):
    print(i)
print('done')

But this indentation prints 'done' in each iteration:

for i in range(5):
    print(i)
    print('done')

Strings

Python strings can be enclosed in single or double quotes:

'A string constant'
"another constant"

Multi line strings use a triple-quote syntax:

"""
SELECT *
FROM EMP
"""

Variables

Variables do not need types declared:

count = 1
ename = 'Arnie'

Comments

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
comment
"""

Printing

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.

print(a3[0])
print(a3[-1])
print(a3[1:3])

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,)"

Objects

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.

a3.append(23)

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 sal > 900000:
    print('Salary is way too big')
elif sal > 500000:
    print('Salary is huge')
else:
    print('Salary might be OK')

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

Loops

A traditional loop is:

for i in range(10):
    print(i)

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:
    print(v)

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

Functions

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:

print(myfunc.__doc__)

Modules

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.

Resources

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