spb/wp22_uqo

1#!/usr/bin/env python32# Author: Simon-Pierre Boucher — simon-pierre.boucher@uqo.ca3"""Phase 1 — immutable extraction of the Vrai Prix SQLite snapshot into parquet layers.45Verifies the snapshot checksum, dumps the SQLite schema, and writes the RAW layer6(no transformation other than column typing) plus a manifest.7"""8from __future__ import annotations910import sys11from pathlib import Path1213sys.path.insert(0, str(Path(__file__).resolve().parents[1] / "src"))1415import polars as pl1617from sharp_hpi import config as C18from sharp_hpi.io import query, sha256_file, sqlite_ro, write_manifest192021def main() -> None:22    C.ensure_dirs()23    sha = sha256_file(C.SNAPSHOT_DB)24    assert sha == C.SNAPSHOT_SHA256, f"snapshot checksum mismatch: {sha}"25    con = sqlite_ro(C.SNAPSHOT_DB)26    schema = query(con, "select type, name, sql from sqlite_master where sql is not null order by type, name")27    (C.DOCS / "snapshot_schema.sql").write_text(28        "-- SQLite schema of the Vrai Prix snapshot (read-only dump)\n" + "\n\n".join(schema["sql"].to_list()) + "\n"29    )3031    tx = query(con, "select * from transactions").with_columns(32        pl.col("date").str.to_date("%Y-%m-%d"),33        pl.col("year_built").cast(pl.Int32),34    )35    tx.write_parquet(C.RAW_TX)3637    units_m = query(38        con,39        """select u.* from units u40           where u.id_provinc in (select distinct id_provinc from transactions where id_provinc is not null and id_provinc <> '')""",41    )42    units_m.write_parquet(C.RAW_UNITS_MATCHED)4344    # Housing-stock layer: every roll unit, classification + value columns only (weights for aggregation).45    stock = query(46        con,47        """select id_provinc, code_mun, municipalite, cubf, type_prop, nb_logements, lien_physique,48                  genre_construction, annee_construction, aire_etages_m2, superficie_terrain_m2,49                  valeur_role, valeur_terrain, valeur_batiment, lat, lng, unite_voisinage, dat_cond_marche50           from units""",51    )52    stock.write_parquet(C.RAW_UNITS_STOCK)5354    mi = query(con, "select * from market_index")55    mi.write_parquet(C.RAW_MARKET_INDEX)5657    write_manifest(58        C.INTERIM / "raw_manifest.json",59        {60            "snapshot_db": str(C.SNAPSHOT_DB.name),61            "snapshot_sha256": sha,62            "snapshot_taken": C.SNAPSHOT_TAKEN,63            "snapshot_source": C.SNAPSHOT_SOURCE,64            "tables": {65                "transactions": {"rows": tx.height, "cols": tx.columns, "date_min": str(tx["date"].min()), "date_max": str(tx["date"].max())},66                "units_matched": {"rows": units_m.height, "cols": units_m.columns},67                "units_stock": {"rows": stock.height, "cols": stock.columns},68                "market_index": {"rows": mi.height, "cols": mi.columns},69            },70        },71    )72    print(f"transactions {tx.height:,} | units matched {units_m.height:,} | stock {stock.height:,} | market_index {mi.height}")737475if __name__ == "__main__":76    main()77