Django:使用带有 executemany 和 MySQL 的自定义原始 SQL 插入

2022-01-09 00:00:00 django sql insert mysql

我需要将大量数据上传到 MySQL 数据库.对于大多数模型，我使用 django 的 ORM，但我的一个模型将有数十亿(！)个实例，我想优化它的插入操作.

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.

我似乎找不到让 executemany() 工作的方法，谷歌搜索后似乎几乎没有示例.

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

我正在寻找正确的 sql 语法 + 正确的命令语法 + 正确的值数据结构，以支持以下 sql 语句的 executemany 命令:

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)

是的，我明确说明了 id (int_column1) 以提高效率.

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

一个简短的示例代码会很棒

A short example code would be great

推荐答案

这是一个实际使用 executemany() 的解决方案！

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

here 示例中的想法基本上是可行的.

Basically the idea in the example here will work.

但请注意，在 Django 中，您需要使用 %s 占位符而不是问号.

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()

相关文章