django.db.utils.DataError: division by zero

Question

I am getting a error in the following line of code:

context['stock_margin'] = context['top_stock'].annotate(
                            Avg_purchase = ExpressionWrapper(F('total_puchase') / F('quantity_purchase'), output_field=FloatField()),
                            Avg_sales = ExpressionWrapper(F('total') / F('quantity'), output_field=FloatField())) 

I am new to django and cant able to figure out why this is happening..

Any idea anyone how to solve this error?

Answer 1

I am new to django and cant able to figure out why this is happening..

PostgreSQL raises an error if you divide by zero, since dividing by zero is undefined. Most other databases return NULL in that case.

Since django-2.2, we can use NullIf ^[Django-doc], and thus work with:

from django.db.models.functions.comparison import NullIf

context['stock_margin'] = context['top_stock'].annotate(
    Avg_purchase = ExpressionWrapper(
        F('total_puchase') / NullIf(F('quantity_purchase'), 0.0),
        output_field=FloatField()
    ),
    Avg_sales = ExpressionWrapper(
        F('total') / NullIf(F('quantity'), 0.0),
        output_field=FloatField()
    )
)

Otherwise you can use a Case ^[Django-doc]:

from django.db.models import Case, When

context['stock_margin'] = context['top_stock'].annotate(
    Avg_purchase = Case(
        When(quantity_purcase__gt=0, then=F('total_puchase') / F('quantity_purchase')),
        default=None,
        output_field=FloatField()
    ),
    Avg_sales = Case(
        When(quantity__gt=0, then=F('total') / F('quantity')),
        default=None,
        output_field=FloatField()
    )
)

In case the purchase_quantity/quantity is thus greater than zero, we perform the division, otherwise we return None. In both cases for records where we divide by zero, we will return None.

That being said, it is a bit odd that you have objects in your database with quantity and quantity_purchase being equal to zero. Depending on the use case, you might want to check where you create these objects, and whether you want to avoid that.