Contents

Overview

This tutorial is a primary guide on using Python with Oracle Database. It contains both beginner and advanced materials. Choose the content that interests you and your skill level. The tutorial has scripts to run and modify, and has suggested solutions.

Python is a popular general purpose dynamic scripting language. The python-oracledb driver provides Python APIs to access Oracle Database. It is an upgrade for the hugely popular cx_Oracle interface.

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 python-oracledb documentation.

The original copy of these instructions that you are reading is here.

Python-oracledb Architecture

The python-oracledb driver enables access to Oracle Database using either one of two modes. Both modes have comprehensive functionality supporting the Python Database API v2.0 Specification. By default, python-oracledb runs in a "thin" mode, which connects directly to Oracle Database. This mode does not need Oracle Client libraries. However, some additional features are available when python-oracledb uses them. Python-oracledb applications that load the Oracle Client libraries via an application script runtime option are said to be in "thick" mode. This tutorial has examples in both modes.

Python python-oracledb architecture

The database can be on the same machine as Python, or it can be remote.

Setup

1. Connecting to Oracle

You can connect from Python to a local, remote or cloud Oracle Database. Documentation link for further reading: Connecting to Oracle Database.

2. Connection Pooling

Connection pooling is important for performance when multi-threaded applications frequently connect and disconnect from the database. Pooling also gives the best support for Oracle's High Availability (HA) features. Documentation link for further reading: Connection Pooling.

3. Fetching Data

Executing SELECT queries is the primary way to get data from Oracle Database. Documentation link for further reading: SQL Queries.

4. Binding Data

Bind variables enable you to re-execute statements with new data values without the overhead of re-parsing the statement. Binding improves code reusability, improves application scalability, and can reduce the risk of SQL injection attacks. Using bind variables is strongly recommended. Documentation link for further reading: Using Bind Variables.

5. PL/SQL

PL/SQL is Oracle's procedural language extension to SQL. PL/SQL procedures and functions are stored and run in the database. Using PL/SQL lets all database applications reuse logic, no matter how the application accesses the database. Many data-related operations can be performed in PL/SQL faster than extracting the data into a program (for example, Python) and then processing it. Documentation link for further reading: PL/SQL Execution.

6. Type Handlers

Type handlers enable applications to alter data that is fetched from, or sent to, the database. Documentation links for further reading: Changing Fetched Data Types with Output Type Handlers and Changing Bind Data Types using an Input Type Handler.

7. LOBs

Oracle Database "LOB" long objects can be streamed using a LOB locator, or worked with directly as strings or bytes. Documentation link for further reading: Using CLOB and BLOB Data.

8. Rowfactory functions

Rowfactory functions enable queries to return objects other than tuples. They can be used to provide names for the various columns or to return custom objects.

9. Subclassing connections and cursors

Subclassing enables application to "hook" connection and cursor creation. This can be used to alter or log connection and execution parameters, and to extend python-oracledb functionality. Documentation link for further reading: Application Tracing.

10. Python-oracledb Thick mode

All the above examples use python-oracledb in thin mode, but there are certain features which are only available in the thick mode of the python-oracledb driver. The upcoming sections show some of these. Note that you can also run all the earlier examples in thick mode by just changing the import line in examples from import db_config to import db_config_thick as db_config.

The following sections assume you have installed the tutorial schema as shown at the tutorial start.

The following sections are specific to the python-oracledb thick modes in this release of python-oracledb.

11. Scrollable cursors

Scrollable cursors enable python-oracledb thick mode applications to move backwards as well as forwards in query results. They can be used to skip rows as well as move to a particular row.

12. Binding named objects

Python-oracledb's thick mode can fetch and bind named object types such as Oracle's Spatial Data Objects (SDO).

The SDO definition includes the following attributes:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SDO_GTYPE                                          NUMBER
 SDO_SRID                                           NUMBER
 SDO_POINT                                          MDSYS.SDO_POINT_TYPE
 SDO_ELEM_INFO                                      MDSYS.SDO_ELEM_INFO_ARRAY
 SDO_ORDINATES                                      MDSYS.SDO_ORDINATE_ARRAY

13. Input and Output Type Handlers with named objects

Named objects can only be used in python-oracledb's thick mode. Documentation links for further reading: Changing Fetched Data Types with Output Type Handlers and Changing Bind Data Types using an Input Type Handler.

14. Advanced Queuing

Oracle Advanced Queuing (AQ) APIs usable in python-oracledb thick mode allow messages to be passed between applications. Documentation link for further reading: Oracle Advanced Queuing (AQ).

15. Simple Oracle Document Access (SODA)

Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs. Documents can be inserted, queried, and retrieved from Oracle Database. By default, documents are JSON strings. SODA APIs exist in many languages. It is usable in python-oracledb's thick mode. Documentation link for further reading: Simple Oracle Document Access (SODA).

Summary

In this tutorial, you have learned how to:

For further reading, see the python-oracledb 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:

On some machines, you may need to run the python3 command instead of python.

When you run scripts, Python automatically creates bytecode versions of them in a folder called __pycache__. These improve the 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 tutorial 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 can be single line:

# a short comment

Or 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 v == 2 or v == 4:
    print('Even')
elif v == 1 or v == 3:
    print('Odd')
else:
    print('Unknown number')

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


License

Copyright © 2017, 2022, Oracle and/or its affiliates.

This software is dual-licensed to you under the Universal Permissive License (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License 2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose either license.

If you elect to accept the software under the Apache License, Version 2.0, the following applies:

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.