Untitled

mail@pastecode.io avatar
unknown
plain_text
5 months ago
2.5 kB
2
Indexable
Never
# ... (previous code)

for index, source_row in source_df.iterrows():
    pk_value = source_row[pk]

    # Check if the primary key exists in the target DataFrame
    if pk_value not in target_df[pk].values:

        # Generate an INSERT query dynamically
        insert_query = f"INSERT INTO {schema_ext}.{table_name} ("
        insert_columns = []
        insert_values = []

        for column_name, source_val in source_row.items():
            if source_val is not None:
                if isinstance(source_val, str) and source_val.startswith('TO_DATE'):
                    # If it already starts with TO_DATE, don't add TO_DATE again
                    insert_values.append(source_val)
                elif is_datetime(source_val):
                    # Format datetime values using the appropriate function for the database type
                    if db_type_ext == 'ORACLE':
                        insert_values.append(f"TO_DATE('{source_val}', 'YYYY-MM-DD HH24:MI:SS')")
                    elif db_type_ext in ('MYSQL', 'MARIA'):
                        # For MariaDB, use STR_TO_DATE
                        insert_values.append(f"STR_TO_DATE('{source_val}', '%Y-%m-%d %H:%i:%s')")
                    else:
                        # Enclose other values in single quotes
                        insert_values.append(f"'{source_val}'")
                    insert_columns.append(column_name)  # Add the column name
                else:
                    # Replace 'NaT' with 'NULL' regardless of additional characters or formatting
                    if 'NaT' in str(source_val):
                        source_val = None  # Set to true NULL
                    # Enclose other values in single quotes
                    insert_values.append(f"'{source_val}'")
                    insert_columns.append(column_name)  # Add the column name
            else:
                insert_values.append('NULL')  # Insert a true NULL
                insert_columns.append(column_name)  # Add the column name

        # Construct the INSERT query with column names
        insert_query = f"INSERT INTO {schema_ext}.{table_name} ({', '.join(insert_columns)}) VALUES ({', '.join(insert_values)})"

        # Execute the INSERT query
        try:
            print(insert_query)
            cursor_ext.execute(insert_query)
            # connection_ext.commit()
        except Exception as e:
            logging.error(
                "Error - {} . Line No - {} ".format(str(e), str(sys.exc_info()[-1].tb_lineno)))

# ... (remaining code)