How to use Peewee to make CASE SQL queries?-CodePudding

I have a table in postgres that holds a priority integer for each row. The priority needs to update whenever I add a new row or change the priority of an existing one for all of the other rows in the table.

One way to do it in pure SQL would be with a CASE query:

# example of editing priority 5 to 2 and fixing the rest
set Priority = case when Priority = 5 then 2
                    when Priority > 1 then Priority   1
                    else Priority
               end

Question: How can I use the peewee ORM in python to facilitate this query?

I couldn't find a way to do it in the documentation. Although the documentation offers some great examples, my use case doesn't fit any one of the examples.

CodePudding user response：

You can use peewee.Case (docs: http://docs.peewee-orm.com/en/latest/peewee/api.html#Case ). It is well documented.

class Task(Model):
    priority = IntegerField()

q = Task.update(priority=Case(None, (
    (Task.priority == 5, 2),
    (Task.priority > 1, Task.priority   1)),
    Task.priority)
q.execute()