Decimals in python, Django and your DB

In programming we use numbers a lot. For counting integers are quite nice, but for many use cases we need “real” numbers which apart from integers also include numbers like 1/3, $ \sqrt{2} $, etc. Typically these are approximated with floating point numbers based on base 2. Traditionally we call them floats .

floats are great for many use cases, but they have their issues.

Why use decimals?

One big issue is that floats can’t express many of real numbers that are commonly used by humans in daily life without some approximation.

Unfortunately, most decimal fractions cannot be represented exactly as binary fractions. A consequence is that, in general, the decimal floating-point numbers you enter are only approximated by the binary floating-point numbers actually stored in the machine.

Here is an example:

>>> 1 + 1 + 1 == 3
True

>>> 0.1 + 0.1 + 0.1 == 0.3
False

That applies to other types of fractions as well, like 2/7 or 1/3 which also can’t be represented accurately.

Some numbers can’t be accurately expressed as fractions like square root of $ \sqrt{2} $ or $\pi$, but for the numbers that can (rational numbers), python has a solution!

fractions module - will help you deal with any type of fractional numbers
decimal module - is specialized with decimal numbers like 0.12 or 99999.999877 which is very useful in business and finance as this is what humans use for money.

This article will focus on the use cases that require decimal arithmetic (e.g. anything related to money) and therefore the use of decimals .

Decimals in action

Python’s standard library has a decimal module, similarly it’s very well supported in Django and majority of the SQL databases.

Decimals in python

To use decimal numbers in python you need to import the decimal module and create your decimals with decimal.Decimal

>>> import decimal
>>> decimal.Decimal(2) * decimal.Decimal("0.75")
Decimal('1.50')

When you create a decimal number, you should do it from either an integer or a string (or a specially formatted tuple, but that’s more complex). Creating a decimal from a float also works, but there are problems associated with that and I will explain it in the pitfalls section.

Python has a feature called operator overloading and thanks to that you can do normal arithmetic on your decimal numbers without a need to call methods, just with + , * etc.

You decimal.Decimal instances also have various methods, the most useful of which is quantize.

>>> Decimal('7.325').quantize(Decimal('.01'), rounding=ROUND_DOWN)
Decimal('7.32')

Decimal operations in python are configurable. You can easily change how many digits after full numbers you want and what will happen if you need some rounding or if your arithmetic is imprecise.

Luckily you don’t have to configure this per each decimal. Decimal instance, that would be very tedious! Python’s decimal module has a concept of a context where all the possible settings live and can be overridden.

There is a default global context that you can access and change, or you can use a local context that will be restored after you exit a code block.

Here are two examples of making the same change to the context, global one:

from decimal import getcontext

getcontext().prec = 42
Decimal(2).sqrt()

Local one:

from decimal import localcontext

with localcontext() as ctx:
    ctx.prec = 42   # Perform a high precision calculation
    s = calculate_something()

Contexts are pretty neat and they can allow you to configure a lot of things, like:

how to round
how much precision to use

decimal module also supports Signals which represent conditions that arise during computations, like “Inexact” or “Overflow”. These can be configured to raise exceptions through the decimal context or just simply accessed and cleared.

Decimals in Django

Django supports decimals through a special models.DecimalField.

You can configure the amount of digits and decimal places. The decimal places are included in the digits. Example model with a precise decimal:

class PriceHistory(models.Model):
    asset = models.ForeignKey(Asset, on_delete=models.CASCADE)
    value = models.DecimalField(max_digits=20, decimal_places=10)
    date = models.DateField()

Similarly, it’s supported in Django forms and django rest framework.

class PositionSerializer(serializers.ModelSerializer[Position]):
    asset = AssetSerializer()
    # Latest price is not defined on the model directly.
    latest_price = serializers.DecimalField(max_digits=20, decimal_places=10)

    class Meta:
        model = Position
        fields = [
            "id",
            "account",
            "asset",
            "quantity",
            "latest_price",
            "latest_price_date",
            "latest_exchange_rate",
            "realized_gain",
            "cost_basis",
        ]

There is no need to specify max_digits and decimal_places for a field that is both in the model and in the serializer.

Decimals in the SQL database

Decimals are well supported by majority of SQL databases (PostgreSQL and MySQL) as it’s part of the SQL standard.

Note: SQLite doesn’t have support for actual decimals. So things might work in unexpected ways.

The implementation between Databases might vary, for example PostgreSQL supports many more significant digits than MySQL (by multiple orders of magnitude).

The decimal SQL standard requires precision and scale to be configured when the decimal column is added. The meaning of those parameters is equivalent to Django’s max_digits and decimal_places.

So what can this precision cost us? More digits incurs storage overhead.

From PostgreSQL documentation:

Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the numeric type is more akin to varchar(***n***) than to char(***n***) .) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

So my Django decimal with 20 max digits and 10 decimal places will require 2 * 5 + 8 (worst case overhead), 18 bytes per number!

A double precision number would be 8 bytes.

That difference can add up pretty quickly.

Downsides and pitfalls

Now that you are convinced that decimals are often necessary and you have learned how they work, let’s talk about how you can shoot yourself in your foot using them.

Mixing floats and decimals

Decimal-float operations are not supported for a good reason.

>> decimal.Decimal("0.3") + 0.8

TypeError                                 Traceback (most recent call last)
<ipython-input-6-92fb256311c2> in <module>
----> 1 decimal.Decimal("0.3") + 0.8

TypeError: unsupported operand type(s) for +: 'decimal.Decimal' and 'float'

In the case above 0.8 is not exactly decimal.Decimal("0.8") it would have to be converted to a decimal and that could result in some imprecision. If you hit this type of errors, it means that some of your numbers are not decimals and they can make the result of your computations not exact in the end.

So what to do? Just convert the float to decimal, by using decimal.Decimal(my_float_number)? Well, no…

If you create decimals from floats you are loosing accuracy

This another pitfall, as every time you create decimals from floats, your number get imprecise.

>>> from decimal import Decimal
>>> Decimal("0.1") + Decimal("0.1") + Decimal("0.1") == Decimal("0.3")
True

>>> Decimal(0.1) + Decimal(0.1) + Decimal(0.1) == Decimal(0.3)
False

Instead, keep the numbers in your system as strings, integers or decimals without ever converting them to floats.

TIP: use type annotations to annotate all decimals.

Default context vs local context

I would generally advise against modifying a global context in decimals in your code, unless you have a simple program and you clearly do it in the beginning of its lifetime.

If you modify it at random points, you might end up with unexpected values in your context if you forget to clean them up!

A much better solution is to always keep it local with localcontext.

from decimal import localcontext

with localcontext() as ctx:
    ctx.prec = 42   # Perform a high precision calculation
    s = calculate_something()

You might even want to declare your custom decorator for that.

Django pitfall: forms/serializers out of sync with model field definition

It’s not only that you don’t need to specify max_digits and decimal_places on a matching field between model and serializer, you shouldn’t.

Why? Any time they get out of sync it can lead to problems, one type if it’s bigger on a serializer and one type if it’s smaller than on the model.

When serializer allows for more digits then user will eventually put more digits there. Their input will pass the validation but then fail at the database level raising an exception and failing to the user.

When serializer allows for less digits than the model field, then e.g. when the value is edited by the user it might get truncated even if user hasn’t really made any changes to the value. This can happen if the user changed other values, but the API is passing all the values again.

Both errors to the users and unexpected losses of precisions are pretty bad, so pay attention to this as it might be hard to catch in tests.

Django pitfall: precision of decimals and duplicate prevention

I’m a big fan of using get_or_create in Django to prevent duplicates when the value is created with the same arguments.

But the arguments won’t be the same if the decimal’s precision differs!

So be careful with that as decimal module precision defaults to 28 places and that is very likely not what you have configured on you model.

To deal with that you can override your context to use required precision when performing arithmetic, or even better you can use the quantize method on the value.

References & Resources

I hope you enjoyed this long post! Please follow me on twitter and share it if you found it useful!

Additional resources:

Tiny struggles