如何在SQLAlchemy ORM与SQLite中让func.now()返回带小数秒的时间

问题背景 #

当SQLite表的主键设为默认时间戳时，因func.now()返回的时间戳不含小数秒，短时间内插入多条记录会触发UNIQUE constraint failed错误。尽管SQLAlchemy文档标注默认时间存储格式包含微秒，但实际生成的时间戳无小数部分。

复现代码 #

import datetime as dt
import time
from sqlalchemy import create_engine
from sqlalchemy import func, Column, DateTime, Time
from sqlalchemy.engine.base import Connection, Engine
from sqlalchemy.orm import Session
from sqlalchemy.orm import DeclarativeBase, Mapped, MappedAsDataclass, mapped_column

class Base(DeclarativeBase):
    """Base class for the database."""

class Table1(Base, MappedAsDataclass):
    """Table."""

    __tablename__ = "dummy table"

    timestamp: Mapped[dt.datetime] = mapped_column(primary_key=True, 
                                                   autoincrement=False, 
                                                   server_default=func.now())
    data: Mapped[int] = mapped_column(nullable = True)

if  __name__ == "__main__":
    engine: Engine = create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)
    record1 = Table1(data=1)
    record2 = Table1(data=2)
    with Session(engine) as session:
        session.add(record1)
        session.commit()
        # time.sleep(2)
        session.add(record2)
        session.commit()

现象说明 #

取消time.sleep(2)注释后插入记录，得到的时间戳无小数秒：

2023-06-14 12:32:17 1
2023-06-14 12:32:19 2

与文档中YYYY-MM-DD HH:MM:SS.ffffff的默认存储格式不符。

解决方案 #

方法1：替换为SQLite原生带小数秒的时间函数 #

SQLite支持datetime('now', '6f')语法返回带6位小数的当前时间，直接替换func.now()即可：

1. 先导入text工具：

from sqlalchemy import text

2. 修改表字段的server_default配置：

timestamp: Mapped[dt.datetime] = mapped_column(
    primary_key=True, 
    autoincrement=False, 
    server_default=text("datetime('now', '6f')")
)

方法2：自定义SQLite DATETIME类型并全局应用 #

通过自定义DATETIME类型，确保时间的存储、读取都保留小数秒：

1. 定义自定义时间类型：

from sqlalchemy.dialects.sqlite import DATETIME

class SQLiteDateTimeWithFractional(DATETIME):
    def __init__(self):
        super().__init__(
            format="%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d",
            storage_format="%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
        )

2. 注册自定义类型到SQLite方言：

from sqlalchemy.dialects import registry

registry.register("sqlite.datetime", __name__, "SQLiteDateTimeWithFractional")
engine = create_engine("sqlite:///:memory:")

3. 同时配合方法1中的server_default配置，确保默认时间带小数秒。

验证效果 #

修改后短时间插入多条记录，时间戳会包含微秒部分，示例：

2023-06-14 12:32:17.123456 1
2023-06-14 12:32:17.789012 2

可避免唯一约束冲突。

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