[Django] #23646: query set sql update to change different values by different keys

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

[Django] #23646: query set sql update to change different values by different keys

Django
#23646: query set sql update to change different values by different keys
----------------------------------------------+--------------------
     Reporter:  brillgen                      |      Owner:  nobody
         Type:  New feature                   |     Status:  new
    Component:  Database layer (models, ORM)  |    Version:  1.7
     Severity:  Normal                        |   Keywords:
 Triage Stage:  Unreviewed                    |  Has patch:  0
Easy pickings:  0                             |      UI/UX:  0
----------------------------------------------+--------------------
 Database backends like Postgresql support doing multiple value update for
 the same field in different rows using a single update query. Please see
 this sample below:

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

 However, such an interface is not exposed via the Django ORM. Would this
 be accepted as a patch? if so, would a list dictionary with the various
 fields:values to be updated be a good way to provide the input to ORM

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

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/051.b12ff1673220737fedbb838527f35b95%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #23646: query set sql update to change different values by different keys

Django
#23646: query set sql update to change different values by different keys
-------------------------------------+-------------------------------------
     Reporter:  brillgen             |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  1.7
  (models, ORM)                      |               Resolution:
     Severity:  Normal               |             Triage Stage:
     Keywords:                       |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by brillgen):

 * needs_better_patch:   => 0
 * needs_tests:   => 0
 * needs_docs:   => 0


Old description:

> Database backends like Postgresql support doing multiple value update for
> the same field in different rows using a single update query. Please see
> this sample below:
>
> http://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-
> query-using-postgresql
>
> However, such an interface is not exposed via the Django ORM. Would this
> be accepted as a patch? if so, would a list dictionary with the various
> fields:values to be updated be a good way to provide the input to ORM

New description:

 Database backends like Postgresql support doing multiple value update for
 the same field in different rows using a single update query. Please see
 this sample below:

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

 Example:
 Currently:
 {{{
 Books.objects.all().update(price=10)
 }}}

 Proposed (syntax can vary):
 {{{
 Books.objects.all().update({'pk': 1, 'price': 10}, {'pk': 2, 'price':
 25}])
 }}}

 Idea is to do it in sql for performance reasons but still use the ORM and
 not use a raw database connection.

 However, such an interface is not exposed via the Django ORM. Would this
 be accepted as a patch? if so, would a list dictionary with the various
 fields:values to be updated be a good way to provide the input to ORM

--

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

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.8c16a6aa108fc635a27ed8628f9ca53e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #23646: query set sql update to change different values by different keys

Django
In reply to this post by Django
#23646: query set sql update to change different values by different keys
-------------------------------------+-------------------------------------
     Reporter:  brillgen             |                    Owner:  nobody
         Type:  New feature          |                   Status:  closed
    Component:  Database layer       |                  Version:  1.7
  (models, ORM)                      |               Resolution:  wontfix
     Severity:  Normal               |             Triage Stage:
     Keywords:                       |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by freakboy3742):

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


Comment:

 The link you've provided suggests that the ORM example you've provided
 would be interpreted as:
 {{{
 UPDATE books_book
 SET
  price = 10 where pk = 1,
  price = 25 where pk = 2
 }}}

 So... what in the ORM statement identifies which column is to be used in
 the `WHERE` clause, and which is to be updated? As currently expressed,
 it's ambiguous. You'd need to modify the statement to be something like:

 {{{
 Books.objects.all().update('pk', values={'pk': 1, 'price': 10}, {'pk': 2,
 'price': 25})
 }}}

 which nominates which values in the data are for the select.

 However, even with those changes, I'm not sure I see why doing this as a
 single statement version would be preferable to:

 {{{
 Book.objects.filter(pk=1).update(price=10)
 Book.objects.filter(pk=2).update(price=25)
 }}}

 AFAIK, it's no faster on the database side (although feel free to prove me
 wrong on that), and I don't find a "one liner API" especially expressive
 or clear for this feature.

 For that reason, I'm marking this wontfix. If you feel the idea is still
 worth pursuing, feel free to start a discussion on django-developers and
 make your case. (I know you started one on django-users, but in this case,
 you need the eyeballs from -dev).

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

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.aa71bc6fab561e8b0252084fbab6bf75%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #23646: query set sql update to change different values by different keys

Django
In reply to this post by Django
#23646: query set sql update to change different values by different keys
-------------------------------------+-------------------------------------
     Reporter:  brillgen             |                    Owner:  nobody
         Type:  New feature          |                   Status:  closed
    Component:  Database layer       |                  Version:  1.7
  (models, ORM)                      |               Resolution:  wontfix
     Severity:  Normal               |             Triage Stage:
     Keywords:                       |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by shaib):

 On an old project, ~10 years ago and not Django based, we used a "bulk
 update" procedure that did:

 1) Bulk-insert the new records into a temporary table
 2) update in one statement using a join (SQL Server lets you do that)

 On hundreds of records, this was significantly faster than updating one-
 by-one (I no longer have access to any hard data, and it's irrelevant
 anyways).

 Anyway, I suspect a better API would involve passing a collection of
 objects and selecting just the fields to update:
 `Book.objects.update_many(books, 'price')`

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

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.f774a0bce4298cd7eb4ac171bd7c060e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #23646: query set sql update to change different values by different keys

Django
In reply to this post by Django
#23646: query set sql update to change different values by different keys
-------------------------------------+-------------------------------------
     Reporter:  brillgen             |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  1.7
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by brillgen):

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


Comment:

 Reopening even though it was closed by a core team member because another
 core member has reported the significant performance improvement.

 Also, there is an app called django-bulk-upate (https://github.com/aykut
 /django-bulk-update) which could/should be integrated into django core
 since we have a bulk_create and this corresponds well with the save (could
 be called bulk save if it makes sense). App works with an identical api to
 bulk_create and so should be a good candidate to be considered for
 inclusion in core.

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

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.9fb34cc3e689c17ffe2a6ecec1ea7136%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #23646: query set sql update to change different values by different keys (include django-bulk-update in core) (was: query set sql update to change different values by different keys)

Django
In reply to this post by Django
#23646: query set sql update to change different values by different keys (include
django-bulk-update in core)
-------------------------------------+-------------------------------------
     Reporter:  brillgen             |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  1.9
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by brillgen):

 * version:  1.7 => 1.9


Old description:

> Database backends like Postgresql support doing multiple value update for
> the same field in different rows using a single update query. Please see
> this sample below:
>
> http://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-
> query-using-postgresql
>
> Example:
> Currently:
> {{{
> Books.objects.all().update(price=10)
> }}}
>
> Proposed (syntax can vary):
> {{{
> Books.objects.all().update({'pk': 1, 'price': 10}, {'pk': 2, 'price':
> 25}])
> }}}
>
> Idea is to do it in sql for performance reasons but still use the ORM and
> not use a raw database connection.
>
> However, such an interface is not exposed via the Django ORM. Would this
> be accepted as a patch? if so, would a list dictionary with the various
> fields:values to be updated be a good way to provide the input to ORM

New description:

 **Update**
 django-bulk-upate (‚Äčhttps://github.com/aykut/django-bulk-update) which
 could/should be integrated into django core since we have a bulk_create
 and this corresponds well with the save (could be called bulk save if it
 makes sense). App works with an identical api to bulk_create and so should
 be a good candidate to be considered for inclusion in core.

 Database backends like Postgresql support doing multiple value update for
 the same field in different rows using a single update query. Please see
 this sample below:

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

 Example:
 Currently:
 {{{
 Books.objects.all().update(price=10)
 }}}

 Proposed (syntax can vary):
 {{{
 Books.objects.all().update({'pk': 1, 'price': 10}, {'pk': 2, 'price':
 25}])
 }}}

 Idea is to do it in sql for performance reasons but still use the ORM and
 not use a raw database connection.

 However, such an interface is not exposed via the Django ORM. Would this
 be accepted as a patch? if so, would a list dictionary with the various
 fields:values to be updated be a good way to provide the input to ORM

--

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

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.46e7abd6cdc4c3f98b10ed97743ca264%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #23646: query set sql update to change different values by different keys (include django-bulk-update in core)

Django
In reply to this post by Django
#23646: query set sql update to change different values by different keys (include
django-bulk-update in core)
-------------------------------------+-------------------------------------
     Reporter:  brillgen             |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  1.9
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by timgraham):

 * stage:  Unreviewed => Accepted


Comment:

 Tentatively accepting, pending discussion on the DevelopersMailingList to
 approve the idea and API.

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

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.b3eef482fbdbadde6688edec389a8525%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #23646: query set sql update to change different values by different keys (include django-bulk-update in core)

Django
In reply to this post by Django
#23646: query set sql update to change different values by different keys (include
django-bulk-update in core)
-------------------------------------+-------------------------------------
     Reporter:  Brillgen Developers  |                    Owner:  nobody
         Type:  New feature          |                   Status:  new
    Component:  Database layer       |                  Version:  1.9
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):

 * has_patch:  0 => 1


Comment:

 #29037 is a duplicate with [https://github.com/django/django/pull/9606 a
 patch].

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

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.825a24b1d953cb27765e99f4a09de16f%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #23646: query set sql update to change different values by different keys (include django-bulk-update in core)

Django
In reply to this post by Django
#23646: query set sql update to change different values by different keys (include
django-bulk-update in core)
-------------------------------------+-------------------------------------
     Reporter:  Brillgen Developers  |                    Owner:  Tom
                                     |  Forbes
         Type:  New feature          |                   Status:  assigned
    Component:  Database layer       |                  Version:  1.9
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:                       |             Triage Stage:  Accepted
    Has patch:  1                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Tom Forbes):

 * cc: Tom Forbes (added)
 * owner:  nobody => Tom Forbes
 * status:  new => assigned


Comment:

 Sorry for the duplicate, I'm not sure how my search missed this. I've got
 a patch that implements this at a basic level, there are some small
 optimizations that could be done at a later stage (including adding
 Postgres specific syntax).

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

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/066.e1b4eaf69897dbcee774bd04d7595dac%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.