十进制数字¶
在某些情况下，您可能需要能够存储十进制数字，并保证其精度。
如果您存储货币、价格、账户等内容，这一点尤其重要，因为您希望确保不会出现舍入误差。
例如，如果您打开 Python 并将 1.1 + 2.2 相加，您会期望看到 3.3，但实际上您会得到 3.3000000000000003
>>> 1.1 + 2.2
3.3000000000000003
这是因为数字以“1 和 0”（二进制）的方式存储。但是 Python 有一个模块和一些类型来处理严格的十进制值。您可以在官方 Python Decimal 文档 中阅读更多相关信息。
由于数据库以与计算机相同的方式（以二进制形式）存储数据，因此它们也会遇到相同类型的问题。因此，它们也有一种特殊的十进制类型。
在大多数情况下，这可能不是问题，例如测量视频中的观看次数或视频游戏中的生命值。但您可以想象，这在处理金钱和金融时尤其重要。
十进制类型¶
Pydantic 对 Decimal 类型 提供了特殊支持。
当您使用 Decimal 时，您可以在 Field() 函数中指定要支持的位数和小数位数。它们将由 Pydantic 验证（例如，当使用 FastAPI 时），并且相同的信息也将用于数据库列。
信息
对于数据库，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)
# 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。
提示
请务必根据您自己的应用程序中的需求调整位数和小数位数。 🤓
创建带有十进制的模型¶
创建新模型时，您实际上可以传递普通 (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()
选择十进制数据¶
然后，当使用十进制类型时，您可以确认它们确实避免了浮点数的舍入误差
# 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 端支持和使用了十进制类型，但并非所有数据库都支持它。 特别是，SQLite 不支持十进制，因此它会将其转换为它支持的相同的浮点 NUMERIC 类型。
但是大多数其他 SQL 数据库都支持十进制。 🎉