Что лучше - выполнить (INSERT) или выполнить многие (INSERT)

Question

Ситуация: нужно вставить довольно много данных в базу данных SQLite.

Проблема: есть два утверждения, которые мы можем использовать для вставки данных:

data = [("111", "222", "333"), ("AAA", "BBB", "CCC"), ("XXX", "YYY", "ZZZ")]

#method1
for item in data:
    cursor.execute("INSERT INTO table(value1, value2, value3) VALUES (?,?,?)", item)
    conn.commit()

#method2
cursor.execute("INSERT INTO table(value1, value2, value3) VALUES(?,?,?)", data)
conn.commit()

Вопрос: Если игнорировать скорости, какой из них лучше практиковать с точки зрения программирования? И, если возможно, объясните почему.

Answer 1

С точки зрения практики программирования, кроме скорости, нет никакой разницы. Тем не мение...

Подготовленные заявления хороши. Тем не менее, массовая вставка создает массу привязок переменных, а SQLite имеет верхний предел количества параметров хоста, которые он может обработать, по умолчанию 999.

Таким образом, мульти-вставка хороша для игры, но для реальных данных вы будете использовать цикл. Один хороший совет, который я могу предложить, это то, что вы захотите обернуть цикл в транзакцию, потому что без нее, AFAIK, каждая вставка будет автоматической транзакцией, что существенно повлияет на время. (Кроме того, фиксация в конце цикла, а не внутри цикла.)

РЕДАКТИРОВАТЬ: Согласно документации Python,

По умолчанию модуль sqlite3 неявно открывает транзакции перед оператором языка изменения данных (DML) (т.е. INSERT/UPDATE/DELETE/REPLACE) и неявно фиксирует транзакции перед оператором без DML, без запроса (т. е. с чем угодно, кроме SELECT или вышеупомянутое).

Итак, ваш код в #method1 делает [BEGIN], INSERT, COMMIT, [BEGIN], INSERT, COMMIT... с BEGIN неявно отправляется Python для начала транзакции, и COMMIT явно заканчивая это. Если вы структурируете свой код следующим образом:

for item in data:
    cursor.execute("INSERT INTO table(value1, value2, value3) VALUES (?,?,?)", item)
conn.commit()

тогда у вас есть один неявный BEGIN в начале, много INSERTS и один явный COMMIT в конце. Это должно ускорить ваш код в 10-20 раз или около того.