I use the following code to do bulk insert with extended "insert into".
cursor = connections['default'].cursor()
sql = "INSERT INTO %s (%s) VALUES ([xxx], [xxx], ...) "
step = 1000
for l in range(0, len(values), step):
s_values = values[l:l+step]
cursor.executemany(sql, s_values)
My question here is if I have a lot of rows to insert, for example, 100, 000:
should I insert in one query.
or call multiple executemany with fixed step, such as 1000.
I read some articles, it is suggest to use 100.
I test my code with 100, 000 records to insert.
One executemany is faster than multiple.
I am not sure what should I do. Not sure whether I miss understood something here.
Please help to comment. Thanks.
MySQL documentation helps elaborate on that:
In most cases, the executemany() method iterates through the sequence of parameters, each time passing the current parameters to the the execute() method.
An optimization is applied for inserts: The data values given by the parameter sequences are batched using multiple-row syntax.