Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django: Using custom raw SQL inserts with executemany and MySQL

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

like image 435
Jonathan Livni Avatar asked Nov 28 '10 18:11

Jonathan Livni


People also ask

Should you use raw SQL in Django?

I would balance that by cautioning against overuse of the raw() and extra() methods.” Django project co-leader Jacob Kaplan-Moss says (paraphrased): “If it's easier to write a query using SQL than Django, then do it. extra() is nasty and should be avoided; raw() is great and should be used where appropriate.”

How can you see raw SQL queries running in Django?

Django gives you two ways of performing raw SQL queries: you can use Manager. raw() to perform raw queries and return model instances, or you can avoid the model layer entirely and execute custom SQL directly. Explore the ORM before using raw SQL!

Can we use SQL with Django?

Django officially supports five database management systems: PostgreSQL, MariaDB, MySQL, Oracle, and SQLite (Django, 2020).


1 Answers

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) '''
    
    
    query_list = build_query_list() 
    
    # here build_query_list() represents some function to populate
    # the list with multiple records
    # in the tuple format (value1, value2, value3).
    
    
    cursor.executemany(query, query_list)
    
    transaction.commit()

like image 101
jsh Avatar answered Sep 18 '22 23:09

jsh