SQLModel插入PostgreSQL时Datetime类型TypeError问题解决

问题场景 #

在Python应用中使用SQLModel ORM向PostgreSQL插入新记录时，遇到与datetime对象相关的TypeError。

核心代码 #

from datetime import datetime, timezone
import uuid
from sqlmodel import Field, SQLModel, Relationship, UniqueConstraint
from typing import List

class UserBase(SQLModel):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    phone_number: str = Field(max_length=255)
    phone_prefix: str = Field(max_length=10)

class User(UserBase, table=True):
    __table_args__ = (
        UniqueConstraint("phone_number", "phone_prefix", name="phone_numbe_phone_prefix_constraint"),
    )
    registered_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))
    interests: List["Interest"] = Relationship(back_populates="user")

报错信息 #

E   TypeError: can't subtract offset-naive and offset-aware datetimes

asyncpg/pgproto/./codecs/datetime.pyx:152: TypeError

引发的后续异常显示插入语句中registered_at被映射为TIMESTAMP WITHOUT TIME ZONE，但传入的是带UTC时区的datetime对象：

self = <sqlalchemy.dialects.postgresql.asyncpg.AsyncAdapt_asyncpg_cursor object at 0x108b78ee0>
operation = 'INSERT INTO "user" (id, phone_number, phone_prefix, registered_at) VALUES ($1::UUID, $2::VARCHAR, $3::VARCHAR, $4::TIMESTAMP WITHOUT TIME ZONE)'
parameters = ('d9999373-a43d-4154-935c-f28f13f17d3e', '8545227945', '+342', datetime.datetime(2024, 2, 29, 18, 25, 54, 21935, tzinfo=datetime.timezone.utc))

问题原因 #

SQLModel默认将datetime类型映射为PostgreSQL的TIMESTAMP WITHOUT TIME ZONE，但插入的是**时区感知（offset-aware）**的datetime对象，asyncpg在处理时因类型不匹配抛出错误。

解决方案 #

方案1：将模型字段映射为带时区的TIMESTAMP（推荐） #

修改registered_at字段的定义，通过sa_type指定SQLAlchemy的带时区TIMESTAMP类型，让数据库字段存储带时区的时间：

from datetime import datetime, timezone
import uuid
from sqlmodel import Field, SQLModel, Relationship, UniqueConstraint
from typing import List
# 导入SQLAlchemy的TIMESTAMP类型
from sqlalchemy.sql.sqltypes import TIMESTAMP

class UserBase(SQLModel):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    phone_number: str = Field(max_length=255)
    phone_prefix: str = Field(max_length=10)

class User(UserBase, table=True):
    __table_args__ = (
        UniqueConstraint("phone_number", "phone_prefix", name="phone_numbe_phone_prefix_constraint"),
    )
    registered_at: datetime = Field(
        default_factory=lambda: datetime.now(timezone.utc),
        sa_type=TIMESTAMP(timezone=True)
    )
    interests: List["Interest"] = Relationship(back_populates="user")

说明：

• 该配置会让SQLModel在创建表时，将registered_at字段设为PostgreSQL的TIMESTAMP WITH TIME ZONE类型
• asyncpg能正确识别并处理时区感知的datetime对象，避免类型不匹配错误

方案2：转换为无时区datetime（不推荐） #

如果不需要保留时区信息，可以将时区感知的datetime转换为无时区对象（但会丢失时区上下文，仅适合特定场景）：

registered_at: datetime = Field(
    default_factory=lambda: datetime.now(timezone.utc).replace(tzinfo=None)
)

额外注意事项 #

如果数据库表已经创建，需要执行表结构迁移，将registered_at字段从TIMESTAMP WITHOUT TIME ZONE修改为TIMESTAMP WITH TIME ZONE，确保模型与数据库结构一致。

内容的提问来源于stack exchange，提问作者softshipper