HTML 82.1%
Python 14.6%
TypeScript 1.9%
CSS 1%
JavaScript 0.5%
1#!/usr/bin/env python32# =============================================================================3# Job·Ka — Groupe KA4# Auteur : Simon-Pierre Boucher5# Contact : contact@spboucher.ai6# Fichier : scripts/backfill_phase2.py7# Rôle : Backfill unique de la Phase 2 (enrichissement des connecteurs) —8# réparations de données + peuplement des nouvelles colonnes.9# Idempotent ; les valeurs recalculées ici sont IDENTIQUES à celles10# que le pipeline produit désormais au sync (mêmes fonctions).11# Créé : 2026-08-19 Modifié : 2026-08-1912# =============================================================================13"""Usage : .venv/bin/python scripts/backfill_phase2.py1415Étapes (chacune consignée) :16 1. villes SuccessFactors cassées (« St ») : re-parse du slug d'URL ;17 2. dates Taleo/agnico_eagle : colonne = date LIMITE, pas publication ;18 3. salaires aberrants : re-normalisation (sanitize) puis re-extraction19 depuis la description, sinon remise à NULL ;20 4. HTML résiduel dans les descriptions : re-nettoyage (clean_html itératif) ;21 5. descriptions vides : purge du detail_cache -> re-fetch au prochain sync ;22 6. région administrative (17 régions, MAMH) depuis la ville ;23 7. langue de l'offre (heuristique) là où aucune valeur source ;24 8. avantages depuis les blocs structurés des descriptions ;25 9. titre d'affichage normalisé (title_clean) ;2610. apply_url depuis details.apply_url (njoyn) ;2711. quarantaine qualité (motifs JSON) sur toutes les offres actives.28"""29from __future__ import annotations3031import json32import re33import sys34import urllib.parse35from pathlib import Path3637sys.path.insert(0, str(Path(__file__).resolve().parent.parent))3839from jobka import db # noqa: E40240from jobka.normalize import ( # noqa: E40241 clean_html, clean_title, detect_language, extract_benefits,42 salary_from_text, salary_to_hourly, salary_to_yearly, sanitize_salary,43)44from jobka.quality import issues # noqa: E40245from jobka.regions import region_for_city # noqa: E402464748def log(step: str, n: int) -> None:49 print(f"[backfill] {step}: {n}")505152def main() -> None:53 con = db.connect()54 con.execute("PRAGMA busy_timeout=30000")5556 # 1. villes/titres SuccessFactors (villes à trait d'union tronquées) --------57 from jobka.regions import city_from_slug_tokens58 n = 059 for r in con.execute(60 "SELECT uid, url, city, title FROM jobs"61 " WHERE ats='successfactors'").fetchall():62 m = re.search(r"/job/([^/]+)/(\d+)/?$", r["url"])63 if not m:64 continue65 parts = [urllib.parse.unquote(p) for p in m.group(1).split("-")]66 city = city_from_slug_tokens(parts)67 if not city or len(city.split("-")) <= len((r["city"] or "").split("-")):68 continue # ville actuelle déjà complète (ou détail plus précis)69 if (r["city"] or "") != city.split("-")[0]:70 continue # la ville actuelle n'est pas le tronçon cassé attendu71 n_city = len(city.split("-"))72 title = " ".join(parts[n_city:-3])73 con.execute(74 "UPDATE jobs SET city=?, title=CASE WHEN ?<>'' THEN ? ELSE title END"75 " WHERE uid=?", (city, title, title, r["uid"]))76 n += 177 log("villes SuccessFactors réparées", n)7879 # 2. dates agnico_eagle (colonne = date limite) -----------------------------80 cur = con.execute(81 "UPDATE jobs SET date_deadline=date_posted, date_posted=NULL"82 " WHERE source='agnico_eagle' AND date_posted IS NOT NULL"83 " AND date_deadline IS NULL")84 log("dates agnico_eagle déplacées vers date_deadline", cur.rowcount)8586 # 3. salaires aberrants ------------------------------------------------------87 n_fixed = n_cleared = 088 for r in con.execute(89 """SELECT uid, salary_min, salary_max, salary_unit, salary_label,90 salary_hour_min, salary_hour_max, salary_year_min,91 salary_year_max, description FROM jobs92 WHERE salary_min IS NOT NULL AND (93 salary_hour_min<14 OR salary_hour_max>250 OR94 salary_year_min<20000 OR salary_year_max>600000 OR95 salary_hour_min>250 OR salary_year_min>600000)""").fetchall():96 lo, hi, unit = sanitize_salary(r["salary_min"], r["salary_max"],97 r["salary_unit"])98 label = r["salary_label"] or ""99 if lo is None:100 lo, hi, unit, label = salary_from_text(r["description"] or "")101 lo, hi, unit = sanitize_salary(lo, hi, unit)102 if lo is not None:103 con.execute(104 """UPDATE jobs SET salary_min=?, salary_max=?, salary_unit=?,105 salary_label=?, salary_year_min=?, salary_year_max=?,106 salary_hour_min=?, salary_hour_max=? WHERE uid=?""",107 (lo, hi, unit, label,108 salary_to_yearly(lo, unit), salary_to_yearly(hi, unit),109 salary_to_hourly(lo, unit), salary_to_hourly(hi, unit),110 r["uid"]))111 n_fixed += 1112 else:113 con.execute(114 """UPDATE jobs SET salary_min=NULL, salary_max=NULL,115 salary_unit=NULL, salary_label='', salary_year_min=NULL,116 salary_year_max=NULL, salary_hour_min=NULL,117 salary_hour_max=NULL WHERE uid=?""", (r["uid"],))118 n_cleared += 1119 log("salaires aberrants re-normalisés", n_fixed)120 log("salaires aberrants remis à NULL", n_cleared)121122 # 4. HTML résiduel -----------------------------------------------------------123 n = 0124 for r in con.execute(125 """SELECT uid, description FROM jobs WHERE description IS NOT NULL126 AND (description LIKE '%<p%' OR description LIKE '%<br%'127 OR description LIKE '%<li%' OR description LIKE '% %'128 OR description LIKE '%&%' OR description LIKE '%<%')129 """).fetchall():130 cleaned = clean_html(r["description"])131 if cleaned != r["description"]:132 con.execute("UPDATE jobs SET description=? WHERE uid=?",133 (cleaned, r["uid"]))134 n += 1135 log("descriptions re-nettoyées (HTML résiduel)", n)136137 # 5. descriptions vides : purge du detail_cache -> re-fetch au prochain sync138 n = 0139 for r in con.execute(140 """SELECT source, external_id FROM jobs WHERE active=1141 AND LENGTH(COALESCE(description,''))<50""").fetchall():142 cur = con.execute(143 "DELETE FROM detail_cache WHERE source=? AND external_id=?",144 (r["source"], r["external_id"]))145 n += cur.rowcount146 log("detail_cache purgé (descriptions vides, re-fetch au prochain sync)", n)147148 # 6-10. région / langue / avantages / title_clean / apply_url ----------------149 n_region = n_lang = n_ben = n_title = n_apply = 0150 for r in con.execute(151 """SELECT uid, title, city, region, description, language,152 benefits, details, apply_url FROM jobs""").fetchall():153 sets, args = [], []154 reg = region_for_city(r["city"] or "")155 if reg and reg != r["region"]:156 sets.append("region=?"); args.append(reg); n_region += 1157 if not r["language"]:158 lang = detect_language(r["title"] or "", r["description"] or "")159 if lang:160 sets.append("language=?"); args.append(lang); n_lang += 1161 if (r["benefits"] or "[]") in ("[]", ""):162 ben = extract_benefits(r["description"] or "")163 if ben:164 sets.append("benefits=?")165 args.append(json.dumps(ben, ensure_ascii=False))166 n_ben += 1167 tc = clean_title(r["title"] or "", r["city"] or "")168 if tc and tc != (r["title"] or ""):169 sets.append("title_clean=?"); args.append(tc); n_title += 1170 if not r["apply_url"]:171 try:172 details = json.loads(r["details"] or "{}")173 except ValueError:174 details = {}175 if details.get("apply_url"):176 sets.append("apply_url=?"); args.append(details["apply_url"])177 n_apply += 1178 if sets:179 con.execute(f"UPDATE jobs SET {', '.join(sets)} WHERE uid=?",180 args + [r["uid"]])181 log("régions administratives attribuées", n_region)182 log("langues détectées", n_lang)183 log("avantages extraits", n_ben)184 log("titres d'affichage normalisés", n_title)185 log("apply_url repris de details", n_apply)186187 # 11. quarantaine qualité (actives) ------------------------------------------188 n_q = n_ok = 0189 for r in con.execute(190 """SELECT uid, title, description, city, date_posted, date_deadline,191 salary_hour_min, salary_hour_max, salary_year_min,192 salary_year_max, salary_unit, quarantine FROM jobs193 WHERE active=1""").fetchall():194 defects = issues(195 title=r["title"] or "", description=r["description"] or "",196 city=r["city"] or "", date_posted=r["date_posted"],197 date_deadline=r["date_deadline"],198 salary_hour_min=r["salary_hour_min"],199 salary_hour_max=r["salary_hour_max"],200 salary_year_min=r["salary_year_min"],201 salary_year_max=r["salary_year_max"],202 salary_unit=r["salary_unit"])203 val = json.dumps(defects, ensure_ascii=False) if defects else None204 if val != r["quarantine"]:205 con.execute("UPDATE jobs SET quarantine=? WHERE uid=?",206 (val, r["uid"]))207 if defects:208 n_q += 1209 else:210 n_ok += 1211 log("offres actives en quarantaine", n_q)212 log("offres actives publiables", n_ok)213214 con.commit()215 con.close()216 print("[backfill] terminé.")217218219if __name__ == "__main__":220 main()221