Я работаю с модулем MySQLdb в Python для взаимодействия с базой данных. У меня есть ситуация, когда есть очень большой список (десятки тысяч элементов), который мне нужно вставить в виде строк в таблицу.
Мое решение прямо сейчас состоит в том, чтобы сгенерировать большой оператор INSERT в виде строки и выполнить его.
Есть ли более разумный способ?
Существует более умный способ.
Проблема с объемными вставками заключается в том, что по умолчанию включен autocommit, в результате чего каждый оператор insert сохраняется в стабильном хранилище до следующего вставка может инициироваться.
Как указано на странице руководства:
По умолчанию MySQL работает с autocommit режим включен. Это означает, что как только когда вы выполняете инструкцию, которая обновляет (изменяет) таблицу, MySQL сохраняет обновление на диске, чтобы сделать это постоянны. Чтобы отключить режим автосохранения, используйте следующую инструкцию:
SET autocommit=0;
После отключения автосохранение, установив переменная autocommit до нуля, изменения для безопасных для транзакций таблиц (таких как для InnoDB, BDB или NDBCLUSTER) немедленно не становятся постоянными. Вы должны использовать COMMIT для хранения ваших изменения на диск или ROLLBACK для игнорирования изменения.
Это довольно распространенная функция систем RDBM, которые предполагают, что целостность базы данных имеет первостепенное значение. Он делает объемные вставки размером порядка 1 с на вставку вместо 1 мс. Альтернатива создания надстрочного оператора вставки пытается достичь этого единственного коммита, подверженного риску перегрузки анализатора SQL.
Если вам нужно вставить очень большой объем данных, почему вы пытаетесь вставить их все в один insert? (Это приведет к ненужной нагрузке на вашу память при создании этой большой строки insert, а также при ее выполнении. Также это не очень хорошее решение, если ваши данные будут вставлены очень сильно.)
Почему бы вам не поместить одну строку за команду insert в db и поместить все строки с помощью for...loop и зафиксировать все изменения в конце?
con = mysqldb.connect(
host="localhost",
user="user",
passwd="**",
db="db name"
)
cur = con.cursor()
for data in your_data_list:
cur.execute("data you want to insert: %s" %data)
con.commit()
con.close()
(Поверьте, это очень быстро, но если вы получаете более медленные результаты, значит, ваш autocommit должен быть True. Установите его на False, как говорит msw.)
Пока вы делаете это как единственный INSERT, а не тысячи отдельных, тогда да, это лучший способ сделать это. Следите за тем, чтобы не превышать размер пакета mysqls max и при необходимости скорректируйте его. Например, это устанавливает максимальный размер пакета сервера до 32 Мб. Вы должны сделать то же самое и на клиенте.
mysqld --max_allowed_packet=32M