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