Data migration from Oracle to mySQL

Data migration: Oracle to MySQL.

Since good data is currently being termed as the fuel of businesses as it allows organizations to establish decisions and goals to keep forward. But how about moving this data (fuel) from one system to another. While this might sound simple and straightforward, it involves lots of technical inputs.

So, am at an engagement and a colleague addresses a technical issue of migrating data from an ORACLE database to the MySQL database as the data warehouse. This colleague finds his procedure so manual with branching this data to an excel file then appends it to the MySQL database.

As I get home after this discussion my brains spin to this issue and I get thinking of a way this can be automated with just a single connection.

So excited to share my solution guys, it actually worked...

Before moving further, you may need to make sure you have the following in place:-

• Credentials to connect to a MySQL database.

• A python IDE, maybe a Jupyter notebook to install some python libraries.

• Credentials to connect to an Oracle database.

So with this in place, I developed a python code to transport this data from Oracle to MySQL with just a few additional python libraries installed like cx_Oracle, sqlalchemy, pandas and mysql.connector.

Let's see the program now.

#Import libraries

import cx_Oracle

import pandas as pd

from sqlalchemy import create_engine

#Set Oralce Connection

dsn_tns = cx_Oracle.makedsn('host address', 'port number', service_name='XE')

conn = cx_Oracle.connect(user='XE', password='1234', dsn=dsn_tns)

#Open cursor

cursor = conn.cursor()

#buidling sql statement to select records from Oracle

sql = "SELECT * FROM CUSTOMER"

   

#read data into dataframe directly

data=pd.read_sql(sql,conn)

print("Total records form Oracle : ", data.shape[0])

data.head()

#connecting to the mysql database

import mysql.connector

mydb = mysql.connector.connect(

    host="localhost",

    user="root",

    password="1234",

    database="db")

mycursor = mydb.cursor()

#Writing the data to the mysql database

sql ='insert into customer values(%s, %s, %s, %s, %s)'

df_list = data.values.tolist()

n = 0

for i in data.iterrows():

    mycursor.execute(sql,df_list[n])

    n += 1

mydb.commit()

mydb.close()

Hope this helps solve your challenge...

DATA, THE NEW OIL.

arglytics01@gmail.com