Как синхронизировать товары из CSV поставщика и не затереть ручные правки

Редактор вчера вечером поправил название: вместо «Кабель USB-C 1м чёрн.» в карточке теперь аккуратное «Кабель USB-C — USB-C, 1 м, чёрный». Утром пришёл файл поставщика, импорт отработал без ошибок, и на витрине снова красуется «чёрн.». Никто не ошибся в одиночку. Импорт честно исполнил своё правило «файл главнее», а редактор честно исполнил своё «карточка принадлежит магазину». Проблема в том, что одно поле получило двух хозяев и никто не решил, чей голос весомее.

Эта статья про то, как такой конфликт разобрать заранее: как опознавать товар, кому отдавать каждое поле, как читать файл, не теряя смысла, как хранить значение поставщика и ручную правку рядом и как всё это уложить в один SQL-запрос. Примеры иллюстративные: схема и запросы придуманы для объяснения, это не готовая интеграция и не результат запуска на чьём-то каталоге.

Сначала: кто такой «этот же товар»

Любая синхронизация начинается с вопроса, по какому признаку строка файла сегодня и строка файла вчера описывают один и тот же товар. Если ответ неверен, дальше всё остальное бессмысленно: ручные правки привязаны не к тому, что вы думаете.

Кандидаты на ключ обычно такие: артикул поставщика, штрихкод, внутренний код вашей системы, название. Название отпадает сразу: его правят и вы, и поставщик. Штрихкод хорош, но бывает пустым или общим для нескольких вариантов. Артикул поставщика чаще всего стабилен, но уникален только в пределах одного поставщика. Поэтому ключ товара в вашей базе разумно делать составным: идентификатор поставщика плюс его артикул. Такой ключ задаётся ограничением UNIQUE или PRIMARY KEY на комбинацию столбцов, и, как описано в документации PostgreSQL по ограничениям, при составном ограничении уникальна именно комбинация, а не каждый компонент по отдельности. Два поставщика могут прислать артикул «A-100», и это будут две разные строки.

Есть тонкость с пустотой. Обычный UNIQUE по умолчанию не считает значения NULL равными, поэтому сам по себе он не запретит несколько строк с NULL в компонентах ключа. PRIMARY KEY объединяет уникальность с NOT NULL. Практический вывод: артикул поставщика в ключе должен быть объявлен NOT NULL, а строки файла без артикула отклоняются на входе, а не превращаются в товар-призрак.

И главное, что касается самой идентичности: ключ нужно хранить как строку. Артикул «00123» не равен «123». Если файл побывал в табличном редакторе, ведущие нули могли пропасть ещё до вас. Читатель CSV этого не исправит: он возвращает то, что лежит в файле, и восстановить утраченные раньше нули не сможет. Поэтому первое правило договора с поставщиком: артикул приходит как текст, а потерю нулей мы замечаем по резкому росту числа «новых» и «исчезнувших» товаров в отчёте о сверке, о котором будет ниже.

Владелец у каждого поля свой

Теперь вернёмся к утреннему конфликту. Он возник потому, что мы говорили «товар», а надо говорить «поле». У товара десятки атрибутов, и они принадлежат разным сторонам. Удобная форма договора — матрица полей: для каждого поля указано, кто его владелец и что делает импорт.

Обратите внимание на среднюю категорию: «магазин, стартует из файла». Название и розничная цена — поля с двумя источниками. Вот для них и нужна особая конструкция, иначе придётся выбирать между «никогда не обновлять» (устаревает) и «всегда обновлять» (затирает правки).

Файл прежде всего нужно правильно прочитать

Прежде чем решать, чьё значение победит, убедимся, что мы вообще видим значение, которое прислали. CSV — не один формат, а семейство диалектов. Как поясняет документация модуля csv в Python, параметры reader определяют, в частности, разделитель и правила кавычек, а файлы при чтении рекомендуется открывать с newline="", чтобы переводы строк внутри кавычек обрабатывались корректно. Описание товара с переносом строки внутри ячейки — вполне обычное дело.

Поэтому разделитель, символ кавычек и кодировку лучше зафиксировать в профиле поставщика, а не угадывать каждое утро. В той же документации сказано, что эвристика Sniffer.has_header приблизительна и бывает ошибочной в обе стороны. Для регулярного импорта это плохой кандидат на роль судьи: если у поставщика строка заголовков есть всегда, так и запишите.

Кодировка и невидимый BOM

Файл, сохранённый табличным редактором, нередко начинается с BOM, трёх служебных байт UTF-8. Если читать его обычным utf-8, первое имя колонки получит невидимый префикс, и ключевая колонка «sku» превратится в «\ufeffsku». Кодек utf-8-sig при декодировании пропускает BOM, если он стоит в начале файла (документация codecs). Но у него нет магии для других кодировок: произвольный файл в старой кириллической кодировке им не прочитать. Если поставщик присылает не UTF-8, это нужно зафиксировать в профиле как отдельную кодировку, а не надеяться на автоопределение.

Обработчик ошибок тоже решение, а не мелочь. По умолчанию действует strict, и неверный байт поднимает исключение. Режимы ignore и replace пропускают данные или подставляют знак замены. Для каталога это значит, что название с испорченной буквой молча попадёт в базу. Разумнее оставить strict и отклонять файл целиком: утренний импорт, который не состоялся, виден и поправим, а тихо искажённое название потом ищут неделями.

Колонки, типы и «кривые» строки

DictReader удобен тем, что связывает значения с именами колонок, беря их из первой строки, если fieldnames не передан. Но важно помнить два поведения. Лишние поля в строке попадают в список под ключом restkey, а недостающие поля непустой строки заполняются значением restval, по умолчанию None. Иначе говоря, короткая строка даёт вам None там, где в файле вообще ничего не было, а пустая ячейка даёт пустую строку. Это два разных события, и позже мы обязаны их различать. Строку с лишними полями разумнее отправлять в отчёт об ошибках, чем пытаться угадать, куда съехали значения.

Типов reader не приводит: по документации он возвращает строки, и лишь режим QUOTE_NONNUMERIC превращает незакавыченные поля в float. Для денег float — плохой выбор. Как показывает документация decimal, Decimal.from_float(0.1) не равен Decimal("0.1"): преобразование из float сохраняет уже возникшее двоичное приближение. Поэтому цену стоит превращать в Decimal прямо из строки, заодно заменяя запятую на точку, если поставщик пишет «199,90». Тогда в базу попадает ровно то, что было в файле.

Обратная сторона: когда вы сами будете писать отчёты в CSV, учтите, что writer записывает Python None как пустую строку, и документация прямо называет это необратимым преобразованием. Отчёт, в котором «не было значения» и «значение пустое» выглядят одинаково, для разбора спорных случаев почти бесполезен. Для отчётов лучше завести явную колонку статуса.

Что означает пустая ячейка

Теперь самое коварное. В файле поставщика может встретиться пустая ячейка, и у неё как минимум пять разных смыслов. Импорт, который не различает их, рано или поздно обнулит что-нибудь важное.

Ключевая мысль: нулевой остаток, пустая строка, NULL и отсутствие товара в файле — четыре разных факта. Объединять их в «ничего» значит принимать решения за поставщика. Каждое значение получает явную семантику в договоре импорта, а не выводится из удобства кода.

Хранить два значения, а не одно

Вернёмся к конфликту на примере названия. Пока в базе лежит одно поле name, ответить на вопрос «кто его последним менял» невозможно. Решение простое и скучное: развести исходное и ручное значение по разным столбцам. Поле supplier_name всегда хранит то, что прислал поставщик. Поле manual_name хранит правку редактора и пусто, пока правки нет. А то, что видит покупатель, считается как эффективное значение.

Вот как это выглядит на трёх состояниях одной карточки.

Последняя строка и есть выигрыш. Новое значение поставщика не потеряно: оно лежит рядом и видно в интерфейсе или в отчёте, но публикуется по-прежнему правка редактора. Это и есть согласованное правило поля: файл питает исходный столбец, а решение о публикации принимает правило, а не порядок, в котором что-то записалось.

Почему не COALESCE

Соблазн велик: эффективное значение = COALESCE(manual_name, supplier_name). Выражение действительно возвращает первый аргумент, не равный NULL (документация по условным выражениям). Но это выбор по NULL, а не по отдельному признаку ручного переопределения. Пока вы различаете «правки нет» и «правка есть» только по NULL, вы не можете выразить два важных случая: редактор сознательно хочет пустое значение (например, скрыть подзаголовок) и редактор хочет вернуться к значению поставщика. В обоих случаях NULL уже занят другим смыслом.

Поэтому рядом с ручным значением заводится явный флаг: name_overridden boolean NOT NULL DEFAULT false. Тогда эффективное значение выбирает CASE: первая ветвь с истинным условием, а при отсутствии подходящей ветви и ELSE получился бы NULL, поэтому ELSE лучше писать всегда (те же условные выражения).

UPSERT, который трогает только своё

Теперь соберём импорт. Конструкция INSERT ... ON CONFLICT позволяет при конфликте с подходящим уникальным ограничением выполнить DO NOTHING либо DO UPDATE, причём для DO UPDATE нужен conflict_target; в выражениях доступны существующая строка через имя таблицы и предлагаемая через excluded (документация INSERT). Список SET задаёт обновляемые столбцы, и всё, что в нём не названо, остаётся прежним (это же верно для обычного UPDATE, документация UPDATE).

Отсюда главный принцип защиты ручных правок: он не в самом UPSERT, а в том, какие столбцы вы перечислили в SET. Столбцы manual_name, name_overridden, description в SET просто не попадают, и импорт физически не может их изменить. Ниже авторский иллюстративный пример; он не выполнялся и показывает форму запроса, а не готовую интеграцию.

CREATE TABLE product (
  supplier_id        integer NOT NULL,
  supplier_sku       text    NOT NULL,
  supplier_name      text,
  manual_name        text,
  name_overridden    boolean NOT NULL DEFAULT false,
  effective_name     text,
  purchase_price     numeric(12,2),
  stock_qty          integer,
  description        text,
  PRIMARY KEY (supplier_id, supplier_sku)
);

INSERT INTO product AS p
  (supplier_id, supplier_sku, supplier_name, effective_name,
   purchase_price, stock_qty)
VALUES (%s, %s, %s, %s, %s, %s)
ON CONFLICT (supplier_id, supplier_sku) DO UPDATE SET
  supplier_name  = COALESCE(NULLIF(excluded.supplier_name, ''), p.supplier_name),
  purchase_price = excluded.purchase_price,
  stock_qty      = excluded.stock_qty,
  effective_name = CASE
    WHEN p.name_overridden THEN p.manual_name
    ELSE COALESCE(NULLIF(excluded.supplier_name, ''), p.supplier_name)
  END;

Разберём, что здесь происходит. Цена и остаток принадлежат поставщику, поэтому берутся из excluded без условий. Имя поставщика обновляется, только если в файле оно непустое: пустая ячейка не затирает прежнее значение, как мы решили в таблице пустоты. Эффективное имя выбирает CASE по флагу: если стоит ручное переопределение, публикуется manual_name, иначе имя поставщика. Выражение для нового значения, как и положено, использует старые значения той же строки через алиас p.

Заметьте также, что NULLIF здесь превращает пустую строку в NULL, чтобы COALESCE мог её пропустить. Это наше решение для именно этого поля. Для остатка такого шага нет намеренно: ноль приходит как ноль, а неизвестный остаток должен отсекаться раньше, на этапе разбора файла, где у вас ещё есть вся информация о том, чем была «пустота».

Два подводных камня самого запроса

Первый. Один INSERT ... ON CONFLICT DO UPDATE не может изменить одну и ту же существующую строку больше одного раза: повторяющиеся входные ключи могут вызвать ошибку cardinality violation. Если поставщик по недосмотру прислал один артикул дважды, пакетная вставка упадёт. Поэтому дубликаты ключей внутри файла надо обнаруживать заранее, и вместе с политикой («берём последнюю строку» или «отклоняем файл») записывать это в договор.

Второй. ON CONFLICT DO UPDATE гарантирует атомарный результат INSERT либо UPDATE, в том числе при конкуренции, если нет независимой ошибки. Это хорошая новость для параллельной работы редактора и импорта, но не волшебная защита: редактор, выставляющий флаг name_overridden, и импорт, считающий effective_name, оба меняют одну строку. Защита следует из того, что в SET импорта нет ручных столбцов, а effective_name вычисляется из актуального значения флага внутри самой строки. Если ваш интерфейс редактирования тоже пересчитывает effective_name, он должен делать это той же формулой, иначе два места будут расходиться.

Дополнительное WHERE в DO UPDATE ограничивает строки, которые обновляются при конфликте. Его удобно использовать, чтобы не переписывать строки, где ничего не изменилось, но учтите счётчик: в UPDATE count входят и совпавшие строки с неизменившимся значением, а нулевой счётчик сам по себе не ошибка. Поэтому по числу обновлённых строк нельзя судить «что поменялось», для этого нужен отдельный preview.

Сначала посмотреть, потом применять

Режим preview не требует новых возможностей базы. Это обычный разбор файла и сравнение с текущим состоянием, результат которого записывается в отчёт, а не в таблицу товаров. Для каждой строки отчёт отвечает на вопросы: новый ли это товар, изменились ли цена и остаток, пришло ли новое имя поставщика, отличающееся от того, что мы видим на витрине, и скрыто ли оно ручной правкой.

Полезно разделить отчёт на группы, а не на один длинный список:

• новые товары, которых раньше не было;
• изменения цены и остатка, где владелец поставщик и вопросов нет;
• изменения имени у карточек с ручной правкой: поставщик прислал новое, публикация осталась прежней, решение за человеком;
• отклонённые строки: нет артикула, лишние поля, дубликаты ключа, нечитаемая цена;
• товары, которых нет в файле, но которые есть в базе.

Третья группа превращает ручные правки из тихого риска в рабочий список. Если поставщик исправил опечатку в названии, а у вас стоит ручное переопределение, вы увидите это и либо сохраните правку, либо снимете флаг.

Применение разумно оформить одной транзакцией. Транзакция объединяет шаги в операцию «всё или ничего», а промежуточные изменения не видны другим транзакциям; ROLLBACK отменяет ещё не зафиксированное (документация по транзакциям). Если на сто втором тысячном ряду файл оказался сломан, не должно получиться, что половина каталога обновилась, а половина нет. Но не переоценивайте этот инструмент: транзакция не решает, чьё значение побеждает, она лишь не оставляет импорт на полпути. Защита ручных правок по-прежнему определяется составом SET и моделью флагов.

Повторный файл, исчезновение товара и снятие правки

Три ситуации стоит продумать до первой аварии, потому что именно они ломают наивные интеграции.

Тот же файл дважды

Поставщик прислал вчерашний файл повторно, или ваш планировщик запустил импорт два раза. Хорошо спроектированный импорт должен выдержать это без последствий. В нашей схеме так и происходит: цена и остаток перезаписываются теми же значениями, имя поставщика тем же, эффективное имя пересчитывается тем же правилом. Результат второго прогона совпадает с первым, потому что запрос выражает желаемое состояние, а не приращение. Опасен был бы импорт вида «остаток = остаток + пришло». Но один нюанс: повтор безвреден только если файл вчерашний по содержанию. Если вчерашний файл придёт после сегодняшнего, цена откатится на старую. Чтобы это заметить, храните метку файла (имя или дату формирования) и отказывайтесь применять файл старше последнего применённого.

Товар пропал из файла

Отсутствие строки в файле — ещё одна форма «пустоты», и она не равна удалению товара. Поставщик мог выгрузить урезанный файл, мог временно снять позицию, а мог её действительно закрыть. Удалять карточку по одному отсутствию опасно: вместе с ней исчезнут ручные правки, история заказов и ссылки с внешних площадок. Безопаснее двухступенчатое правило: при отсутствии в файле обнулить остаток или пометить позицию как «нет у поставщика», а скрытие с витрины и архивирование выполнять только после нескольких подряд файлов без неё, и то по согласованному порядку. Число «нескольких» вы определяете сами исходя из частоты выгрузок; это ваше правило, а не стандарт. Если же в файле внезапно пропало подавляющее большинство товаров, это признак поломанной выгрузки, и применение стоит остановить до выяснения.

Как снять ручное переопределение

Ручная правка не должна быть пожизненной. Рано или поздно поставщик исправит название, и оно окажется лучше вашего. Снятие правки в нашей модели выглядит как отдельное явное действие: name_overridden выставляется в false, а effective_name пересчитывается из supplier_name по тому же правилу. Содержимое manual_name можно сохранить на случай, если решение придётся переиграть.

Хорошая практика: показывать при снятии, что именно изменится на витрине, то есть то же значение поставщика из таблицы выше. Тогда редактор видит последствия до подтверждения. А вот случай «сознательно пустое ручное значение» теперь выражается без двусмысленности: флаг истинен, manual_name пуст, эффективное значение пустое по прямому решению человека, а не из-за пропущенной ячейки.

Где запускать и сколько ресурсов нужно

Сама по себе логика импорта лёгкая: разбор файла построчно, пакетная запись в PostgreSQL, отчёт. Для регулярного запуска нужен сервер, на котором работают планировщик, скрипт и база, и root-доступ, чтобы настроить окружение под себя. На VPS MAATRIX (NVMe, AMD EPYC, root-доступ) такое окружение можно развернуть самостоятельно: для небольшого каталога логично начать с младшей конфигурации, например Ba с 1 vCPU, 2 ГБ RAM и 40 ГБ NVMe, а при росте объёма файлов и базы перейти на Uab (2 vCPU, 4 ГБ, 80 ГБ) или выше. Это ориентир по ресурсам, а не обещание ёмкости: реальную потребность покажет ваш файл и ваша нагрузка. В публичном каталоге также предложены n8n и Nextcloud с установкой, если вам удобнее собирать цепочку приёма файлов из готовых блоков; точное наличие и ОС проверяется в форме заказа.

Если вы выбираете самого поставщика, а не только настраиваете его выгрузку, полезно заранее посмотреть материал про проверку поставщика по реестру ПО: он закрывает соседний вопрос, с кем вообще стоит выстраивать такую интеграцию.

Критерии, по которым видно, что договор работает

Вместо универсальной проверки — несколько предметных признаков, по которым можно оценить свой импорт на тестовом файле из десятка строк:

• карточка с ручным названием после импорта сохраняет его, а новое имя поставщика видно в supplier_name и в отчёте preview;
• цена и остаток обновляются независимо от наличия ручных правок;
• ячейка описания, пустая в файле, не стирает существующее описание;
• остаток «0» применяется как ноль, а пустой остаток обрабатывается по отдельному правилу и попадает в отчёт;
• повторный запуск того же файла не меняет данные и не плодит записи;
• строка с лишними полями или без артикула не попадает в базу, а оказывается в списке отклонённых;
• снятие переопределения возвращает на витрину значение поставщика, и это видно до подтверждения.

Если все семь пунктов выполняются на небольшом примере, у вас не «синхронизация, которая обычно ничего не ломает», а явный договор: файл питает исходные поля, ручные правки живут в своих столбцах, а то, что видит покупатель, определяется правилом каждого поля. Утром редактор откроет карточку и увидит и свою формулировку, и новое слово от поставщика рядом, а решение о публикации останется за правилом, которое вы согласовали заранее.