Django: Using custom raw SQL inserts with executem

Question

I need to upload a lot of data to a MySQL db. For most models I use django's ORM, but one of my models will have billions (!) of instances and I would like to optimize its insert operation.

I can't seem to find a way to make executemany() work, and after googling it seems there are almost no examples out there.

I'm looking for the correct sql syntax + correct command syntax + correct values data structure to support an executemany command for the following sql statement:

INSERT INTO `some_table` (`int_column1`, `float_column2`, `string_column3`, `datetime_column4`) VALUES (%d, %f, %s, %s)

Yes, I'm explicitly stating the id (int_column1) for efficiency.

A short example code would be great

Answer 1

If you need to modify the data, bulk load with load data into a temporary table as is. Then apply modifications with an insert into select command. IME, this is by far the fastest way to get a lot of data into a table.

Answer 2

are you serisouly suggesting loading billions of rows (sorry instances) of data via some ORM data access layer - how long do you have ?

bulk load if possible - http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Answer 3

I'm not sure how to use the executemany() command, but you can use a single SQL INSERT statement to insert multiple records

Answer 4

Here's a solution that actually uses executemany() !

Basically the idea in the example here will work.

But note that in Django, you need to use the %s placeholder rather than the question mark.

Also, you will want to manage your transactions. I'll not get into that here as there is plenty of documentation available.

from django.db import connection,transaction
cursor = connection.cursor()



query=''' INSERT INTO table_name 
        (var1,var2,var3) 
        VALUES (%s,%s,%s) '''


queryList=buildQueryList() 

#here buildQueryList() represents some function to populate
#the list with multiple records
#in the tuple format (value1,value2,value3).


cursor.executemany(query,queryList)

transaction.commit()