Django - save() update on duplicate key

Question

I have little application which allows a user to rate a video.

The user can rate only once. So I have defined the uniqueness on the model.

But he should be able change his rate. So the save() should update on duplicate key

class VideoRate(models.Model):
  """Users can Rate each Video on the criterias defined for the topic"""
  user = models.ForeignKey(User)
  video = models.ForeignKey(VideoFile)
  crit = models.ForeignKey(VideoCrit)
  rate = models.DecimalField(max_digits=2, decimal_places=1, choices=RATE_CHOICES)
  class Meta:
    unique_together = (('user', 'video', 'crit'),)
    verbose_name = 'Video Rating'

If I

rate = VideoRate(user_id=1, video_id=1, crit_id=1, rate=2)
rate.save()

It's saving the rating, but if I

rate = VideoRate(user_id=1, video_id=1, crit_id=1, rate=3)
rate.save()

I get the normal error

IntegrityError: (1062, "Duplicate entry '1-1-1' for key 'user_id'")

Even if I use force_update=True (since based only on primary keys)

Is there a way to update the rating if it already exists without having to check data before ?

score 41 · Accepted Answer · answered Jun 17 '11 at 07:57

41

To update an existing rating, you actually have to have the one you want to update. If you know the object may not exist, use get_or_create:

rate, created = VideoRate.objects.get_or_create(user_id=1, video_id=1, crit_id=1)
rate.rate = 2
rate.save()

You can short-cut the process by using update():

VideoRate.objects.filter(user_id=1, video_id=1, crit_id=1).update(rate=2)

But this will silently fail if the rating does not exist - it won't create one.

answered Jun 17 '11 at 07:57

Daniel Roseman

588,541
66
880
895

+1: First option will do 2 or 3 queries, while the second will do 1. – Sam Dolan Jun 17 '11 at 08:00
Seems not bad. You mean Django can't perform a `INSERT INTO ... ON DUPLICATE KEY UPDATE ...` ? – Pierre de LESPINAY Jun 17 '11 at 09:11
3

No, because that's a MySQL-specific extension, and Django works with a range of databases. – Daniel Roseman Jun 17 '11 at 09:20
How about adding a put() method to the base model in conjunction with the use of unique_together? To me that looks cleaner than using get_or_create(). – HostedMetrics.com Apr 05 '13 at 23:27
You can do it, just use low level API like: connection.cursor().execute(sql) where sql is "INSERT INTO example (a, b, c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c);" – smentek May 19 '16 at 08:53

Mp0int · Answer 2 · 2017-09-26T12:38:19.723

10

First, you must check if the rating exists. So you may either use what Daniel Roseman said or use exists, but you can not solve this with a simple update since update do not create new records...

rating = 2
rate, created = VideoRate.objects.get_or_create(user_id=1, video_id=1, crit_id=1,
    defaults={'rate':rating})#if create, also save the rate infdormation

if not created:# update
    rate.rate = rating
    rate.save()

You can use defaults to pass exrta arguments, so if it is an insert, database record will be created with all required information and you do not need to update it again...

Documentation

Update: This answer is quite old just like the question. As @peterthomassen mention, Django now have update_or_create() method

edited Sep 26 '17 at 12:38

answered Jun 17 '11 at 08:30

Mp0int

18,172
15
83
114

The `defaults` keyword is also very interesting – Pierre de LESPINAY Oct 07 '11 at 07:03
1

The values from the `defaults` argument are also used for updating in the case where the object does not need to be created in the database, see https://docs.djangoproject.com/en/1.11/ref/models/querysets/#update-or-create. Consequently, the `if` part of your code is no-op. – Peter Thomassen Sep 25 '17 at 22:05
@peterthomassen thanks for the notification. Updated the answer. – Mp0int Sep 26 '17 at 12:38

score 1 · Answer 3 · answered Aug 20 '22 at 10:10

Django 4.1 has added the support for such conflicts. In the backend, it runs a INSERT...ON DUPLICATE KEY UPDATE query.

Example of above in a single query:

rate = VideoRate(user_id=1, video_id=1, crit_id=1, rate=2)
rate.save()

# updating rate object if it exists, else create one
rate = VideoRate(user_id=1, video_id=1, crit_id=1, rate=3)
VideoRate.objects.bulk_create(
    [rate],
    update_conflicts=True,
    update_fields=['rate']
)

Django - save() update on duplicate key

3 Answers3

Linked