Decimal type with precision in Python

Most databases define an arbitrary precision number column type, where the precision and scale define the total and the fractional number…

Most databases define an arbitrary precision number column type, where the precision and scale define the total and the fractional number of digits, respectively. They naturally map to the type Decimal in Python.

from dataclasses import dataclass
from decimal import Decimal

@dataclass
class Product:
    """CREATE TABLE product (id integer, name varchar, price numeric(8, 2));
    """
    id: int
    name: str
    price: Decimal

If you try to insert the value 1.796 into the field, most databases will simply round it the value to the nearest scale (1.80). However, they will simply refuse any value greater than the precision (999,999.99).

I can’t count how many times I’ve had a database error from trying to insert or updating a value in a row that was greater than the defined precision, sometimes invalidating an entire load (by using COPY).

Python’s type annotation has come a long way in the last few years, but there’s no built-in annotation for the Decimal field type to allow me to specify the precision and scale. (By the way, this is also true for varchars with limit, but I’m not covering that here.) So I had to pull my own Decimal subclass:

from decimal import Decimal

class _Numeric(type):
    """Metaclass for Numeric and subclasses of it."""
    _cache = {}

    def __getitem__(cls, size):
        if size not in cls._cache:
            precision, scale = size
            cls._cache[size] = type(f'Numeric{precision}_{scale}', (Numeric,), {}, precision=precision, scale=scale)
        return cls._cache[size]

class Numeric(Decimal, metaclass=_Numeric):
    """For use with field annotations, such as:
        fieldname: Numeric[12, 2]
    """
    precision = None
    scale = None
    quantize_with = None
    min_value = None
    max_value = None

    def __init_subclass__(cls, /, precision, scale, **kwargs):
        super().__init_subclass__(**kwargs)
        cls.precision = precision
        cls.scale = scale
        cls.quantize_with = Decimal(f'1e{-scale}')
        cls.min_value = Decimal(f'-1e{precision - scale - 1}')
        cls.max_value = Decimal(f'1e{precision - scale}')

    def __new__(cls, *args, **kwargs):
        instance = super().__new__(cls, *args, **kwargs)
        if cls.precision and (instance < cls.min_value or instance >= cls.max_value):
            raise ValueError(f'Value out of bounds for Numeric[{cls.precision}, {cls.scale}]: {instance}')
        if cls.scale is not None:
            instance = instance.quantize(cls.quantize_with)
        return instance

There’s a log of magic going on here, thanks to metaclasses and magic methods. But now we can use the class above to instantiate values that are automatically validated, eg:

>>> Numeric('1.796')  # The same as Decimal
Decimal('1.796')
>>> Numeric[8,2]('1.796')
Decimal('1.80')
>>> Numeric[8,0]('1.796')
Decimal('2')
>>> Numeric[8,2]('1000000.00')
ValueError: Value out of bounds for Numeric[8, 2]: 1000000.00

We can then handle the exceptions the same way we would if they were invalid numbers, before even trying to insert them into the database.

Note: If you are using data classes, it’s not enough to declare the fields as Numeric[8,2] for example. You must use the type as a constructor when assigning the attribute. Python does not enforce the types of variables therefore you could even assign a string to that field.