CODEWITHSUNDEEP
pythondjango
user2492364
user2492364

Reputation: 6693

django use Case When to compose sql query

I have data format like this: 

[
 {
  "name":"abc",
  "number":120,
  "total":500
 },
 {
  "name":"def",
  "number":30,
  "total":400
 }

]

and I have a django query logic like this:

Table.objects.update(
      number = Case(
          When(name=data[0]['name'], then=Value(data[0]['number'])),
          When(name=data[1]['name'], then=Value(data[1]['number'])),
          When(name=data[2]['name'], then = Value(data[2]['number'])),
          ...
          When(name=data[499]['name'], then = Value(data[499]['number'])),
          default=F('number'),
      )
  )

I want to use code to generate the When part

so I use list comprehension to generate it, and then use ','.join(query_list) to remove the bracket [ ]
But here is problem : it said TypeError: sequence item 0: expected string, When found

I can't convert it to string , because When(name=data[499]['name'], then = Value(data[499]['number']) should be a object. How can I do to fix this???

query_list = [When(name=data[i]['name'], then=Value(data[i]['number'])) for i,item in enumerate(data)]

part_of_sql= ','.join(query_list)
print(part_of_sql)


Table.objects.update(
   number=Case(
     part_of_sql, 
     default=F('number'),
   )
)

Upvotes: 1

Views: 118

Answers (1)

neverwalkaloner
neverwalkaloner

Reputation: 47354

Try unpack query_list when passing it to Case with * syntax:

Case(*query_list, default=F('number')

Upvotes: 2

Related Questions