pythonmysqldjangoexecutemany

Django cursor.executemany what's the preferred batch size for each "executemany"


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.


Solution

  • 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.