Decimal 数字¶
在某些情况下，您可能需要能够存储带精度保证的十进制数字。
这在存储货币、价格、账户等信息时尤为重要，因为您会希望确保没有舍入误差。
举个例子，如果您打开 Python 并计算 1.1 + 2.2，您会期望看到 3.3，但实际上您会得到 3.3000000000000003
>>> 1.1 + 2.2
3.3000000000000003
这是因为数字以“一和零”（二进制）的方式存储。但 Python 有一个模块和一些类型可以处理严格的十进制值。您可以在官方的 Python Decimal 文档中阅读更多相关信息。
由于数据库以与计算机相同的方式（二进制）存储数据，它们也会遇到相同类型的问题。正因如此，它们也有特殊的十进制类型。
在大多数情况下，这可能不是问题，例如测量视频观看次数或视频游戏中的生命条。但正如您可以想象的，这在处理金钱和财务时尤为重要。
Decimal 类型¶
Pydantic 对 Decimal 类型有特殊支持。
当您使用 Decimal 时，您可以在 Field() 函数中指定支持的数字位数和小数位数。它们将由 Pydantic 进行验证（例如在使用 FastAPI 时），并且相同的信息也将用于数据库列。
信息
对于数据库，SQLModel 将使用 SQLAlchemy 的 DECIMAL 类型。
SQLModel 中的 Decimals¶
假设数据库中的每个英雄都有一定数量的钱。我们可以使用 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)
# 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()
这里我们声明 money 最多可以有 5 位数字，通过 max_digits 指定，这包括整数部分（小数点左侧）和小数部分（小数点右侧）。
我们还声明小数位数（小数点右侧）为 3，因此 money 字段中的这些数字可以有 3 位小数。这意味着我们将有 2 位整数部分和 3 位小数部分。
✅ 因此，例如，这些都是 money 字段的有效数字
12.34512.3121.20.1230
🚫 但这些都是 money 字段的无效数字
1.2345- 这个数字的小数位数超过 3 位。
123.234- 这个数字的总位数（整数和小数部分）超过 5 位。
123- 尽管这个数字没有任何小数，我们仍然为它们预留了 3 个位置，这意味着我们只能使用 2 个位置作为整数部分，而这个数字有 3 位整数。所以，允许的整数位数是
max_digits-decimal_places= 2。
提示
请确保根据您自己的应用程序需求调整数字位数和小数位数。🤓
创建带 Decimals 的模型¶
在创建新模型时，您实际上可以传递普通的 (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 👇
👀 完整文件预览
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()
选择 Decimal 数据¶
然后，在处理 Decimal 类型时，您可以确认它们确实避免了浮点数带来的舍入误差
# 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
$ 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
警告
尽管 Python 端支持并使用 Decimal 类型，但并非所有数据库都支持它。特别是 SQLite 不支持 decimals，因此它会将其转换为它支持的相同浮点 NUMERIC 类型。
但大多数其他 SQL 数据库都支持 decimals。🎉