[Django] #29037: Add a bulk_update method to models

Django

unread,

Jan 18, 2018, 7:23:48 PM1/18/18

to django-...@googlegroups.com

#29037: Add a bulk_update method to models
-------------------------------------+-------------------------------------
Reporter: Tom | Owner: nobody
Forbes |
Type: New | Status: new
feature |
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Currently it's not easily or neatly possible to update multiple rows with
differing values using the ORM. Most people who need to update a number of
models with a value that's distinct to each model need to issue N queries.
This is akin to the situation that lead to the addition of bulk_create.

Updating multiple rows with differing values in a single query is indeed
possible in SQL, and Postgres has some specific syntax for it[1]. Other
databases can use a CASE/WHEN:

{{{#!python
SomeModel.object.update(
some_field=Case(
When(id=1, then=Value('Field value for ID=1')),
When(id=2, then=Value('Field value for ID=2'))
)
)
}}}

This isn't particularly elegant and cannot take advantage of specific DB
features (like pg UPDATE FROM), and batching is hard. An API similar to
bulk_create would be nice:

{{{#!python
SomeModel.objects.bulk_update(list_of_models, batch_size=100)
}}}

There is some prior art in the django-bulk-update package[2], which has
some impressive performance numbers in it's readme.

1. https://stackoverflow.com/questions/18797608/update-multiple-rows-in-
same-query-using-postgresql

2. https://github.com/aykut/django-bulk-update

--
Ticket URL: <https://code.djangoproject.com/ticket/29037>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Jan 18, 2018, 7:35:52 PM1/18/18

to django-...@googlegroups.com

#29037: Add a bulk_update method to models
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Tom Forbes:

Old description:

New description:

Currently it's not easily or neatly possible to update multiple rows with
differing values using the ORM. Most people who need to update a number of
models with a value that's distinct to each model need to issue N queries.
This is akin to the situation that lead to the addition of bulk_create.

Updating multiple rows with differing values in a single query is indeed
possible in SQL, and Postgres has some specific syntax for it[1]. Other
databases can use a CASE/WHEN:

{{{#!python
SomeModel.object.filter(id__in=[1,2]).update(

some_field=Case(
When(id=1, then=Value('Field value for ID=1')),
When(id=2, then=Value('Field value for ID=2'))
)
)
}}}

This isn't particularly elegant and cannot take advantage of specific DB
features (like pg UPDATE FROM), and batching is hard. An API similar to
bulk_create would be nice:

{{{#!python
SomeModel.objects.bulk_update(list_of_models, batch_size=100)
}}}

There is some prior art in the django-bulk-update package[2], which has
some impressive performance numbers in it's readme.

1. https://stackoverflow.com/questions/18797608/update-multiple-rows-in-
same-query-using-postgresql

2. https://github.com/aykut/django-bulk-update

--

--
Ticket URL: <https://code.djangoproject.com/ticket/29037#comment:1>

Django

unread,

Jan 18, 2018, 7:37:13 PM1/18/18

to django-...@googlegroups.com

#29037: Add a bulk_update method to models
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Tom Forbes:

Old description:

> Currently it's not easily or neatly possible to update multiple rows with

> differing values using the ORM. Most people who need to update a number
> of models with a value that's distinct to each model need to issue N
> queries. This is akin to the situation that lead to the addition of
> bulk_create.
>
> Updating multiple rows with differing values in a single query is indeed
> possible in SQL, and Postgres has some specific syntax for it[1]. Other
> databases can use a CASE/WHEN:
>
> {{{#!python

> SomeModel.object.filter(id__in=[1,2]).update(

> some_field=Case(
> When(id=1, then=Value('Field value for ID=1')),
> When(id=2, then=Value('Field value for ID=2'))
> )
> )
> }}}
>
> This isn't particularly elegant and cannot take advantage of specific DB
> features (like pg UPDATE FROM), and batching is hard. An API similar to
> bulk_create would be nice:
>
> {{{#!python
> SomeModel.objects.bulk_update(list_of_models, batch_size=100)
> }}}
>
> There is some prior art in the django-bulk-update package[2], which has
> some impressive performance numbers in it's readme.
>
> 1. https://stackoverflow.com/questions/18797608/update-multiple-rows-in-
> same-query-using-postgresql
>
> 2. https://github.com/aykut/django-bulk-update

New description:

Currently it's not easily or neatly possible to update multiple rows with
differing values using the ORM. Most people who need to update a number of
models with a value that's distinct to each model need to issue N queries.
This is akin to the situation that lead to the addition of bulk_create.

Updating multiple rows with differing values in a single query is indeed
possible in SQL, and Postgres has some specific syntax for it[1]. Other
databases can use a CASE/WHEN:

{{{#!python
SomeModel.object.filter(id__in=[1,2]).update(

some_field=Case(
When(id=1, then=Value('Field value for ID=1')),
When(id=2, then=Value('Field value for ID=2'))
)
)
}}}

This isn't particularly elegant and cannot take advantage of specific DB
features (like pg UPDATE FROM), and batching is hard. An API similar to
bulk_create would be nice:

{{{#!python
SomeModel.objects.bulk_update(list_of_models, batch_size=100,
fields=['some_field'])
}}}

There is some prior art in the django-bulk-update package[2], which has
some impressive performance numbers in it's readme.

1. https://stackoverflow.com/questions/18797608/update-multiple-rows-in-
same-query-using-postgresql

2. https://github.com/aykut/django-bulk-update

--

--
Ticket URL: <https://code.djangoproject.com/ticket/29037#comment:2>

Django

unread,

Jan 20, 2018, 11:32:38 AM1/20/18

to django-...@googlegroups.com

#29037: Add a bulk_update method to models
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tom Forbes):

PR: https://github.com/django/django/pull/9606

--
Ticket URL: <https://code.djangoproject.com/ticket/29037#comment:3>

Django

unread,

Jan 20, 2018, 11:46:33 AM1/20/18

to django-...@googlegroups.com

#29037: Add a bulk_update method to models
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tom Forbes):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/29037#comment:4>

Django

unread,

Feb 13, 2018, 10:59:36 AM2/13/18

to django-...@googlegroups.com

#29037: Add a bulk_update method to models
-------------------------------------+-------------------------------------

Reporter: Tom Forbes | Owner: nobody

Type: New feature | Status: closed

Component: Database layer | Version: master
(models, ORM) |

Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* status: new => closed
* resolution: => duplicate

Comment:

[https://groups.google.com/d/topic/django-developers/nHH-
gKPCs6Q/discussion django-developers thread]