Decimal Numbers

In some cases you might need to be able to store decimal numbers with guarantees about the precision.

This is particularly important if you are storing things like currencies, prices, accounts, and others, as you would want to know that you wouldn't have rounding errors.

As an example, if you open Python and sum 1.1 + 2.2 you would expect to see 3.3, but you will actually get 3.3000000000000003:

>>> 1.1 + 2.2
3.3000000000000003

This is because of the way numbers are stored in "ones and zeros" (binary). But Python has a module and some types to have strict decimal values. You can read more about it in the official Python docs for Decimal.

Because databases store data in the same ways as computers (in binary), they would have the same types of issues. And because of that, they also have a special decimal type.

In most cases this would probably not be a problem, for example measuring views in a video, or the life bar in a videogame. But as you can imagine, this is particularly important when dealing with money and finances.

Decimal Types

Pydantic has special support for Decimal types.

When you use Decimal you can specify the number of digits and decimal places to support in the Field() function. They will be validated by Pydantic (for example when using FastAPI) and the same information will also be used for the database columns.

Info

For the database, SQLModel will use SQLAlchemy's DECIMAL type.

Decimals in SQLModel

Let's say that each hero in the database will have an amount of money. We could make that field a Decimal type using the condecimal() function:

Python 3.10+Python 3.7+

from decimal import Decimal

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)
    money: Decimal = Field(default=0, max_digits=5, decimal_places=3)

# More code here later 👇

from decimal import Decimal
from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)
    money: Decimal = Field(default=0, max_digits=5, decimal_places=3)

# More code here later 👇

👀 Full file preview

Python 3.10+Python 3.7+

from decimal import Decimal

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)
    money: Decimal = Field(default=0, max_digits=5, decimal_places=3)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson", money=1.1)
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", money=0.001)
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, money=2.2)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        hero_1 = results.one()
        print("Hero 1:", hero_1)

        statement = select(Hero).where(Hero.name == "Rusty-Man")
        results = session.exec(statement)
        hero_2 = results.one()
        print("Hero 2:", hero_2)

        total_money = hero_1.money + hero_2.money
        print(f"Total money: {total_money}")


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

from decimal import Decimal
from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)
    money: Decimal = Field(default=0, max_digits=5, decimal_places=3)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson", money=1.1)
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", money=0.001)
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, money=2.2)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        hero_1 = results.one()
        print("Hero 1:", hero_1)

        statement = select(Hero).where(Hero.name == "Rusty-Man")
        results = session.exec(statement)
        hero_2 = results.one()
        print("Hero 2:", hero_2)

        total_money = hero_1.money + hero_2.money
        print(f"Total money: {total_money}")


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

Here we are saying that money can have at most 5 digits with max_digits, this includes the integers (to the left of the decimal dot) and the decimals (to the right of the decimal dot).

We are also saying that the number of decimal places (to the right of the decimal dot) is 3, so we can have 3 decimal digits for these numbers in the money field. This means that we will have 2 digits for the integer part and 3 digits for the decimal part.

✅ So, for example, these are all valid numbers for the money field:

• 12.345
• 12.3
• 12
• 1.2
• 0.123
• 0

🚫 But these are all invalid numbers for that money field:

• 1.2345
• This number has more than 3 decimal places.
• 123.234
• This number has more than 5 digits in total (integer and decimal part).
• 123
• Even though this number doesn't have any decimals, we still have 3 places saved for them, which means that we can only use 2 places for the integer part, and this number has 3 integer digits. So, the allowed number of integer digits is max_digits - decimal_places = 2.

Tip

Make sure you adjust the number of digits and decimal places for your own needs, in your own application. 🤓

Create models with Decimals

When creating new models you can actually pass normal (float) numbers, Pydantic will automatically convert them to Decimal types, and SQLModel will store them as Decimal types in the database (using SQLAlchemy).

Python 3.10+Python 3.7+

# Code above omitted 👆

def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson", money=1.1)
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", money=0.001)
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, money=2.2)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()

# Code below omitted 👇

# Code above omitted 👆

def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson", money=1.1)
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", money=0.001)
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, money=2.2)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()

# Code below omitted 👇

👀 Full file preview

Python 3.10+Python 3.7+

from decimal import Decimal

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)
    money: Decimal = Field(default=0, max_digits=5, decimal_places=3)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson", money=1.1)
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", money=0.001)
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, money=2.2)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        hero_1 = results.one()
        print("Hero 1:", hero_1)

        statement = select(Hero).where(Hero.name == "Rusty-Man")
        results = session.exec(statement)
        hero_2 = results.one()
        print("Hero 2:", hero_2)

        total_money = hero_1.money + hero_2.money
        print(f"Total money: {total_money}")


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

from decimal import Decimal
from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)
    money: Decimal = Field(default=0, max_digits=5, decimal_places=3)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson", money=1.1)
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", money=0.001)
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, money=2.2)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        hero_1 = results.one()
        print("Hero 1:", hero_1)

        statement = select(Hero).where(Hero.name == "Rusty-Man")
        results = session.exec(statement)
        hero_2 = results.one()
        print("Hero 2:", hero_2)

        total_money = hero_1.money + hero_2.money
        print(f"Total money: {total_money}")


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

Select Decimal data

Then, when working with Decimal types, you can confirm that they indeed avoid those rounding errors from floats:

Python 3.10+Python 3.7+

# Code above omitted 👆

def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        hero_1 = results.one()
        print("Hero 1:", hero_1)

        statement = select(Hero).where(Hero.name == "Rusty-Man")
        results = session.exec(statement)
        hero_2 = results.one()
        print("Hero 2:", hero_2)

        total_money = hero_1.money + hero_2.money
        print(f"Total money: {total_money}")

# Code below omitted 👇

# Code above omitted 👆

def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        hero_1 = results.one()
        print("Hero 1:", hero_1)

        statement = select(Hero).where(Hero.name == "Rusty-Man")
        results = session.exec(statement)
        hero_2 = results.one()
        print("Hero 2:", hero_2)

        total_money = hero_1.money + hero_2.money
        print(f"Total money: {total_money}")

# Code below omitted 👇

👀 Full file preview

Python 3.10+Python 3.7+

from decimal import Decimal

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)
    money: Decimal = Field(default=0, max_digits=5, decimal_places=3)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson", money=1.1)
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", money=0.001)
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, money=2.2)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        hero_1 = results.one()
        print("Hero 1:", hero_1)

        statement = select(Hero).where(Hero.name == "Rusty-Man")
        results = session.exec(statement)
        hero_2 = results.one()
        print("Hero 2:", hero_2)

        total_money = hero_1.money + hero_2.money
        print(f"Total money: {total_money}")


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

from decimal import Decimal
from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)
    money: Decimal = Field(default=0, max_digits=5, decimal_places=3)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson", money=1.1)
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", money=0.001)
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48, money=2.2)

    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)

        session.commit()


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.name == "Deadpond")
        results = session.exec(statement)
        hero_1 = results.one()
        print("Hero 1:", hero_1)

        statement = select(Hero).where(Hero.name == "Rusty-Man")
        results = session.exec(statement)
        hero_2 = results.one()
        print("Hero 2:", hero_2)

        total_money = hero_1.money + hero_2.money
        print(f"Total money: {total_money}")


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

Review the results

Now if you run this, instead of printing the unexpected number 3.3000000000000003, it prints 3.300:

$ python app.py

// Some boilerplate and previous output omitted 😉

// The type of money is Decimal('1.100')
Hero 1: id=1 secret_name='Dive Wilson' age=None name='Deadpond' money=Decimal('1.100')

// More output omitted here 🤓

// The type of money is Decimal('1.100')
Hero 2: id=3 secret_name='Tommy Sharp' age=48 name='Rusty-Man' money=Decimal('2.200')

// No rounding errors, just 3.3! 🎉
Total money: 3.300

Warning

Although Decimal types are supported and used in the Python side, not all databases support it. In particular, SQLite doesn't support decimals, so it will convert them to the same floating NUMERIC type it supports.

But decimals are supported by most of the other SQL databases. 🎉