Transfer Data from Oracle to MySql using sqlalchemy Python

Transfer Data from Oracle to MySql using sqlalchemy Python

sqlalchemy

Is a rich and open source library which facilitates developer with many data transfer options like schema to schema migration, table to table transfer etc. In this code example we will be using followings to achieve dynamic data transfer from Oracle to MySql (note this code will not work for CLOB/BLOB data transfer. If you want to transfer CLOB/BLOB data from Oralce to Myql please refer my blog “Transfer CLOB/BLOB data from Oracle to MySql using Python“):

  • cl_Oralce: Oracle library to connect Oracle Database in Python
  • pandas: to build DataFrame to transport data
  • sqlalchemy : to push data into MySql database

Code Example

#Import libraries
import cx_Oracle
import pandas as pd
from sqlalchemy import create_engine

#Set Oralce 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"

#read data into dataframe directly
data=pd.read_sql(sql,conn)

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

#Create sqlalchemy engine
engine = create_engine("mysql+pymysql://mysql_user:mysql_password@host_name:PORT/Schema_Name")
data.to_sql("Table name", con = engine, if_exists = 'append', index = False, chunksize =10000)

print("Data pushed success")

#close connection
conn.close()

Next: Install Python Libraries with Trusted host

One thought on “Transfer Data from Oracle to MySql using sqlalchemy Python

Leave a Reply

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