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

Using Django
1

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

2

There are actually two different things being discussed here.

First, your raw SQL query does not return a real rounded to 2 decimal places. It’s returning the character string representation of a real value to 2 decimal places.

This distinction is important because of your use of the icontains clause in your search.

To handle your first case (returning a real rounded to 2 decimal places), you can create a custom function class to do this:

from django.db.models import Func

class Round2(Func):
    function = 'ROUND'
    template='%(function)s(%(expressions)s, 2)'

Then, if you want it returned as a string for use in your query filter, you can use the Cast function to cast this result to a CharField.

To get your final result, this is what I’ve come up with:
qs = qs.annotate(rounded=Cast(Round2('orig_total'), output_field=CharField())).filter(rounded__contains=search_value)
(I hope I have all the parens matched correctly)

3

Hi Ken,

Thanks for the quick response.

Round2() worked out great, however for values that evaluated to, for example, “2.10”, the returned value is “2.1”. So even when casted to CharField, the string would be “2.1”.

Currently, as a workaround to address the search portion, the search values for any search value that had trailing zeroes are modified. So if the search was for “2.10”, the modified search would be for “2.1”.

Ideally, I would just like the qs value to have “2.10”.

On a side note that I found that regardless if value cast as a CharField or Float, filtering for the search_value still able to filter for it.

Cheers,
Wai