""" Infrastructure base de données — initialisation et schéma (MySQL). Ce fichier n'est PAS un outil de l'agent : il fournit setup_db() et DB_SCHEMA utilisés au démarrage et dans les prompts de l'orchestrateur. L'outil d'interrogation SQL est dans tools/query_db.py. Tables gérées : - mutations : transactions DVF 2021-2025, département 59 (Nord) Peuplée par build_mutations_iris.py (inclut code_iris, lat/lon) - loyers : loyers observés 2025 (Base_OP_2025_Nationale.csv) - prix_evolution_iris: prix médian par IRIS / type / année (build_mutations_iris.py) """ from __future__ import annotations import csv import logging import os from domain.core.mysql_db import get_connection logger = logging.getLogger("tools.database") _ROOT = os.path.dirname(os.path.dirname(os.path.dirname(__file__))) CSV_LOYERS = os.path.join(_ROOT, "data", "Base_OP_2025_Nationale.csv") # ── helpers ─────────────────────────────────────────────────────────────────── def _float(value: str) -> float | None: v = value.strip().replace(",", ".") try: return float(v) if v else None except ValueError: return None def _int(value: str) -> int | None: v = value.strip() try: return int(v) if v else None except ValueError: return None def _table_exists(cur, table: str) -> bool: cur.execute( "SELECT 1 FROM information_schema.tables" " WHERE table_schema=DATABASE() AND table_name=%s", (table,), ) return cur.fetchone() is not None def _column_names(cur, table: str) -> set[str]: cur.execute( "SELECT COLUMN_NAME FROM information_schema.columns" " WHERE table_schema=DATABASE() AND table_name=%s", (table,), ) return {row["COLUMN_NAME"] for row in cur.fetchall()} # ── table mutations (DVF Nord 2021-2025) ───────────────────────────────────── _DDL_MUTATIONS = """\ CREATE TABLE mutations ( id INT NOT NULL AUTO_INCREMENT, id_mutation VARCHAR(50), annee INT, date_mutation VARCHAR(20), valeur_fonciere DOUBLE, adresse_numero VARCHAR(20), adresse_nom_voie VARCHAR(200), code_postal VARCHAR(10), code_commune VARCHAR(10), nom_commune VARCHAR(100), code_departement VARCHAR(5), type_local VARCHAR(20), surface_reelle_bati DOUBLE, prix_m2 DOUBLE, latitude DOUBLE, longitude DOUBLE, code_iris VARCHAR(10), nom_iris VARCHAR(100), PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\ """ def _setup_mutations(cur) -> None: if _table_exists(cur, "mutations"): cols = _column_names(cur, "mutations") if "code_iris" in cols: cur.execute("SELECT COUNT(*) AS n FROM mutations") count = cur.fetchone()["n"] logger.info( "Table 'mutations' déjà enrichie IRIS (%d lignes) — non réinitialisée.", count ) return cur.execute("DROP TABLE IF EXISTS mutations") cur.execute(_DDL_MUTATIONS) logger.warning( "Table 'mutations' créée vide. " "Exécutez 'python build_mutations_iris.py' pour charger les données DVF 2021-2025." ) # ── table loyers (Observatoire des Loyers) ──────────────────────────────────── _DDL_LOYERS = """\ CREATE TABLE loyers ( id INT NOT NULL AUTO_INCREMENT, annee INT, agglomeration VARCHAR(200), zone VARCHAR(50), type_habitat VARCHAR(20), epoque_construction VARCHAR(50), anciennete_locataire VARCHAR(50), nombre_pieces VARCHAR(30), loyer_m2_moyen DOUBLE, loyer_m2_median DOUBLE, loyer_m2_q1 DOUBLE, loyer_m2_q3 DOUBLE, loyer_mensuel_moyen DOUBLE, loyer_mensuel_median DOUBLE, loyer_mensuel_q1 DOUBLE, loyer_mensuel_q3 DOUBLE, surface_moyenne DOUBLE, nombre_observations INT, nombre_logements INT, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\ """ def _setup_loyers(cur) -> None: if not os.path.exists(CSV_LOYERS): logger.warning( "Fichier CSV loyers introuvable : %s — table 'loyers' non rechargée.", CSV_LOYERS ) return cur.execute("DROP TABLE IF EXISTS loyers") cur.execute(_DDL_LOYERS) count = skipped = 0 with open(CSV_LOYERS, encoding="latin-1", newline="") as f: for row in csv.DictReader(f): if not row.get("loyer_moyen") and not row.get("moyenne_loyer_mensuel"): skipped += 1 continue cur.execute( """INSERT INTO loyers (annee, agglomeration, zone, type_habitat, epoque_construction, anciennete_locataire, nombre_pieces, loyer_m2_moyen, loyer_m2_median, loyer_m2_q1, loyer_m2_q3, loyer_mensuel_moyen, loyer_mensuel_median, loyer_mensuel_q1, loyer_mensuel_q3, surface_moyenne, nombre_observations, nombre_logements) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", ( _int(row.get("Data_year", "")), row.get("agglomeration", "").strip() or None, row.get("Zone_complementaire", "").strip() or None, row.get("Type_habitat", "").strip() or None, row.get("epoque_construction_homogene", "").strip() or None, row.get("anciennete_locataire_homogene", "").strip() or None, row.get("nombre_pieces_homogene", "").strip() or None, _float(row.get("loyer_moyen", "")), _float(row.get("loyer_median", "")), _float(row.get("loyer_1_quartile", "")), _float(row.get("loyer_3_quartile", "")), _float(row.get("moyenne_loyer_mensuel", "")), _float(row.get("loyer_mensuel_median", "")), _float(row.get("loyer_mensuel_1_quartile","")), _float(row.get("loyer_mensuel_3_quartile","")), _float(row.get("surface_moyenne", "")), _int(row.get("nombre_observations", "")), _int(row.get("nombre_logements", "")), ), ) count += 1 logger.info( "Table 'loyers' : %d lignes chargées depuis Base_OP_2025_Nationale.csv%s.", count, f" ({skipped} ignorées)" if skipped else "", ) # ── point d'entrée public ───────────────────────────────────────────────────── def setup_db() -> None: """ Crée (ou recrée) les tables depuis leurs fichiers CSV respectifs. Peut être appelée plusieurs fois sans risque. """ conn = get_connection() cur = conn.cursor() try: _setup_mutations(cur) _setup_loyers(cur) conn.commit() finally: conn.close() # ── schéma injecté dans le prompt de l'orchestrateur ───────────────────────── DB_SCHEMA = """ Quatre tables sont disponibles dans la base de données : ═══════════════════════════════════════════════════════════════ TABLE "mutations" (source : DVF géocodé 2021-2025, département 59 — Nord) Transactions de vente immobilières résidentielles (Maison / Appartement) Peuplée par build_mutations_iris.py — inclut la jointure spatiale IRIS ═══════════════════════════════════════════════════════════════ - id INT : identifiant unique - id_mutation TEXT : identifiant DVF de la mutation - annee INT : année de transaction (2021 à 2025) - date_mutation TEXT : date de transaction (YYYY-MM-DD) - valeur_fonciere DOUBLE : prix de vente en euros - adresse_numero TEXT : numéro de rue (peut être NULL) - adresse_nom_voie TEXT : nom de la voie - code_postal TEXT : ex. 59000 - code_commune TEXT : code INSEE commune (5 chiffres) - nom_commune TEXT : nom de la commune - code_departement TEXT : 59 - type_local TEXT : "Appartement" | "Maison" - surface_reelle_bati DOUBLE : surface bâtie en m² - prix_m2 DOUBLE : prix au m² calculé (valeur / surface) - latitude DOUBLE : latitude WGS84 - longitude DOUBLE : longitude WGS84 - code_iris TEXT : code IRIS 9 chiffres (NULL si hors périmètre) - nom_iris TEXT : nom du quartier IRIS (NULL si hors périmètre) Communes couvertes : métropole lilloise et département Nord (59) Filtres appliqués : ventes uniquement, surface > 0, prix/m² ∈ [500-12 000 €], valeur ∈ [10 000-5 000 000 €], coordonnées GPS valides ═══════════════════════════════════════════════════════════════ TABLE "prix_evolution_iris" (agrégat DVF 2021-2025 par IRIS) Prix médian au m² par IRIS / type de bien / année Peuplée par build_mutations_iris.py ═══════════════════════════════════════════════════════════════ - id INT : identifiant unique - code_iris TEXT : code IRIS 9 chiffres - nom_iris TEXT : nom du quartier IRIS - nom_commune TEXT : nom de la commune - type_local TEXT : "Appartement" | "Maison" - annee INT : année (2021 à 2025) - nb_transactions INT : nombre de ventes retenues pour le calcul - prix_m2_median DOUBLE : prix médian au m² en euros - evolution_m2_pct DOUBLE : évolution % du prix médian vs année précédente (NULL pour la première année disponible par IRIS) Seuil minimum : 3 transactions par (IRIS, type, année) pour figurer dans la table. ═══════════════════════════════════════════════════════════════ TABLE "prix_evolution" (agrégat DVF 2021-2025 par commune) Prix médian au m² par commune / code postal / type de bien / année Peuplée par build_prix_evolution.py (17 villes métropole lilloise) ═══════════════════════════════════════════════════════════════ - id INT : identifiant unique - annee INT : année (2021 à 2025) - code_postal TEXT : code postal - nom_commune TEXT : nom de la commune - type_local TEXT : "Appartement" | "Maison" - nb_transactions INT : nombre de ventes retenues - prix_m2_moyen DOUBLE : prix moyen au m² en euros - prix_m2_median DOUBLE : prix médian au m² en euros - evolution_m2_pct DOUBLE : évolution % du médian vs année précédente (NULL si 1ère année) - prix_moyen DOUBLE : prix total moyen en euros - prix_median DOUBLE : prix total médian en euros - surface_moyenne DOUBLE : surface moyenne en m² Villes : Wasquehal, Marcq-en-Barœul, Lille, Croix, Roubaix, Tourcoing, Villeneuve-d'Ascq, Lezennes, Mons-en-Barœul, Hem, Saint-André-lez-Lille, Mouvaux, Lambersart, Marquette-lez-Lille, Wambrechies, Linselles, Quesnoy-sur-Deûle ═══════════════════════════════════════════════════════════════ TABLE "loyers" (source : Observatoire des Loyers 2025, Toulouse) Loyers observés — Agglomération de Toulouse ═══════════════════════════════════════════════════════════════ - id INT : identifiant unique - annee INT : 2025 - agglomeration TEXT : "Agglomération de Toulouse" - zone TEXT : "Ville centre" | "Périphérie" | NULL (toute l'agglomération) - type_habitat TEXT : "Appartement" | "Maison" | NULL (ensemble) - epoque_construction TEXT : "1. Avant 1946" | … | NULL (toutes époques) - anciennete_locataire TEXT : "1. Moins de 1 an" | "2. 1 an et plus" | NULL (toutes) - nombre_pieces TEXT : "Appart 1P"|"Appart 2P"|"Appart 3P"|"Appart 4P+" "Maison 1-3P"|"Maison 4P+" NULL (tous types) - loyer_m2_moyen DOUBLE : loyer moyen en €/m² - loyer_m2_median DOUBLE : loyer médian en €/m² - loyer_m2_q1 DOUBLE : 1er quartile du loyer €/m² - loyer_m2_q3 DOUBLE : 3e quartile du loyer €/m² - loyer_mensuel_moyen DOUBLE : loyer mensuel moyen en € - loyer_mensuel_median DOUBLE : loyer mensuel médian en € - loyer_mensuel_q1 DOUBLE : 1er quartile loyer mensuel en € - loyer_mensuel_q3 DOUBLE : 3e quartile loyer mensuel en € - surface_moyenne DOUBLE : surface moyenne des logements en m² - nombre_observations INT : nombre de baux observés - nombre_logements INT : parc locatif estimé """