Connecting to Oracle Database in Python

Summary: in this tutorial, you will learn how to connect to the Oracle Database in Python using stand-alone or pooled connections.

Installing the cx_Oracle module

To install the cx_Oracle module on Windows, you use the following command:

python -m pip install cx_Oracle --upgradeCode language: Python (python)

On MacOS or Linux you use python3 instead of python:

python3 -m pip install cx_Oracle --upgradeCode language: Python (python)

You can connect to Oracle Database using cx_Oracle in two ways: standalone and pooled connections.

The standalone connections are useful when the application has a single user session to the Oracle database while the collection pooling is critical for performance when the application often connects and disconnects from the database.

Before diving into each method, let’s create a module config.py to store the Oracle database’s configuration:

username = 'OT'
password = '<password>'
dsn = 'localhost/pdborcl'
port = 1512
encoding = 'UTF-8'Code language: Python (python)

In this module, the dsn has two parts the server (localhost) and the pluggable database (pdborcl)

If the Oracle Database runs on the example.com, you use the following dsn:

dsn = 'example.com/pdborcl'Code language: Python (python)

Creating standalone connections

To create a standalone connection, you use the cx_Oracle.connect() method or cx_Oracle.Connection().

The following connect.py shows how to create a new connection to the Oracle Database:

import cx_Oracle
import config

connection = None
try:
    connection = cx_Oracle.connect(
        config.username,
        config.password,
        config.dsn,
        encoding=config.encoding)

    # show the version of the Oracle Database
    print(connection.version)
except cx_Oracle.Error as error:
    print(error)
finally:
    # release the connection
    if connection:
        connection.close()
Code language: Python (python)

Let’s examine the code in detail:

First, import the cx_Oracle package:

import cx_Oracle
Code language: Python (python)

and the config package created previously

import configCode language: Python (python)

Second, create a connection by using the cx_Oracle.connect() method:

connection = cx_Oracle.connect(
                config.username,
                config.password,
                config.dsn,
                encoding=config.encoding)
Code language: Python (python)

Third, the try..catch block handles exceptions if they occur. If the connection is established successfully, the following code will execute to display the Oracle Database’s version:

print(connection.version)Code language: Python (python)

Finally, release the connection once it is no longer used by calling the Connection.close() method:

if connection:
    connection.close()Code language: Python (python)

Alternatively, you can let Python automatically close the connection when the reference to the connection goes out of scope by using the with block:

import cx_Oracle
import config

try:
    with cx_Oracle.connect(
            config.username,
            config.password,
            config.dsn,
            encoding=config.encoding) as connection:
    # show the version of the Oracle Database
    print(connection.version)
except cx_Oracle.Error as error:
    print(error)
Code language: Python (python)

Creating pooled connections

The cx_Oracle‘s connection pooling allows applications to create and maintain a pool of connections to the Oracle database.

Internally, the cx_Oracle implements the connection pool using Oracle’s session pool technology. In general, each connection in a cx_Oracle connection pool corresponds to one session in the Oracle Database.

To create pooled connections, you use the cx_Oracle.SessionPool() method. The following connect_pool.py illustrates how to create pooled connections:

import cx_Oracle
import config

# Create the session pool
pool = cx_Oracle.SessionPool(
    config.username,
    config.password,
    config.database,
    min=100,
    max=100,
    increment=0,
    encoding=config.encoding
)

# Acquire a connection from the pool
connection = pool.acquire()

# Use the pooled connection
print('Using the connection')

# Release the connection to the pool
pool.release(connection)

# Close the pool
pool.close()
Code language: Python (python)

In this example:

First, import the cx_Oracle and config modules.

Second, use the cx_Oracle.SessionPool() method to create a connection pool.

The min and max are the read-only attributes that return the minimum and maximum number of sessions that the session pool can control.

The increment is a read-only attribute that returns the number of sessions that will be established when additional sessions need to be created.

It is a good practice to use a fixed-sized pool (min and max have the same values and increment equals zero).

Third, acquire a connection from the connection pool by using the SessionPool.acquire() method.

Fourth, use the connection for executing the query.

Fifth, release the connection to the pool once the connection is no longer used by using the SessionPool.release() method.

Finally, close the pool by calling the SessionPool.close() method.

In this tutorial, you have learned how to create standalone and pooled connections to the Oracle Database from a Python program.