Read Write CLOB, BLOB Data from Oracle to MySql using Python

Read Write CLOB, BLOB Data from Oracle to MySql using Python

Preface

Ok friends, considering COVID-19 entire IT industry went on cost cutting and same I recently experienced in my Organization where I was asked to migrate from Oracle to MySQL. When I took this activity in my hand, I analyzed from root and came to know that while transferring data from Oracle to MySql is going to a bit challenging mainly for CLOB or BLOB data types as they are big in size.

Considering Python a legitimate language which works silently and having enrich libraries and Open Source too, I started research on it and finally build something which will help others to move from Oracle to MySql.

Prerequisite

  • User should have appropriate Oracle Client installed on the machine from which the script needs to be executed.
  • cx_Oracle package needs to be imported
  • mysql connector needs to be imported

Approach

In given code we will fetch all records from a table having BLOB/CLOB, Push into MySql database. Following is the code script which has to be run in .py file. Please change database credentials as per configuration:

#Importing references
import mysql.connector
import cx_Oracle
import pandas as pd
from mysql.connector import Error
from pandas import DataFrame

#open Oracle Connection
dsn_tns = cx_Oracle.makedsn('Host IP or Host name', 'Port Number', service_name='name of your service')
conn = cx_Oracle.connect(user='DB User', password='DB Password', dsn=dsn_tns)

#Open cursor
cursor = conn.cursor()

#buidling sql statement to select records from Oracle
sql = "SELECT * FROM ORACLE_BLOB_TABLE"

#Executing query
cursor.execute(sql)

#Create MySql INSERT statement
mySqlQury = "INSERT INTO ORACLE_BLOB_TABLE (COLUMN1, COLUMN2) VALUES(:COLUMN1, :COLUMN2)"

#Open mysql connection
mydb = mysql.connector.connect(user='mysql user', password='mysql db password', port='port number', host='MySql Host', database='database name', auth_plugin='caching_sha2_password')

#Loop on list of records from Oracle and push into the database
for row in cursor:
	mCursor = mydb.cursor(mySqlQury, row[0],row[1])
	mCursor.execute()
	mydb.close()
#Closing oracle connection
conn.close()
print("Data pupulated")

Next: Transfer data from Oracle to MySql using sqlalchemy Python

Leave a Reply

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