← back to Kravet Sheet Sync 2026 04 20
__pycache__/load_catalog_to_pg.cpython-314.pyc
50 lines
+
UK�j[
� �j � R t ^ RIt^ RIt^ RIt^ RIt^ RIt^ RItRR.tRtR t R t
R t ] ! R4 ^ t ^ t
]! 4 t]P ! R R
R R R
7 ;_uu_ 4 t]P$ ! ]4 t]! ]4 ;_uu_ 4 t] EF t]P. ! ]4 t]R, R8w d K& ]R, t]
! ]4 t]P7 R4 ;' g / t ] P7 R4 t ] P7 R4 t ]R, F� t ] ! ] R, 4 t ]] 3t!]!]9 d ]
^,
t
K( ]PE ]!4 ]PG ]]] R, ] ] R, ] R, ] ;' g R ] ;' g R .4 ] ^,
t K� EK RRR4 ]PH t%RRR4 ]&! R] R
R]
R
R24 ]PN ! R]%R .R R 7 ] ! R 4 ]PN ! ]R ., R R 7 ]PP ! ]%4 ]&! R!4 R# + ' g i L�; i + ' g i L�; i)"zBLoad kravet_image_catalog.jsonl into dw_unified.kravet_sku_images.N�sshzroot@45.61.58.125zkravet_image_catalog.jsonlc � � \ P ! \ R \ R4 P P RR4 RV R2., RRRR7 R# ) z PGPASSWORD=�os�
PGPASSWORD� z1 psql -h 127.0.0.1 -U dw_admin -d dw_unified -c "�"T)�capture_output�text�checkN)�
subprocess�run�SSH�
__import__�environ�get)�sqls &�load_catalog_to_pg.py�pg_execr sR � ��N�N�3�
�j��&�.�.�2�2�<��C�D�Du�vy�uz�z{�|�~� ~��$�d�4� c
� � \ P ! R R\ P ! RRV P 4 P 4 4 4 # )z\.0$r �-�.)�re�sub�strip�upper)�ss &r �norm_skur s/ � �
�6�6�'�2�r�v�v�d�C����� � �1B� C� D�Dr c �P � \ P ! RRV 4 P R4 # )z Strip size params for dedup key.z[?&](width|height|pad)=\w+r z?&)r r �rstrip)�us &r �
canonical_urlr! s! � �
�6�6�/��Q� 7� >� >�t� D�Dr a�
CREATE TABLE IF NOT EXISTS kravet_sku_images (
id serial PRIMARY KEY,
mfr_sku_norm text NOT NULL,
mfr_sku_raw text NOT NULL,
url text NOT NULL,
url_canonical text NOT NULL,
kind text NOT NULL,
source text NOT NULL,
product_url text,
product_name text,
created_at timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_kravet_sku_images_norm ON kravet_sku_images(mfr_sku_norm);
CREATE INDEX IF NOT EXISTS idx_kravet_sku_images_kind ON kravet_sku_images(kind);
CREATE UNIQUE INDEX IF NOT EXISTS ux_kravet_sku_images_norm_canon
ON kravet_sku_images(mfr_sku_norm, url_canonical);
�wz.csvFr )�suffix�delete�newline�status�ok�sku�algolia�url�name�urls�kind�sourcezcsv: �,z rows (z in-file dupes)�scpz-root@45.61.58.125:/tmp/kravet_images_load.csvT)r
zTRUNCATE kravet_sku_imagesa= PGPASSWORD={__import__("os").environ.get("PGPASSWORD","")} psql -h 127.0.0.1 -U dw_admin -d dw_unified <<'SQL'
\copy kravet_sku_images (mfr_sku_norm, mfr_sku_raw, url, url_canonical, kind, source, product_url, product_name) FROM '/tmp/kravet_images_load.csv' WITH (FORMAT csv);
SELECT COUNT(*) AS rows, COUNT(DISTINCT mfr_sku_norm) AS skus FROM kravet_sku_images;
SELECT kind, COUNT(*) FROM kravet_sku_images GROUP BY kind ORDER BY COUNT(*) DESC;
SELECT 'coverage' AS metric, COUNT(DISTINCT s.mfr_sku_norm) AS sheet_wallcovers,
COUNT(DISTINCT i.mfr_sku_norm) AS skus_with_at_least_one_image,
COUNT(DISTINCT s.mfr_sku_norm) - COUNT(DISTINCT i.mfr_sku_norm) AS missing
FROM kravet_sheet_fields s
LEFT JOIN kravet_sku_images i ON i.mfr_sku_norm = s.mfr_sku_norm
WHERE upper(COALESCE(s.use,'')) LIKE '%WALL%';
SQL
�done))�__doc__�jsonr r �csv�tempfiler r
�JSONLr r r! �rows�
skipped_dupes�set�seen�NamedTemporaryFile�tf�writerr" �open�f�line�loads�rr( �normr �alg�purl�pnamer �cu�key�add�writerowr+ �tmp�printr �unlink� r r �<module>rO s �� H� .� .� .� �!�"��$��4� E�E� � � �( ���
�
�u��
� � ��V�E�2�N�N�RT� �
�
�2��A�
�e�����D��
�
�4� �A���{�d�"���E�(�C��C�=�D��%�%� �"�(�(�b�C��7�7�5�>�D��G�G�F�O�E��v�Y�Y��"�1�U�8�,���R�j���$�;�!�Q�&�M� �����
��
�
�D�#�q��x��Q�v�Y��(� �T�Z�Z�UW�Y^�Yd�Yd�bd�e�f��� �� � �
�&
�'�'�C�+ O�. ��d�1�X�W�]�1� -�_�=� >� ����s� K�L�TX� Y� �$� %�
���s� � � �
� � � � �#�� �f�
�Q
��� O�N�s8 �#H"�>AH �BH �&
H �1H � H"� H
�H"�" H2