Using ORM to retrieve values (REAL) from SQLite database to round to 2 decimal places

[Wai Yeung]

Hello,

To be able to get a REAL value from a SQLite database rounded to 2 decimal places, I had to use the following RAW SQL query:

SELECT 

            printf("%.2f", round(orig_total,2)),

FROM final_billing_tbl

I would like to be able to duplicate the functionality of the in ORM to create a queryset.  I need the result in the queryset because later in the procedure, I search the queryset as follows:

queryset = queryset.filter( Q( orig_total  __icontains=search_value) )

The search_value would be a value that would rounded to 2 decimal places.

Thanks in Advance,

Wai

[Lalit Suthar]

this can help
https://docs.djangoproject.com/en/2.2/ref/models/database-functions/#round

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/b89b27f3-a11c-4b2c-8473-59420c7c3f26n%40googlegroups.com.

[Wai Yeung]

Thanks for the quick response Lalit,

Django "Round" rounds to the nearest integer whereas SQLite "round" allows you to pass in a second parameter to to round "rounded to Y digits to the right of the decimal point" (https://sqlite.org/lang_corefunc.html#round).

That said, since Django "Round" doesn't support this second parameter, I still used it by multiplying the value by 1000, then rounded the value and then divided the value by 1000.  This still didn't result in a value with 2 decimal places as some values have a '0' in the second decimal place (i.e. "2.10" would be "2.1"

To address the search portion, the search values for any search value that had trailing zeroes were modified.  So if the search was for "2.10", the modified search would be for "2.1".

Cheers,

Wai

You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/D72IUtA1uqQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAGp2JVHr3hQ03XGnOGO0i13gWhw1%3D%2Bv51BcbHAOfAb0u5V4_tg%40mail.gmail.com.

[Lalit Suthar]

That's great the way you found the workaround :D 

We can also try writing raw sql queries to use sql functions https://docs.djangoproject.com/en/2.2/topics/db/sql/#executing-custom-sql-directly

But that has some security issues since we have to pass a search parameter into it.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAKDLG%3DRq-YhQeGKfFk5vy24Kqdyyk-FG6OzQRnE0DMEwPtzbMg%40mail.gmail.com.