`scripts/artmura-onboard/db_write.py`

36 lines
#!/usr/bin/env python3
"""Refresh newwall_catalog Artmura rows FROM data/artmura.json (no network)."""
import json, os, subprocess

HERE = os.path.dirname(os.path.abspath(__file__))
PKG = os.path.join(HERE, "data", "artmura.json")
cs = open("/tmp/.dwcs").read().strip()

def esc(v):
    if v is None: return "NULL"
    if isinstance(v, (int, float)): return str(v)
    return "'" + str(v).replace("'", "''") + "'"

d = json.load(open(PKG))
recs = d["products"]
print(f"refreshing {len(recs)} newwall_catalog rows from package ...")
updated = 0
for r in recs:
    all_images = "{" + ",".join('"%s"' % s for s in r["images"]) + "}"
    about = (f"Artmura — Italian non-woven wallcovering. "
             f"Grade: {r.get('grade')}. Origin: {r.get('origin')}. Lead-time: {r.get('lead_time')}.")
    q = f"""UPDATE newwall_catalog SET
      mfr_sku={esc(r['mfr_sku'])}, pattern_name={esc(r['pattern_series'])}, color_name={esc(r['color'])},
      collection={esc(r['collection_book'])}, product_type={esc(r['product_type'])}, material={esc(r['substrate'])},
      width={esc(r['dimensions'])}, coverage={esc(r['wall_coverage'])}, price_retail={esc(r['price_newwall_retail'])},
      image_url={esc(r['images'][0] if r['images'] else None)}, all_images={esc(all_images)},
      body_html={esc(r['body_html'])}, us_distributor='newwall.com (reseller)', about_vendor={esc(about)},
      last_scraped=now(), updated_at=now()
    WHERE handle={esc(r['handle'])};"""
    res = subprocess.run(["psql", cs, "-tAc", q], capture_output=True, text=True)
    if "UPDATE 1" in res.stdout:
        updated += 1
    elif res.stderr.strip():
        print("  !", r["mfr_sku"], res.stderr.strip()[:120])
print(f"updated {updated}/{len(recs)} rows")