← back to Carnegie Reprice
Carnegie Phase 1: psql-piped writer (no psycopg dep), fix repeat Length/Width split
08178e07dfe1a8ab946d4cfdafa73787b572d43d · 2026-08-18 12:23:06 -0700 · steve
Files touched
M carnegie_phase1_scrape.py
Diff
commit 08178e07dfe1a8ab946d4cfdafa73787b572d43d
Author: steve <steve@designerwallcoverings.com>
Date: Tue Aug 18 12:23:06 2026 -0700
Carnegie Phase 1: psql-piped writer (no psycopg dep), fix repeat Length/Width split
---
carnegie_phase1_scrape.py | 134 +++++++++++++++++++++++++---------------------
1 file changed, 73 insertions(+), 61 deletions(-)
diff --git a/carnegie_phase1_scrape.py b/carnegie_phase1_scrape.py
index b85d8e0..6a15aa2 100644
--- a/carnegie_phase1_scrape.py
+++ b/carnegie_phase1_scrape.py
@@ -7,9 +7,10 @@ legitimately don't exist on Carnegie (numeric colorways) so color_name is left
as-is (placeholder) and NOT fabricated.
"""
import re, sys, json, time, html as H, urllib.request, urllib.error
-import psycopg2, psycopg2.extras
+import subprocess
DSN = "host=/tmp dbname=dw_unified"
+OUT_SQL = "/tmp/carnegie_p1_updates.sql"
UA = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124 Safari/537.36"
# Carnegie product-description label -> our column
@@ -107,70 +108,81 @@ def parse_colors(h):
out[str(num).strip()] = img.replace("\\/", "/")
return out
-def main():
- conn = psycopg2.connect(DSN)
- conn.autocommit = False
- cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
- cur.execute("SELECT DISTINCT product_url FROM carnegie_catalog WHERE product_url IS NOT NULL ORDER BY product_url")
- urls = [r["product_url"] for r in cur.fetchall()]
- total = len(urls)
- print(f"[carnegie phase1] {total} distinct product URLs")
-
- ok_pages = 0; fail_pages = 0; rows_specd = 0; rows_imaged = 0
- for idx, url in enumerate(urls, 1):
- h = fetch(url)
- if not h:
- fail_pages += 1
- print(f" [{idx}/{total}] FETCH-FAIL {url}")
- continue
- specs = parse_specs(h)
- colors = parse_colors(h)
+def q(v):
+ """SQL literal or NULL."""
+ if v is None or v == "":
+ return "NULL"
+ return "'" + str(v).replace("'", "''") + "'"
- rh, rv = split_repeat(specs.get("Repeat"))
- vals = {
- "width": specs.get("Width"),
- "content": specs.get("Contents") or specs.get("Content"),
- "durability_wyzenbeek": specs.get("Durability"),
- "repeat_h": rh,
- "repeat_v": rv,
- "finish": specs.get("Finish/es (as stocked)") or specs.get("Available Finish(es)"),
- "backing": specs.get("Backing") or specs.get("Backing (as stocked)"),
- "cleaning_code": specs.get("Cleaning Code"),
- "flammability": specs.get("Flammability"),
- "origin": specs.get("Manufactured In"),
- "specs": json.dumps(specs) if specs else None,
- }
- # update all color rows on this URL with shared specs
- cur.execute("""
- UPDATE carnegie_catalog SET
- width=%(width)s, content=%(content)s,
- durability_wyzenbeek=%(durability_wyzenbeek)s,
- repeat_h=%(repeat_h)s, repeat_v=%(repeat_v)s,
- finish=%(finish)s, backing=%(backing)s,
- cleaning_code=%(cleaning_code)s, flammability=%(flammability)s,
- origin=%(origin)s, specs=%(specs)s::jsonb, updated_at=now()
- WHERE product_url=%(url)s
- """, {**vals, "url": url})
- if any(v for k, v in vals.items() if k != "specs"):
- rows_specd += cur.rowcount
+def get_urls():
+ r = subprocess.run(
+ ["psql", DSN, "-tAc",
+ "SELECT DISTINCT product_url FROM carnegie_catalog WHERE product_url IS NOT NULL ORDER BY product_url"],
+ capture_output=True, text=True, check=True)
+ return [u for u in r.stdout.splitlines() if u.strip()]
- # per-color swatch image
- for num, img in colors.items():
- cur.execute("""
- UPDATE carnegie_catalog SET swatch_image_url=%s, updated_at=now()
- WHERE product_url=%s AND color_number=%s
- """, (img, url, num))
- rows_imaged += cur.rowcount
+def main():
+ urls = get_urls()
+ total = len(urls)
+ print(f"[carnegie phase1] {total} distinct product URLs", flush=True)
- ok_pages += 1
- if idx % 25 == 0:
- conn.commit()
- print(f" [{idx}/{total}] committed | pages_ok={ok_pages} specd_rows={rows_specd} imaged_rows={rows_imaged}")
- time.sleep(0.4)
+ ok_pages = 0; fail_pages = 0; spec_pages = 0; img_stmts = 0
+ with open(OUT_SQL, "w") as sql:
+ sql.write("BEGIN;\n")
+ for idx, url in enumerate(urls, 1):
+ h = fetch(url)
+ if not h:
+ fail_pages += 1
+ print(f" [{idx}/{total}] FETCH-FAIL {url}", flush=True)
+ continue
+ specs = parse_specs(h)
+ colors = parse_colors(h)
+ rh, rv = split_repeat(specs.get("Repeat"))
+ vals = {
+ "width": specs.get("Width"),
+ "content": specs.get("Contents") or specs.get("Content"),
+ "durability_wyzenbeek": specs.get("Durability"),
+ "repeat_h": rh,
+ "repeat_v": rv,
+ "finish": specs.get("Finish/es (as stocked)") or specs.get("Available Finish(es)"),
+ "backing": specs.get("Backing") or specs.get("Backing (as stocked)"),
+ "cleaning_code": specs.get("Cleaning Code"),
+ "flammability": specs.get("Flammability"),
+ "origin": specs.get("Manufactured In"),
+ }
+ specs_json = json.dumps(specs) if specs else None
+ if any(vals.values()) or specs_json:
+ spec_pages += 1
+ sql.write(
+ "UPDATE carnegie_catalog SET "
+ f"width={q(vals['width'])}, content={q(vals['content'])}, "
+ f"durability_wyzenbeek={q(vals['durability_wyzenbeek'])}, "
+ f"repeat_h={q(vals['repeat_h'])}, repeat_v={q(vals['repeat_v'])}, "
+ f"finish={q(vals['finish'])}, backing={q(vals['backing'])}, "
+ f"cleaning_code={q(vals['cleaning_code'])}, flammability={q(vals['flammability'])}, "
+ f"origin={q(vals['origin'])}, "
+ f"specs={q(specs_json)}::jsonb, updated_at=now() "
+ f"WHERE product_url={q(url)};\n")
+ for num, img in colors.items():
+ sql.write(
+ f"UPDATE carnegie_catalog SET swatch_image_url={q(img)}, updated_at=now() "
+ f"WHERE product_url={q(url)} AND color_number={q(str(num))};\n")
+ img_stmts += 1
+ ok_pages += 1
+ if idx % 25 == 0:
+ print(f" [{idx}/{total}] pages_ok={ok_pages} spec_pages={spec_pages} img_stmts={img_stmts}", flush=True)
+ time.sleep(0.4)
+ sql.write("COMMIT;\n")
- conn.commit()
- print(f"[DONE] pages ok={ok_pages} fail={fail_pages} | rows specd={rows_specd} imaged={rows_imaged}")
- cur.close(); conn.close()
+ print(f"[SCRAPE DONE] pages ok={ok_pages} fail={fail_pages} spec_pages={spec_pages} img_stmts={img_stmts}", flush=True)
+ print(f"[APPLY] running {OUT_SQL} via psql ...", flush=True)
+ r = subprocess.run(["psql", DSN, "-v", "ON_ERROR_STOP=1", "-f", OUT_SQL],
+ capture_output=True, text=True)
+ print(r.stdout[-1500:]);
+ if r.returncode != 0:
+ print("PSQL ERROR:\n", r.stderr[-2000:], flush=True)
+ sys.exit(1)
+ print("[APPLIED OK]", flush=True)
if __name__ == "__main__":
main()
← 4258cca Carnegie Phase 1: spec-column ALTER + Magento spec/swatch sc
·
back to Carnegie Reprice
·
Carnegie Phase 1 complete: specs+swatch images applied to 59 f5f4a83 →