Python Automation: Connecting to Oracle Database using Cx Oracle

How I use Cx Oracle and a few other Python libraries to automate small ETL tasks

Image from Pexels

I’ve been using cx_Oracle Python extension module to access my Oracle Database and I was able to automate most of my small tasks because of it.

There are documentations available online provided by Oracle which can help you directly access Oracle database, but in this article I’ll be demonstrating how I use Cx Oracle and a few other python libraries to automate small ETL tasks from local computer to Oracle Database.

The goal is to write a python script that reads a file in a local folder, perform data cleaning and transformations, then insert into the Oracle Database. This python script file can then be scheduled via task scheduler so it can be run automatically on a set schedule. Extra logging codes will be added to keep track of what happens within the script.

Prerequisite:

Before you can write Cx Oracle in your python scripts, you need to install Oracle Client Libraries, when you’ve successfully installed those you’re all set and ready to write your python scripts. To begin the script I imported these libraries:

import pandas as pd
import cx_Oracle
import math
import logging