Data transfer from Oracle To PostgreSQL via Pandas

 I needed to transfer data from Oracle to PostgreSQL, which are on different machines.
This time, I used Pandas and Pickle on Python 2.7.

Reading from Oracle:

import pickle

import cx_Oracle

import pandas as pd

def main():

conn = cx_Oracle.connect("ID/PW@DB")

tables = ["a","b"]

data = {}

for iTab in tables:

query = "select * from " + iTab

curDF = pd.read_sql(query, con=conn)

data[iTab] = curDF

conn.close()

with open("data.dat","wb") as f:

pickle.dump(data,f)

Writing to PostgreSQL:
(Note1, table name, schema and column names should be in lower case. Otherwise, you will find or need strange double quotation marks.
Note2, you should give chunksize. Otherwise, the python instance will eat up all the memory.
Note3, you need to set up schemas, but tables will be automatically created)

import pickle

import pandas as pd

from sqlalchemy import create_engine

def main():

with open("data.dat","rb") as f:

arrData = pickle.load(f)

engine = create_engine('postgresql://ID:PW@localhost:5432/server')

for iTab in arrData:

print(iTab)

arrData[iTab].to_sql(iTab.lower(), engine,schema="schema".lower(),chunksize=100)