django_bulk_upsert.py

def bulk_upsert(model, fields, values, by):
    stmt = """
    WITH
    -- write the new values
    data_set(%s) AS (
      VALUES %s
    ),
    -- update existing rows
    upsert AS (
      UPDATE %s t
      SET %s
      FROM data_set
      WHERE t.%s = data_set.%s
      RETURNING t.%s
    )
    -- insert missing rows
    INSERT INTO %s (%s)
    SELECT %s
    FROM data_set
    WHERE NOT EXISTS (SELECT 1
                      FROM upsert up
                      WHERE up.%s = data_set.%s)
    """
    table_name = model._meta.db_table
    set_fields = ['{0}=data_set.{1}'.format(f, f) for f in fields if f != by]
    select_fields = ['data_set.{0}'.format(f) for f in fields]
    fields_str = ', '.join(fields)
    values_placeholders = ('%s, ' * len(values))[:-2]
    formatted_sql = stmt % (
        fields_str,
        values_placeholders,
        table_name,
        ', '.join(set_fields),
        by,
        by,
        by,
        table_name,
        fields_str,
        ', '.join(select_fields),
        by,
        by
    )

    with connection.cursor() as cursor:
        cursor.execute(formatted_sql, values)

    return formatted_sql