Menu
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
435
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.”
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!
Django officially supports five database management systems: PostgreSQL, MariaDB, MySQL, Oracle, and SQLite (Django, 2020).
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
