Django: using custom raw SQL inserts using executemany and MySQL

Question

I need to load a lot of data into a MySQL database. For most models, I use django ORM, but one of my models will have billions (!) Instances, and I would like to optimize its insert operation.

It seems I can’t find a way to make executeemany () work, and after googling it seems that there are almost no examples there.

I am looking for the correct sql syntax + the correct command syntax + the correct value data structure to support the executeemany 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 explicitly specify id (int_column1) for efficiency.

A short code example will be great

Answer 1

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

In principle, the idea will work in the example here .

But note that in Django you need to use the% s sign instead of the question mark.

In addition, you will want to manage your transactions. I will not go into this because a lot of documentation is 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() 

Answer 2

You suggest loading billions of rows (sorry instances) of data through some level of access to ORM data - how much time do you have?

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

Answer 3

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

Answer 4

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