How to migrate/copy postgresql tables to oracle using python?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

import psycopg2
import cx_Oracle

# Connect to PostgreSQL database
pg_conn = psycopg2.connect(
    database="your_postgres_db",
    user="your_pg_user",
    password="your_pg_password",
    host="localhost"
)
pg_cur = pg_conn.cursor()

# Connect to Oracle database
ora_conn = cx_Oracle.connect(
    "your_oracle_user/your_oracle_password@your_oracle_db"
)
ora_cur = ora_conn.cursor()

# Fetch tables from PostgreSQL
pg_cur.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='public'")
tables = pg_cur.fetchall()

# Migrate tables to Oracle
for table in tables:
    table_name = table[0]
    pg_cur.execute(f"SELECT * FROM {table_name}")
    rows = pg_cur.fetchall()
    
    # Insert data into Oracle
    for row in rows:
        ora_cur.execute(f"INSERT INTO {table_name} VALUES {row}")
        
ora_conn.commit()

# Close connections
pg_cur.close()
pg_conn.close()

ora_cur.close()
ora_conn.close()