Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
2.4 kB
2
Indexable
Never
import cx_Oracle
import pandas as pd

# Database connection details
source_connection = cx_Oracle.connect("source_user/source_password@source_db")
target_connection = cx_Oracle.connect("target_user/target_password@target_db")
ppm_pc_connection = cx_Oracle.connect("ppm_pc_user/ppm_pc_password@ppm_pc_db")

# Fetch table names from the etl_pmpc table
table_name_query = "SELECT eprm_table_name FROM etl_pmpc"
table_name_df = pd.read_sql(table_name_query, ppm_pc_connection)

# Iterate through each table name
for index, row in table_name_df.iterrows():
    table_name = row['eprm_table_name']

    # Fetch data from the source and target tables into Pandas DataFrames
    source_query = f"SELECT * FROM {table_name}"
    source_df = pd.read_sql(source_query, source_connection)

    target_query = f"SELECT * FROM {table_name}"
    target_df = pd.read_sql(target_query, target_connection)

    # Ensure both DataFrames have the same number of rows
    if len(source_df) != len(target_df):
        print(f"Error: {table_name} in Source and Target databases have different row counts.")
    else:
        for index, (source_row, target_row) in enumerate(zip(source_df.iterrows(), target_df.iterrows())):
            source_row_data = source_row[1]
            target_row_data = target_row[1]

            if not source_row_data.equals(target_row_data):
                # Generate an update query dynamically
                update_query = f"UPDATE {table_name} SET "
                column_updates = []

                for column_name, source_val in source_row_data.items():
                    target_val = target_row_data[column_name]
                    if source_val != target_val:
                        column_updates.append(f"{column_name} = '{source_val}'")

                update_query += ", ".join(column_updates)
                # You need to add a condition to specify which row to update, e.g., based on a unique identifier.

                # Execute the update query in the target database using cx_Oracle
                target_cursor = target_connection.cursor()
                target_cursor.execute(update_query)
                target_cursor.close()

        # Commit the changes to the target database
        target_connection.commit()
        print(f"Update completed successfully for {table_name}.")

# Close connections
source_connection.close()
target_connection.close()
ppm_pc_connection.close()