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 ?

Daniel Roseman · Accepted Answer · 2011-06-17 07:57:19Z

42

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, 2011 at 7:57

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sam Dolan Over a year ago

+1: First option will do 2 or 3 queries, while the second will do 1.

Pierre de LESPINAY Over a year ago

Seems not bad. You mean Django can't perform a INSERT INTO ... ON DUPLICATE KEY UPDATE ... ?

Daniel Roseman Over a year ago

No, because that's a MySQL-specific extension, and Django works with a range of databases.

HostedMetrics.com Over a year ago

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().

smentek Over a year ago

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);"

Mp0int · Accepted Answer · 2017-09-26 12:38:19Z

11

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, 2017 at 12:38

answered Jun 17, 2011 at 8:30

Mp0int

18.8k16 gold badges88 silver badges119 bronze badges

3 Comments

Pierre de LESPINAY Over a year ago

The defaults keyword is also very interesting

Peter Thomassen Over a year ago

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 docs.djangoproject.com/en/1.11/ref/models/querysets/…. Consequently, the if part of your code is no-op.

Mp0int Over a year ago

@peterthomassen thanks for the notification. Updated the answer.

Pratyush · Accepted Answer · 2022-08-20 10:10:20Z

4

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']
)

answered Aug 20, 2022 at 10:10

Pratyush

5,5587 gold badges43 silver badges67 bronze badges

Collectives™ on Stack Overflow

Django - save() update on duplicate key

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related