Here is a simple example of a data pipeline that transfers data from an Oracle database to a PostgreSQL database using Python:
# Import necessary libraries
import cx_Oracle
import psycopg2
# Connect to the Oracle database
orcl = cx_Oracle.connect(
user="oracle_user",
password="oracle_password",
dsn="oracle_host:oracle_port/oracle_service"
)
# Connect to the PostgreSQL database
pgdb = psycopg2.connect(
host="postgres_host",
user="postgres_user",
password="postgres_password",
database="postgres_database"
)
# Create a cursor to perform operations on the Oracle database
orclcursor = orcl.cursor()
# Create a cursor to perform operations on the PostgreSQL database
pgcursor = pgdb.cursor()
# Execute an Oracle query to retrieve the data
orclcursor.execute("SELECT * FROM oracle_table")
# Fetch the result of the Oracle query
oracle_data = orclcursor.fetchall()
# Loop through the result and insert each row into the PostgreSQL table
for row in oracle_data:
pgcursor.execute("INSERT INTO postgres_table VALUES (%s, %s, %s)", row)
# Commit the changes to the PostgreSQL database
pgdb.commit()
# Close the cursors and databases
orclcursor.close()
pgcursor.close()
orcl.close()
pgdb.close()
This script uses the cx_Oracle library to connect to the Oracle database, and the psycopg2 library to connect to the PostgreSQL database. It then retrieves the data from the Oracle table and inserts it into the PostgreSQL table. You can modify this script as needed for your specific databases and use case.
