Skip to content

十进制数字

在某些情况下，您可能需要能够存储具有精度保证的十进制数字。

这对于存储像 货币、价格、账户 等内容尤为重要，因为您希望确保不会发生四舍五入错误。

举个例子，如果您在 Python 中执行 1.1 + 2.2，您可能期望结果是 3.3，但实际上您会得到 3.3000000000000003：

>>> 1.1 + 2.2
3.3000000000000003

这是因为数字在“零和一”（二进制）中存储的方式。但 Python 有一个模块和一些类型，允许使用严格的十进制值。您可以在官方的 Python Decimal 文档 中了解更多内容。

由于数据库以与计算机相同的方式存储数据（即二进制），它们也会面临相同的问题。因此，数据库也有一个特殊的 十进制 类型。

在大多数情况下，这可能不会成为问题，例如视频中的观看次数或视频游戏中的生命条数。但正如您可以想象的那样，在处理 货币 和 财务 时，这一点尤为重要。

十进制类型

Pydantic 特别支持 Decimal 类型。

当您使用 Decimal 时，可以在 Field() 函数中指定支持的位数和小数位数。Pydantic 会验证这些（例如，在使用 FastAPI 时），同样的信息也将用于数据库列。

Info

对于数据库，SQLModel 将使用 SQLAlchemy 的 DECIMAL 类型。

SQLModel 中的十进制

假设数据库中每个英雄都有一笔钱。我们可以使用 condecimal() 函数将该字段设置为 Decimal 类型：

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 👇
👀 完整文件预览 
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()

这里我们声明 money 最多可以有 5 位数字（通过 max_digits），这包括整数部分（小数点左边）和小数部分（小数点右边）。

我们还声明小数部分（小数点右边）的位数为 3，所以在 money 字段中，我们可以有 3 位小数数字。这意味着我们将有 2 位用于整数部分 和 3 位用于小数部分。

✅ 例如，以下都是 money 字段的有效数字：

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

🚫 但是以下数字对于 money 字段来说是无效的：

  • 1.2345
  • 这个数字有超过 3 位的小数。
  • 123.234
  • 这个数字的总位数（整数部分和小数部分）超过了 5 位。
  • 123
  • 即使这个数字没有小数部分，我们仍然为小数部分预留了 3 位，这意味着我们只能为 整数部分 使用 2 位，而这个数字有 3 位整数。所以，允许的整数位数是 max_digits - decimal_places = 2。

/// 提示

确保根据您自己应用程序的需求调整位数和小数位数。🤓

///

使用十进制创建模型

在创建新模型时，您实际上可以传递普通的 (float) 数字，Pydantic 会自动将它们转换为 Decimal 类型，SQLModel 会将它们作为 Decimal 类型存储在数据库中（使用 SQLAlchemy）。

# 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 👇
👀 完整文件预览 
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()

选择十进制数据

然后，当使用十进制类型时，您可以确认它们确实避免了浮动数字的四舍五入错误：

# 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 👇
👀 完整文件预览 
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()

审查结果

现在，如果您运行此代码，它将输出 3.300，而不是意外的 3.3000000000000003：

fast →python app.py

Hero 1: id=1 secret_name='Dive Wilson' age=None name='Deadpond' money=Decimal('1.100')


Hero 2: id=3 secret_name='Tommy Sharp' age=48 name='Rusty-Man' money=Decimal('2.200')

Total money: 3.300

restart ↻

Warning

尽管在 Python 端支持并使用了十进制类型，但并非所有数据库都支持它。特别是，SQLite 不支持十进制，因此它会将其转换为它支持的浮动 NUMERIC 类型。

但大多数其他 SQL 数据库都支持十进制。🎉

Was this page helpful?