CODEWITHSUNDEEP
djangodjango-orm
Adam Nelson
Adam Nelson

Reputation: 8090

Django equivalent of SQL REPLACE

Is there a Django ORM best practice for this SQL:

REPLACE app_model SET field_1 = 'some val', field_2 = 'some val';

Assumption: field_1 or field_2 would have a unique key on them (or in my case on both), otherwise this would always evaluate to an INSERT.

Edit:

My best personal answer right now is this, but it's 2-3 queries where 1 should be possible:

    from django.core.exceptions import ValidationError
    try:
        Model(field_1='some val',field_2='some val').validate_unique()
        Model(field_1='some val',field_2='some val',extra_field='some val').save()
    except ValidationError:
        Model.objects.filter(field_1='some val',field_2='some val').update(extra_field='some val')

Upvotes: 6

Views: 6876

Answers (3)

Anentropic
Anentropic

Reputation: 33833

Since Django 1.7 you can use update_or_create method:

obj, created = Person.objects.update_or_create(
    first_name='John',
    last_name='Lennon',
    defaults={'profession': 'musician'},
)

Upvotes: 11

byoungb
byoungb

Reputation: 1801

I think the following is more efficient.

(obj, created) = Model.objects.get_or_create(
   field_1 = 'some val',
   field_2 = 'some_val',
   defaults = {
      'extra_field': 'some_val'
   },
)
if not created and obj.extra_field != 'some_val':
   obj.extra_field = 'some_val'
   obj.save(
      update_fields = [
         'extra_field',
      ],
   )

This will only update the extra_field if the row was not created and needs to be updated.

Upvotes: 5

eternicode
eternicode

Reputation: 6935

You say you want REPLACE, which I believe is supposed to delete any existing rows before inserting, but your example indicates you want something more like UPSERT.

AFAIK, django doesn't support REPLACE (or sqlite's INSERT OR REPLACE, or UPSERT). But your code could be consolidated:

obj, created = Model.objects.get_or_create(field_1='some val', field_2='some_val')
obj.extra_field = 'some_val'
obj.save()

This of course assumes that either field_1, field_2, or both are unique (as you've said).

It's still two queries (a SELECT for get_or_create, and an INSERT or UPDATE for save), but until an UPSERT-like solution comes along (and it may not for a long, long time), it may be the best you can do.

Upvotes: 11

Related Questions