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