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 --upgrade
On MacOS or Linux you use
python3 instead of
python3 -m pip install cx_Oracle --upgrade
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'
In this module, the
dsn has two parts the server (
localhost) and the pluggable database (
If the Oracle Database runs on the
example.com, you use the following
dsn = 'example.com/pdborcl'
Creating standalone connections
To create a standalone connection, you use the
cx_Oracle.connect() method or
connect.py shows how to create a new connection to 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()
Let’s examine the code in detail:
First, import the
config package created previously
Second, create a connection by using the
connection = cx_Oracle.connect( config.username, config.password, config.dsn, encoding=config.encoding)
try..catch block handles exceptions if they occurs. If the connection is established successfully, the following code will execute to display the Oracle Database’s version:
Finally, release the connection once it is no longer used by calling the
if connection: connection.close()
Alternatively, you can let Python automatically closes the connection when the reference to the connection goes out of scope by using the
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)
Creating pooled connections
cx_Oracle‘s connection pooling allows applications to create and maintain a pool of connections to the Oracle database.
cx_Oracle implements the connection pool using the 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()
In this example:
First, import the
Second, use the
cx_Oracle.SessionPool() method to create a connection pool.
max are the read-only attributes that return the minimum and maximum number of sessions that the session pool can control.
increment is a read-only attribute which 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 (
max have the same values and
increment equals zero).
Third, acquire a connection from the connection pool by using the
Fourth, use the connection for executing query.
Fifth, release the connection to the pool once the connection is no longer used by using the
Finally, close the pool by calling the
In this tutorial, you have learned how to create standalone and pooled connections to the Oracle Database from a Python program.