spb/lou-ka Public
Lou·Ka — tous les logements à louer du Québec, un seul endroit.
HTML 99.7%
1# -----------------------------------------------------------------------------2# Lou-Ka — Agrégateur de logements à louer (province de Québec)3# Auteur : Simon-Pierre Boucher — contact@spboucher.ai4# db.py : persistance SQLite — upsert avec détection de changements,5# cycle de vie avec délai de grâce (2 syncs), détection de dérive,6# cache des pages détail, cache de géocodage.7# -----------------------------------------------------------------------------8from __future__ import annotations910import json11import sqlite312import statistics13import time14from pathlib import Path1516from .schema import Listing1718DB_PATH = Path(__file__).resolve().parent.parent / "data" / "louka.db"1920# Nombre d'exécutions consécutives où une annonce doit être absente de la21# source avant d'être désactivée (délai de grâce contre les ratés ponctuels).22MISS_GRACE = 22324# Dérive : si une source retourne <= DRIFT_RATIO × sa médiane historique25# (médiane >= DRIFT_MIN_BASE annonces), on alerte et on suspend les retraits.26DRIFT_RATIO = 0.2527DRIFT_MIN_BASE = 828DRIFT_HISTORY = 52930_SCHEMA = """31CREATE TABLE IF NOT EXISTS listings (32 uid TEXT PRIMARY KEY,33 source TEXT NOT NULL,34 external_id TEXT NOT NULL,35 url TEXT,36 title TEXT,37 address TEXT,38 sector TEXT,39 city TEXT,40 unit_type TEXT,41 price REAL,42 price_label TEXT,43 availability TEXT,44 availability_date TEXT,45 area_sqft REAL,46 pets TEXT,47 furnished INTEGER,48 description TEXT,49 amenities TEXT, -- JSON (liste de textes source)50 details TEXT, -- JSON (champs structurés : inclusions, parking…)51 images TEXT, -- JSON52 lat REAL,53 lng REAL,54 geocode_failed INTEGER DEFAULT 0,55 content_hash TEXT,56 first_seen REAL,57 last_seen REAL,58 updated_at REAL,59 miss_count INTEGER DEFAULT 0,60 active INTEGER DEFAULT 161);62CREATE INDEX IF NOT EXISTS idx_listings_source ON listings(source);63CREATE INDEX IF NOT EXISTS idx_listings_city ON listings(city);64CREATE INDEX IF NOT EXISTS idx_listings_active ON listings(active);6566CREATE TABLE IF NOT EXISTS sync_log (67 id INTEGER PRIMARY KEY AUTOINCREMENT,68 source TEXT,69 ts REAL,70 found INTEGER,71 added INTEGER,72 updated INTEGER,73 removed INTEGER,74 ok INTEGER,75 message TEXT,76 stats TEXT -- JSON : taux de champs null, missed, alerte…77);7879CREATE TABLE IF NOT EXISTS detail_cache (80 source TEXT NOT NULL,81 external_id TEXT NOT NULL,82 key TEXT, -- hash du contenu « liste » de l'annonce83 payload TEXT, -- JSON opaque propre au connecteur84 fetched_at REAL,85 PRIMARY KEY (source, external_id)86);8788CREATE TABLE IF NOT EXISTS price_log (89 uid TEXT NOT NULL,90 ts REAL NOT NULL,91 price REAL -- prix observé (NULL = retiré de l'affichage)92);93CREATE INDEX IF NOT EXISTS idx_price_log_uid ON price_log(uid);9495CREATE TABLE IF NOT EXISTS poi_cache (96 coord_key TEXT PRIMARY KEY, -- "lat,lng" arrondi à 4 décimales (~11 m)97 lat REAL,98 lng REAL,99 pois TEXT, -- JSON : [{cat, name, dist_m}] (plus proche/catégorie)100 ts REAL101);102103CREATE TABLE IF NOT EXISTS geocode_cache (104 address TEXT PRIMARY KEY, -- adresse normalisée (clé de cache)105 lat REAL,106 lng REAL,107 provider TEXT,108 failed INTEGER DEFAULT 0,109 ts REAL110);111"""112113# Colonnes ajoutées après la v1 — migration automatique des bases existantes.114_MIGRATIONS = {115 "listings": {116 "availability_date": "TEXT",117 "area_sqft": "REAL",118 "pets": "TEXT",119 "furnished": "INTEGER",120 "details": "TEXT",121 "geocode_failed": "INTEGER DEFAULT 0",122 "miss_count": "INTEGER DEFAULT 0",123 "dauid": "TEXT", # aire de diffusion 2021 (stats de quartier)124 "digest": "TEXT", # JSON louka/textmine.py (description structurée)125 },126 "sync_log": {127 "stats": "TEXT",128 },129}130131132def connect() -> sqlite3.Connection:133 DB_PATH.parent.mkdir(parents=True, exist_ok=True)134 con = sqlite3.connect(DB_PATH)135 con.row_factory = sqlite3.Row136 con.executescript(_SCHEMA)137 for table, cols in _MIGRATIONS.items():138 existing = {r["name"] for r in con.execute(f"PRAGMA table_info({table})")}139 for col, decl in cols.items():140 if col not in existing:141 con.execute(f"ALTER TABLE {table} ADD COLUMN {col} {decl}")142 con.commit()143 return con144145146# ---------------------------------------------------------------------------147# Synchronisation d'une source148# ---------------------------------------------------------------------------149150def _drift_alert(con: sqlite3.Connection, source: str, found: int,151 null_price_rate: float) -> str | None:152 """Détecte une dérive du connecteur (chute du volume ou des prix extraits).153154 Retourne un message d'alerte, ou None si tout est normal.155 """156 hist = con.execute(157 "SELECT found, stats FROM sync_log WHERE source=? AND ok=1"158 " ORDER BY ts DESC LIMIT ?", (source, DRIFT_HISTORY)).fetchall()159 if len(hist) < 3:160 return None161 med_found = statistics.median(r["found"] for r in hist)162 if med_found >= DRIFT_MIN_BASE and found <= DRIFT_RATIO * med_found:163 return (f"dérive: {found} annonce(s) trouvée(s) contre une médiane de "164 f"{med_found:.0f} — retraits suspendus, vérifier le connecteur")165 if found >= DRIFT_MIN_BASE and null_price_rate >= 0.8:166 rates = []167 for r in hist:168 try:169 rates.append(json.loads(r["stats"] or "{}")["null_price_rate"])170 except (KeyError, ValueError, TypeError):171 continue172 if rates and statistics.median(rates) <= 0.3:173 return (f"dérive: {null_price_rate:.0%} des annonces sans prix "174 f"(habituellement {statistics.median(rates):.0%}) — "175 "le format de la source a probablement changé")176 return None177178179def sync_source(con: sqlite3.Connection, source: str,180 listings: list[Listing]) -> dict:181 """Synchronise les annonces d'une source.182183 - nouvelle annonce -> insertion184 - annonce modifiée -> mise à jour (comparaison de content_hash)185 - annonce disparue -> miss_count += 1, puis active=0 après MISS_GRACE186 exécutions consécutives (délai de grâce)187 - dérive détectée -> alerte consignée, retraits suspendus188 """189 now = time.time()190 added = updated = 0191 seen_uids = set()192193 n = len(listings)194 null_price = sum(1 for l in listings if l.price is None)195 null_addr = sum(1 for l in listings if not l.address)196 null_price_rate = round(null_price / n, 3) if n else 0.0197198 alert = _drift_alert(con, source, n, null_price_rate)199200 for lst in listings:201 seen_uids.add(lst.uid)202 h = lst.content_hash()203 row = con.execute("SELECT content_hash, price FROM listings WHERE uid=?",204 (lst.uid,)).fetchone()205 params = dict(206 uid=lst.uid, source=lst.source, external_id=lst.external_id,207 url=lst.url, title=lst.title, address=lst.address,208 sector=lst.sector, city=lst.city, unit_type=lst.unit_type,209 price=lst.price, price_label=lst.price_label,210 availability=lst.availability,211 availability_date=lst.availability_date,212 area_sqft=lst.area_sqft, pets=lst.pets,213 furnished=(None if lst.furnished is None else int(lst.furnished)),214 description=lst.description,215 digest=(json.dumps(lst.digest, ensure_ascii=False)216 if getattr(lst, "digest", None) else None),217 amenities=json.dumps(lst.amenities, ensure_ascii=False),218 details=json.dumps(lst.details, ensure_ascii=False),219 images=json.dumps(lst.images, ensure_ascii=False),220 lat=lst.lat, lng=lst.lng, content_hash=h, now=now,221 )222 if row is None:223 con.execute(224 """INSERT INTO listings (uid, source, external_id, url, title,225 address, sector, city, unit_type, price, price_label,226 availability, availability_date, area_sqft, pets, furnished,227 description, digest, amenities, details, images, lat, lng,228 content_hash, first_seen, last_seen, updated_at,229 miss_count, active)230 VALUES (:uid,:source,:external_id,:url,:title,:address,231 :sector,:city,:unit_type,:price,:price_label,:availability,232 :availability_date,:area_sqft,:pets,:furnished,233 :description,:digest,:amenities,:details,:images,:lat,:lng,234 :content_hash,:now,:now,:now,0,1)""", params)235 if lst.price is not None: # prix initial = point de départ de l'historique236 con.execute("INSERT INTO price_log (uid, ts, price) VALUES (?,?,?)",237 (lst.uid, now, lst.price))238 added += 1239 elif row["content_hash"] != h:240 # COALESCE : ne jamais écraser des coordonnées géocodées par null241 con.execute(242 """UPDATE listings SET url=:url, title=:title,243 address=:address, sector=:sector, city=:city,244 unit_type=:unit_type, price=:price,245 price_label=:price_label, availability=:availability,246 availability_date=:availability_date,247 area_sqft=:area_sqft, pets=:pets, furnished=:furnished,248 description=:description, digest=:digest,249 amenities=:amenities, details=:details, images=:images,250 lat=COALESCE(:lat, lat), lng=COALESCE(:lng, lng),251 content_hash=:content_hash, last_seen=:now,252 updated_at=:now, miss_count=0, active=1253 WHERE uid=:uid""", params)254 if lst.price != row["price"]: # changement de prix -> historique255 con.execute("INSERT INTO price_log (uid, ts, price) VALUES (?,?,?)",256 (lst.uid, now, lst.price))257 updated += 1258 else:259 con.execute(260 "UPDATE listings SET last_seen=?, miss_count=0, active=1 WHERE uid=?",261 (now, lst.uid))262263 # Annonces de cette source qui n'apparaissent plus : délai de grâce,264 # puis désactivation. Suspendu si une dérive est détectée.265 removed = missed = 0266 if not alert:267 for r in con.execute(268 "SELECT uid, miss_count FROM listings WHERE source=? AND active=1",269 (source,)).fetchall():270 if r["uid"] in seen_uids:271 continue272 missed += 1273 if r["miss_count"] + 1 >= MISS_GRACE:274 con.execute(275 "UPDATE listings SET active=0, miss_count=?, updated_at=?"276 " WHERE uid=?", (r["miss_count"] + 1, now, r["uid"]))277 removed += 1278 else:279 con.execute("UPDATE listings SET miss_count=miss_count+1 WHERE uid=?",280 (r["uid"],))281282 stats = {283 "null_price_rate": null_price_rate,284 "null_address_rate": round(null_addr / n, 3) if n else 0.0,285 "missed": missed,286 }287 if alert:288 stats["alert"] = alert289 con.execute(290 "INSERT INTO sync_log (source, ts, found, added, updated, removed, ok,"291 " message, stats) VALUES (?,?,?,?,?,?,1,?,?)",292 (source, now, n, added, updated, removed, alert or "ok",293 json.dumps(stats, ensure_ascii=False)))294 con.commit()295 out = {"source": source, "found": n, "added": added,296 "updated": updated, "removed": removed}297 if alert:298 out["alert"] = alert299 return out300301302def log_failure(con: sqlite3.Connection, source: str, message: str) -> None:303 con.execute(304 "INSERT INTO sync_log (source, ts, found, added, updated, removed, ok, message)"305 " VALUES (?,?,0,0,0,0,0,?)", (source, time.time(), message))306 con.commit()307308309# ---------------------------------------------------------------------------310# Cache des pages détail (« détail si nouveau/modifié »)311# ---------------------------------------------------------------------------312313def get_cached_detail(con: sqlite3.Connection, source: str,314 external_id: str, key: str) -> dict | None:315 """Payload détail mis en cache si la clé (hash liste) n'a pas changé."""316 row = con.execute(317 "SELECT key, payload FROM detail_cache WHERE source=? AND external_id=?",318 (source, external_id)).fetchone()319 if row and row["key"] == key and row["payload"]:320 try:321 return json.loads(row["payload"])322 except ValueError:323 return None324 return None325326327def put_cached_detail(con: sqlite3.Connection, source: str,328 external_id: str, key: str, payload: dict) -> None:329 con.execute(330 "INSERT INTO detail_cache (source, external_id, key, payload, fetched_at)"331 " VALUES (?,?,?,?,?)"332 " ON CONFLICT(source, external_id) DO UPDATE SET"333 " key=excluded.key, payload=excluded.payload, fetched_at=excluded.fetched_at",334 (source, external_id, key, json.dumps(payload, ensure_ascii=False),335 time.time()))336 con.commit()337