Python Database Connector

The purpose of this article is to demonstrate how to use a Python Database Connector to both read and write data to an Oracle database. The concept is the same for any Database Management System (DBMS) you are using, but the drivers will be different. This article will go over the steps required to install an oracle driver to be used by python for database programming.

Additionally, Docker will be used for this exercise. Docker will provide a repeatable and self documenting process for configuring the Oracle database driver to be used by python.

Dockerfile from python image

Create a Dockerfile from a python image of your choice. For this example we are using the python:3.11.0a6-bullseye image. It can be found here: https://hub.docker.com/_/python

The contents of the Dockerfile should be the following:

FROM python:3.11.0a6-bullseye
RUN apt-get update && apt-get install -y libaio1 wget unzip
WORKDIR /opt/oracle
RUN wget https://download.oracle.com/otn_software/linux/instantclient/instantclient-basiclite-linuxx64.zip && \
    unzip instantclient-basiclite-linuxx64.zip && rm -f instantclient-basiclite-linuxx64.zip && \
    cd /opt/oracle/instantclient* && rm -f *jdbc* *occi* *mysql* *README *jar uidrvci genezi adrci && \
    echo /opt/oracle/instantclient* > /etc/ld.so.conf.d/oracle-instantclient.conf && ldconfig
RUN apt-get -y update && python3 -m pip install cx_Oracle --upgrade

Make sure to install the python cx_Oracle package after the driver or your code will fail with an error stating the cx_Oracle module could not be loaded.

Create a database connection in Python

The following code makes an Oracle database connection.

cx_Oracle.connect parameters:

1. user – The username of the account making the connection
2. password – The corresponding password to the username
3. dsn – The data source name. For example, hostname:sid (example.com:proddb)

If the connection could not be made, raise a DatabaseError when an exception occurs.

import cx_Oracle
def create_connection(self, user, password, dsn):
    try:
        return cx_Oracle.connect(user=user, password=password, dsn=dsn, encoding="UTF-8")
    except cx_Oracle.DatabaseError as e:
        print("Could not connect to the database", e)
        raise e

Query a database in Python

After making the Oracle database connection you are ready to query or write to the database.

Query one record

The following code will return the first result returned and ignore the rest.

1. Create a cursor with the connection created in the previous example.
2. Use the cursor to execute the given query with the given parameters.
3. Use the fetchone method to fetch only one result.
4. If an exception occurs, raise it.
5. Close the cursor.

def fetch_one(self, connection, query, params):
    cursor = connection.cursor()
    try:
        cursor.execute(query, params)
        row = cursor.fetchone()
        return row
    except cx_Oracle.DatabaseError as e:
        print("Error running query", e)
        raise e
    finally:
        if cursor:
            cursor.close()

To call this method:

fetch_one(connection, 'select * from dual where 1=:number', {'number': 1})

Query all records

The following code will return all results for the given query.

 def fetch_all(self, connection, query, params):
    cursor = connection.cursor()
    try:
        cursor.execute(query, params)
        rows = cursor.fetchall()
        return rows
    except cx_Oracle.DatabaseError as e:
        print("Error running query", e)
        raise e
    finally:
        if cursor:
            cursor.close()

1. Create a cursor with the connection created in the previous example.
2. Use the cursor to execute the given query with the given parameters.
3. Use the fetchall method to fetch all of the results.
4. If an exception occurs, raise it.
5. Close the cursor.

A list of tuples is returned.

Conclusion – Python Database Connector

In conclusion, this article has demonstrated how to use a Python Database Connector by using an Oracle driver and the cx_Oracle module to make database connections as well as read and write data. Let us know in the comments if you have any questions or would like to see additional examples.

Read more of our posts on Docker and Python.