Here is a simple example of a data pipeline that transfers data from a MySQL database to a PostgreSQL database using Python:
# Import necessary libraries
import mysql.connector
import psycopg2
# Connect to the MySQL database
mydb = mysql.connector.connect(
host="mysql_host",
user="mysql_user",
password="mysql_password",
database="mysql_database"
)
# 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 MySQL database
mycursor = mydb.cursor()
# Create a cursor to perform operations on the PostgreSQL database
pgcursor = pgdb.cursor()
# Execute a MySQL query to retrieve the data
mycursor.execute("SELECT * FROM mysql_table")
# Fetch the result of the MySQL query
mysql_data = mycursor.fetchall()
# Loop through the result and insert each row into the PostgreSQL table
for row in mysql_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
mycursor.close()
pgcursor.close()
mydb.close()
pgdb.close()
This script uses the mysql-connector-python library to connect to the MySQL database, and the psycopg2 library to connect to the PostgreSQL database. It then retrieves the data from the MySQL table and inserts it into the PostgreSQL table. You can modify this script as needed for your specific databases and use case.
