generate update query using django orm

Question

I need to implement this query using django orm:

update table set field=field+1 where id=id

I don't whant to use this:

o = model.objects.get(id=id)
o.field+=1
o.save()

because it use select and when update, and not thread safe.

How to implement this via orm?

score 37 · Accepted Answer · answered Mar 10 '13 at 11:05

37

Both the previous answerers have part of the solution: you should use update in conjunction with F():

Model.objects.filter(id=id).update(field=F('field') +1))

Note this does an in-place UPDATE without any need for SELECT at all.

answered Mar 10 '13 at 11:05

Daniel Roseman

588,541
66
880
895

score 2 · Answer 2 · edited Sep 23 '16 at 22:44

2

You can use update, details can be found in the documentation

edited Sep 23 '16 at 22:44

David Schumann

13,380
9
75
96

answered Mar 10 '13 at 07:24

linbo

2,393
3
22
45

OP wants to update attributes based on existing fields in a thread safe way, which is done by F() Expressions. I don't think update has such functionality. are you sure? – jurgenreza Mar 10 '13 at 08:26

score 1 · Answer 3 · answered Mar 20 '21 at 22:17

for django v 2.2 (and maybe 3+) SQL statement compilation function could be:

from django.db.models.sql.subqueries import UpdateQuery

def compile_update_query(query_set, update_kwargs):
    query = query_set.query.chain(UpdateQuery)
    query.add_update_values(update_kwargs)
    return str(query)

where query_set is MyShinyModel.objects.filter(id=42) and update_kwargs is a dictionary of model fields to update

score 0 · Answer 4 · answered Oct 28 '20 at 19:03

If you need to convert the UPDATE ORM query to UPDATE SQL statement without performing it

The update() method is applied instantly and returns the number of rows matched by the query (django 1.8 docs)

def generate_update_sql(queryset, update_kwargs):
    """  [checked on django 1.8]
    Converts queryset with update_kwargs
    like if was: queryset.update(**update_kwargs)
    """
    from django.db.models import sql

    query = queryset.query.clone(sql.UpdateQuery)
    query.add_update_values(update_kwargs)
    compiler = query.get_compiler(queryset.db)
    sql, params = compiler.as_sql()
    return sql % params

Usage

qs = Event.objects.exclude(app='some')
update_kwargs = dict(description='test', action='action')
generate_update_sql(qs, update_kwargs)

Will return

UPDATE `api_event` SET `description` = test, `action` = action WHERE NOT (`api_event`.`app` = some)

generate update query using django orm

4 Answers4

Linked