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()