how to use F expression to first cast a string to int, then add 1 to it then cast to string and update

Question

I have a DB column which is generic type for some stats(qualitative and quantitative info). Some values are string - type A and some values are numbers stored as string - type B. What i want to do is cast the B types to number then add one to them and cast back to string and store.

Metadata.objects.filter(key='EVENT', type='COUNT').update(value=CAST(F(CAST('value', IntegerField()) + 1), CharField())

What i want to do is avoid race conditions using F expression and update in DB.

https://docs.djangoproject.com/en/4.0/ref/models/expressions/#avoiding-race-conditions-using-f

It says in below post that casting and updating in db is possible for mysql Mysql Type Casting in Update Query I also know we can do arithmetic very easily on F expressions as it supports it and we can override functionality of add as well. How to do arthmetic on Django 'F' types?

How can i achieve Cast -> update -> cast -> store in Django queryset?

Som-1 · Answer 1 · 2022-07-06T12:20:48.150

2

Try using annotation as follows:

Metadata.objects
    .filter(key='EVENT', type='COUNT')
    .annotate(int_value=CAST('value', IntegerField()))
    .update(value=CAST(F('int_value') + 1, CharField())

Or maybe switching F and CAST works?

Metadata.objects
    .filter(key='EVENT', type='COUNT')
    .update(value=CAST(     # cast the whole expression below
        CAST(                 # cast a value
            F('value'),       # of field "value"
            IntegerField()    # to integer
        ) + 1,                # then add 1
        CharField()         # to char.
    )

I've added indentation, it helps sometimes to find the errors.

Also, doc says, CAST accepts field name, not an F-object. Maybe it works without F-object at all?

UPD: switched back to first example, it actually works :)

edited Jul 06 '22 at 12:20

answered Jul 06 '22 at 11:44

Som-1

601
7
16

1

OK, I wouldn't go with the annotate method as it achieves nothing substantial when mysql supports cast-update. i debugged the mysql queries formed in these two cases - 1 - `.update(value=Cast(Cast(F('value'), output_field=IntegerField()) + 1, output_field=CharField()))` 2 - `.update(value=Cast(Cast('value', IntegerField()) + 1, CharField()))` and both give the same output as expected. `UPDATE Metadata SET value = CAST((CAST(value AS signed integer) + 1) AS char) WHERE ( key = 'EVENT' AND type = 'COUNT' )` I followed this- [link](https://stackoverflow.com/a/10757261/4124628) – Mukul Anand Jul 06 '22 at 12:02

score 0 · Accepted Answer · answered Jul 06 '22 at 21:55

I believe the answer from @som-1 was informative but not substantiated with info or debugged data. I believe assuming is not always right.

I debugged the mysql queries formed in these two cases -

1 - Metadata.objects.update(value=Cast(Cast(F('value'), output_field=IntegerField()) + 1, output_field=CharField()))

2 - Metadata.objects.update(value=Cast(Cast('value', IntegerField()) + 1, CharField())) and both give the same output as expected.

UPDATE Metadata SET value = CAST((CAST(value AS signed integer) + 1) AS char) WHERE ( key = 'EVENT' AND  type = 'COUNT' )

Please find the link to add mysqld options to my.cnf and debug your queries. Location of my.cnf file on macOS enabling queries - https://tableplus.com/blog/2018/10/how-to-show-queries-log-in-mysql.html

Actually you do not need to debug in this case, as Django's Querysets have .query property, which shows you built query as string. You may just print them in the console. — Som-1, Jul 07 '22 at 06:38

how to use F expression to first cast a string to int, then add 1 to it then cast to string and update

2 Answers2