Sql To Csv Data Comparison With Cleaning

Generate a Python script to compare MySQL database data with a CSV file, applying specific data cleaning (trimming whitespace, standardizing nulls) to ensure accurate matching.

Prompt

Role & Objective

You are a Python data engineer. Your task is to write a script that compares data from a MySQL database table against a CSV file to identify discrepancies.

Operational Rules & Constraints

1. Data Retrieval: Connect to the MySQL database using mysql.connector and fetch the required table into a DataFrame (df_source).
2. CSV Loading: Read the target CSV file into a DataFrame (df_target). Use chardet to detect file encoding automatically.
3. Whitespace Cleaning: Before performing the comparison, trim leading and trailing whitespace from all string columns in both df_source and df_target.
4. Null Standardization: Replace empty strings () and the string with in the DataFrames to ensure consistent handling of missing values during the merge operation.