MySQLBulkInsertManager

manager.py

from django.db import models
from django.db.models import sql


class MySQLBulkInsertManager(models.Manager):
    """ bulkでinsert on duplicate updateするためのマネージャー
    """

    def bulk_insert_on_duplicate(self, obj_list, update_fields, batch_size=None):
        """  Usage
        Users.objects.bulk_insert_on_duplicate(
            [
                User(
                    name="kato",
                    age="29"
                ),
                User(
                    name="mosa",
                    age="27"
                ),
            ],
            update_fields=["name", "age"]
        )
        参考: https://groups.google.com/forum/#!topic/django-developers/DhCBpjbKpOY
        """

        from django.db import connections
        connection_name = self.model._meta.app_label
        connection = connections[connection_name]

        fields = self.model._meta.local_fields
        batch_size = (batch_size or max(connection.ops.bulk_batch_size(fields, obj_list), 1))

        _updates = ', '.join("`{0}`=VALUES(`{0}`)".format(field) for field in update_fields)
        on_duplicate = "ON DUPLICATE KEY UPDATE {0}".format(_updates)

        cursor = connection.cursor()

        for obj_batch in [obj_list[i:i + batch_size] for i in range(0, len(obj_list), batch_size)]:
            iq = sql.InsertQuery(self.model)
            iq.insert_values(fields, obj_batch)
            c = iq.get_compiler(using=connection_name)
            sql_list = c.as_sql()

            for query, args in sql_list:
                query = u"{0} {1}".format(query, on_duplicate)

                raw_sql = query % tuple(cursor.connection().literal(a) for a in args)
                cursor.execute(raw_sql)