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

by darrion.kuhn , in category: MySQL , 4 months ago

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

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

Member

by lottie , 4 months ago

@darrion.kuhn 

To migrate/copy PostgreSQL tables to Oracle using Python, you can use the psycopg2 library to connect to both databases and fetch data from PostgreSQL, and then use the cx_Oracle library to insert the data into Oracle.


Here is an example script that demonstrates how you can migrate 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()


Make sure to replace the placeholder values with your actual database credentials. Also, ensure that you have the relevant libraries (psycopg2 and cx_Oracle) installed before running the script.