• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Mister PKI

SSL Certificates * SSL Tools * Certificate Decoder

  • Buy SSL Certificates
  • Blog
  • OpenSSL
  • Keytool
  • SSL Tools
  • Donate

Python Database Connector

April 14, 2022 by Mister PKI Leave a Comment

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.

docker,  python

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

PKCS12

openssl s_client

Keytool

Keytool list

ECDSA vs RSA

OpenSSL

PKCS7

Certificate Decoder

Training Courses

Top online courses in IT & Software

Cyber Security Training

Udemy - The Complete Internet Security Privacy Course icon

Buy SSL Certificates

The SSL Store

Comodo Store

Sectigo Store

RapidSSL

Recent Posts

  • pfx password
  • pkcs12
  • Sendmail vs Postfix – Mail Transfer Agent Comparison
  • Python mock datetime now
  • Python get SSL Certificate

Footer

  • Twitter
  • YouTube

Pages

  • About Mister PKI
  • Blog
  • Compare and Buy Affordable PKI Certificates
  • Contact Us
  • Full Disclosure
  • Privacy Policy
  • SSL Tools – Certificate Decoder and Certificate Checker

Copyright © 2022