"""CSV -> PostGIS, via the atomic dataset swap of stack doc §3. python -m pipeline.load_places data/finland-food.csv data/kenya-food.csv Build `places_new` alongside the live table, load and index it while the app keeps serving, then rename in one short transaction. `places_new` is declared in full rather than with `LIKE places INCLUDING ALL`, for the two reasons §3 gives: LIKE copies an id default still pointing at the sequence `places` owns (which the later DROP destroys, breaking the *second* refresh), and it copies the GiST index, making the explicit CREATE INDEX a duplicate. Django owns the `places` schema; this script owns the data. The DDL below must therefore stay in step with the Place model. """ import csv import os import sys import time from pathlib import Path import psycopg DDL = """ CREATE TABLE places_new ( id bigint GENERATED BY DEFAULT AS IDENTITY CONSTRAINT places_new_pkey PRIMARY KEY, osm_type varchar(1) NOT NULL, osm_id bigint NOT NULL, name text NOT NULL, location geography(Point, 4326) NOT NULL, category text NULL, cuisine text NULL, opening_hours text NULL, diet_vegan text NULL, diet_vegetarian text NULL, diet_gluten_free text NULL ) """ COPY_SQL = """ COPY places_new (osm_type, osm_id, name, location, category, cuisine, opening_hours, diet_vegan, diet_vegetarian, diet_gluten_free) FROM STDIN """ def stream(csv_path: Path): """Yield COPY tuples, turning the CSV's lon/lat pair into a geography literal. Absent optional tags arrive as empty CSV fields and must reach Postgres as NULL, not as empty strings — the model declares them nullable and the app tests them for presence. """ with open(csv_path, newline="", encoding="utf-8") as fh: rows = csv.reader(fh) next(rows) for osm_type, osm_id, name, lon, lat, cat, cuisine, hours, vegan, vegetarian, gluten in rows: yield ( osm_type, int(osm_id), name, f"SRID=4326;POINT({lon} {lat})", cat or None, cuisine or None, hours or None, vegan or None, vegetarian or None, gluten or None, ) def load(csv_paths: list[Path], dsn: str) -> None: """Build the new table from one or more CSVs, then swap it in. Several inputs load into one table because `places` is a single global index: two regional extracts are two parts of the same world, not two datasets. Whatever is passed here *is* the table afterwards — this replaces, it never appends to what is already live. """ with psycopg.connect(dsn, autocommit=True) as conn: t0 = time.monotonic() # A previous run that died between CREATE and swap leaves this behind. conn.execute("DROP TABLE IF EXISTS places_new") conn.execute(DDL) with conn.cursor() as cur: with cur.copy(COPY_SQL) as copy: for csv_path in csv_paths: # Counted here, not from cur.rowcount: that is not meaningful until the # COPY has been closed out, so a per-file reading of it would be noise. written = 0 for record in stream(csv_path): copy.write_row(record) written += 1 print(f" {csv_path.name}: {written} rows") loaded = cur.rowcount # only final once the COPY has been closed out print(f"copied {loaded} rows in {time.monotonic() - t0:.1f}s") conn.execute("CREATE INDEX places_new_location_idx ON places_new USING GIST (location)") conn.execute("ANALYZE places_new") print(f"indexed and analyzed in {time.monotonic() - t0:.1f}s") # The only step that blocks readers, and only for the duration of two catalogue # updates. In-flight queries see all old rows or all new rows, never a mix. with conn.transaction(): conn.execute("ALTER TABLE places RENAME TO places_old") conn.execute("ALTER TABLE places_new RENAME TO places") conn.execute("DROP TABLE places_old") # Index and sequence names live in the schema namespace, not inside their table, so # they survive the table rename and would collide with the next run's `places_new`. # Dropping places_old is what frees the unprefixed names for this run's objects. conn.execute("ALTER INDEX places_new_pkey RENAME TO places_pkey") conn.execute("ALTER INDEX places_new_location_idx RENAME TO places_location_idx") conn.execute("ALTER SEQUENCE places_new_id_seq RENAME TO places_id_seq") print(f"swap complete in {time.monotonic() - t0:.1f}s") if __name__ == "__main__": # DATABASE_URL is written for django-environ, whose PostGIS scheme libpq does not know. dsn = os.environ["DATABASE_URL"].replace("postgis://", "postgresql://", 1) load([Path(arg) for arg in sys.argv[1:]], dsn)