如何在SQLAlchemy 2.0中映射SQLite自增主键解决插入失败问题

问题 #

在使用SQLAlchemy 2.0结合SQLite数据库时，映射生成的主键列导致无法执行插入操作。

实体类代码 #

import datetime
from typing import Optional

from sqlalchemy import String, TIMESTAMP, Integer
from sqlalchemy import func
from sqlalchemy.dialects import postgresql, sqlite
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.schema import CreateTable
from typing_extensions import Annotated

timestamp = Annotated[datetime.datetime, mapped_column(nullable=False, server_default=func.CURRENT_TIMESTAMP()),]


class Base(DeclarativeBase):
    type_annotation_map = {datetime.datetime: TIMESTAMP(timezone=True), }


class UserClass(Base):
    __tablename__ = "user"

    id: Mapped[Integer] = mapped_column(Integer, primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(50))
    fullname: Mapped[Optional[str]] = mapped_column(String(50), nullable=False)
    nickname: Mapped[Optional[str]] = mapped_column(String(30))
    date: Mapped[datetime.datetime]
    status: Mapped[str]
    created_at: Mapped[timestamp] = mapped_column(server_default=func.UTC_TIMESTAMP())
    updated_at: Mapped[timestamp] = mapped_column(server_default=func.UTC_TIMESTAMP())

生成的表结构 #

CREATE TABLE user (
    id BIGINT NOT NULL,
    name VARCHAR(50) NOT NULL,
    fullname VARCHAR(50) NOT NULL,
    nickname VARCHAR(30),
    date TIMESTAMP NOT NULL,
    status VARCHAR NOT NULL,
    created_at TIMESTAMP DEFAULT (UTC_TIMESTAMP()) NOT NULL,
    updated_at TIMESTAMP DEFAULT (UTC_TIMESTAMP()) NOT NULL,
    PRIMARY KEY (id)
);

插入命令及错误 #

插入语句：

INSERT INTO user (name, fullname, nickname, date, status, created_at, updated_at) VALUES ('John', 'John Doe', 'Johnny', '2024-09-21 19:09:51.484769', 'active', '2024-09-21 19:09:51.490379', '2024-09-21 19:09:51.490400')

错误信息：

sqlite3.IntegrityError) NOT NULL constraint failed: user.id

需求：通过映射列创建SQLite格式的主键 id integer primary key autoincrement。

解决方案 #

调整主键映射代码 #

要生成符合SQLite要求的自增主键，需修改主键字段的类型指定，改用SQLite专属的INTEGER类型：

import datetime
from typing import Optional

from sqlalchemy import String, TIMESTAMP
from sqlalchemy import func
from sqlalchemy.dialects.sqlite import INTEGER  # 导入SQLite专属整数类型
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped, mapped_column
from typing_extensions import Annotated

timestamp = Annotated[datetime.datetime, mapped_column(nullable=False, server_default=func.CURRENT_TIMESTAMP()),]


class Base(DeclarativeBase):
    type_annotation_map = {datetime.datetime: TIMESTAMP(timezone=True), }


class UserClass(Base):
    __tablename__ = "user"

    # 修改主键为SQLite专属INTEGER类型
    id: Mapped[int] = mapped_column(INTEGER, primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(50))
    fullname: Mapped[Optional[str]] = mapped_column(String(50), nullable=False)
    nickname: Mapped[Optional[str]] = mapped_column(String(30))
    date: Mapped[datetime.datetime]
    status: Mapped[str]
    created_at: Mapped[timestamp] = mapped_column(server_default=func.UTC_TIMESTAMP())
    updated_at: Mapped[timestamp] = mapped_column(server_default=func.UTC_TIMESTAMP())

原理说明 #

• 通用Integer类型在SQLite中会被映射为BIGINT，而SQLite的自增主键机制仅对INTEGER类型生效
• 搭配primary_key=True和autoincrement=True，SQLAlchemy会自动生成INTEGER PRIMARY KEY AUTOINCREMENT的表结构语句

验证生成结果 #

修改后生成的表结构如下，符合SQLite自增主键要求：

CREATE TABLE user (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) NOT NULL,
    fullname VARCHAR(50) NOT NULL,
    nickname VARCHAR(30),
    date TIMESTAMP NOT NULL,
    status VARCHAR NOT NULL,
    created_at TIMESTAMP DEFAULT (UTC_TIMESTAMP()) NOT NULL,
    updated_at TIMESTAMP DEFAULT (UTC_TIMESTAMP()) NOT NULL
);

此时执行原插入语句，SQLite会自动为id字段生成自增数值，不再触发NOT NULL约束错误。

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