PyMySQL разные обновления в одном запросе?

Итак, у меня есть python script, который проходит примерно через 350 000 объектов данных, и в зависимости от некоторых тестов ему необходимо обновить строку, которая представляет каждый из этих объектов в mysql db. Я также использую pymysql, так как у меня были проблемы с ним, особенно при отправке больших запросов (выберите операторы с предложением where column IN (....), которое может содержать 100 000+ значений).

Поскольку каждое обновление для каждой строки может быть разным, каждый оператор обновления отличается. Например, для одной строки мы можем обновить first_name, но для другой строки мы хотим оставить first_name нетронутой, и мы хотим обновить last_name.

Вот почему я не хочу использовать метод cursor.executemany(), который принимает один общий оператор обновления, а затем он передает его значениям, как я уже упоминал, каждое обновление отличается от того, что один общий оператор обновления не действительно работать для моего дела. Я также не хочу отправлять более 350 000 инструкций обновления по отдельности. В любом случае я могу объединить все мои операторы обновления и отправить их сразу?

Я пробовал использовать их все в одном запросе и использовать метод cursor.execute(), но он, похоже, не обновляет все строки.

Ответ 1

SQL # 1: CREATE TABLE t с любыми столбцами, которые могут потребоваться изменить. Сделайте все из них NULL (в отличие от NOT NULL).

SQL # 2: Сделайте объем INSERT (или LOAD DATA) всех необходимых изменений. Например, если изменить только first_name, заполните id и first_name, но у вас есть другие столбцы NULL.

SQL # 3-14:

UPDATE real_table
  JOIN t  ON t.id = real_table.id
  SET real_table.first_name = t.first_name
  WHERE t.first_name IS NOT NULL;
# ditto for each other column.

Все SQL-запросы, кроме # 1, будут трудоемкими. И, поскольку UPDATE должен построить журнал отмены, он может быть тайм-аут или иначе быть проблематичным. См. обсуждение chunking, если это необходимо.

При необходимости используйте такие функции, как COALESCE(), GREATEST(), IFNULL() и т.д.

Масса UPDATEs обычно подразумевает плохой дизайн схемы.

(Если Райан перескакивает с помощью "Ответ" вместо "комментария", он должен, вероятно, получить "щедрость".)

Ответ 2

Ваша лучшая производительность будет, если вы сможете кодировать свои "тесты" в самой логике SQL, чтобы вы могли свернуть все до нескольких операторов UPDATE. Или, по крайней мере, получить как можно больше так, чтобы меньшее количество строк нужно было обновлять индивидуально.

Например:

UPDATE tablename set firstname = [some logic]
WHERE [logic that identifies which rows need the firstname updated];

Вы не много описываете свои тесты, так что это трудно сделать. Но вы обычно можете получить довольно много логики в своем предложении WHERE с небольшим количеством работы.

Другим вариантом было бы поставить вашу логику в хранимую процедуру. Вы по-прежнему будете делать 350 000 обновлений, но, по крайней мере, они не все "идут по проводам". Я бы использовал это только как последнее средство; бизнес-логика должна храниться на прикладном уровне, когда это возможно, а хранимые процедуры делают ваше приложение менее портативным.