如何在Django中执行批量插入？ [英] How do I perform a batch insert in Django?

问题描述

在mysql中，您可以在n> 0的一个查询中为表插入多行：

In mysql, you can insert multiple rows to a table in one query for n > 0:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9), ..., (n-2, n-1, n);

有没有办法使用Django查询方法来实现上述？以下是一个例子：

Is there a way to achieve the above with Django queryset methods? Here's an example:

values = [(1, 2, 3), (4, 5, 6), ...]

for value in values:
    SomeModel.objects.create(first=value[0], second=value[1], third=value[2])

我相信以上是为for循环的每次迭代调用一个插入查询。我正在寻找一个单一的查询，是否可能在Django？

I believe the above is calling an insert query for each iteration of the for loop. I'm looking for a single query, is that possible in Django?

推荐答案

我最近自己寻找这样的一个东西 QuerySet.update（） ，我想象你也是这样）据我所知，目前的生产框架（截至今天为1.1.1）并不存在批量创建。我们最终为需要批量创建的模型创建了自定义管理器，并在该管理器上创建了一个具有VALUES参数序列的适当SQL语句的功能。

I recently looked for such a thing myself (inspired by QuerySet.update(), as I imagine you are too). To my knowledge, no bulk create exists in the current production framework (1.1.1 as of today). We ended up creating a custom manager for the model that needed bulk-create, and created a function on that manager to build an appropriate SQL statement with the sequence of VALUES parameters.

有些东西（如果这不行，请抱歉，希望我可以从我们的代码中调整出来）：

Something like (apologies if this does not work... hopefully I've adapted this runnably from our code):

from django.db import models, connection

class MyManager(models.Manager):

    def create_in_bulk(self, values):
        base_sql = "INSERT INTO tbl_name (a,b,c) VALUES "
        values_sql = []
        values_data = []

        for value_list in values:
            placeholders = ['%s' for i in range(len(value_list))]
            values_sql.append("(%s)" % ','.join(placeholders))
            values_data.extend(value_list)

        sql = '%s%s' % (base_sql, ', '.join(values_sql))

        curs = connection.cursor()
        curs.execute(sql, values_data)

class MyObject(models.Model):
    # model definition as usual... assume:
    foo = models.CharField(max_length=128)

    # custom manager
    objects = MyManager()

MyObject.objects.create_in_bulk( [('hello',), ('bye',), ('c', )] )

此方法确实具有特定数据库的风险。在我们的例子中，我们希望函数返回刚才创建的ID，所以我们在函数中有一个postgres特定的查询，从代表该对象的表的主键序列生成必需的ID数。也就是说，它在测试中的表现要好一些，而不是迭代数据并发出单独的QuerySet.create（）语句。

This approach does run the risk of being very specific to a particular database. In our case, we wanted the function to return the IDs just created, so we had a postgres-specific query in the function to generate the requisite number of IDs from the primary key sequence for the table that represents the object. That said, it does perform significantly better in tests versus iterating over the data and issuing separate QuerySet.create() statements.

这篇关于如何在Django中执行批量插入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！