import csv

with open('iris_data.csv', newline='', encoding='utf-8') as csvfile, \
     open('insert_districts.sql', 'w', encoding='utf-8') as sqlfile:
    reader = csv.DictReader(csvfile, delimiter=';')
    for row in reader:
        # Remplacer les valeurs vides par NULL pour SQL
        prix_appt = row['PRIX_MOYEN_APPT'] if row['PRIX_MOYEN_APPT'] else 'NULL'
        prix_maison = row['PRIX_MOYEN_MAISON'] if row['PRIX_MOYEN_MAISON'] else 'NULL'
        nb_transactions = row['NB_TRANSACTIONS'] if row['NB_TRANSACTIONS'] else 'NULL'
        data_year = row['ANNEE'] if row['ANNEE'] else 'NULL'

        sqlfile.write(
            f"""INSERT INTO districts (
                district_name,
                insee_code,
                latitude,
                longitude,
                avg_price_per_sqm_apartment,
                avg_price_per_sqm_house,
                number_of_transactions,
                data_year
            ) VALUES (
                '{row['NOM_IRIS'].replace("'", "''")}',
                '{row['COM']}',
                {row['LATITUDE']},
                {row['LONGITUDE']},
                {prix_appt},
                {prix_maison},
                {nb_transactions},
                {data_year}
            );\n"""
        )