如何通过SQLAlchemy让SQLite使用不重复的自增ID？

我在开发一个存储脚本信息的项目，需要在SQLite数据库里避免主键ID复用。了解到SQLite默认的整数主键会在删除行后复用ID，不符合需求；而INTEGER PRIMARY KEY AUTOINCREMENT类型会记录历史最大ID值，新条目ID永远比历史最大值大，不会复用已删除的ID。官方文档说明：

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table.

目前我用SQLAlchemy+Alembic做数据库迁移，只有通过原生SQL先建表再添加列的方式能实现预期效果：

op.execute(sa.text("CREATE TABLE scripts (id INTEGER PRIMARY KEY AUTOINCREMENT)"))
op.add_column('scripts',
    sa.Column('filename', sa.Unicode(length=64), nullable=False)
)
op.add_column('scripts',
    sa.Column('upload_date', sa.DateTime(), server_default=sa.text('(CURRENT_TIMESTAMP)'),
              nullable=False)
)

但这种依赖原生SQL的方式不够规范，想找更贴合ORM工具的实现方法。

我的模型字段定义如下：

id = db.Column(db.Integer, primary_key=True, autoincrement=True)
filename = db.Column(db.Unicode(64), nullable=False)
upload_date = db.Column(db.DateTime, nullable=False, server_default=func.now(), onupdate=func.now())

但Alembic自动生成的迁移脚本达不到预期效果：

def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('scripts',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('filename', sa.Unicode(length=64), nullable=False),
sa.Column('upload_date', sa.DateTime(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
sa.PrimaryKeyConstraint('id')

解决方案 #

1. 模型层直接使用sqlite_autoincrement参数（最规范） #

SQLAlchemy针对SQLite提供了sqlite_autoincrement专属参数，直接在主键列定义中添加即可，不需要修改迁移脚本，完全符合ORM使用规范。

修改后的模型字段：

id = db.Column(db.Integer, primary_key=True, autoincrement=True, sqlite_autoincrement=True)

这样配置后，Alembic生成的迁移脚本会自动为SQLite数据库生成INTEGER PRIMARY KEY AUTOINCREMENT的列定义，完美满足避免ID复用的需求。

2. 手动调整Alembic迁移脚本 #

如果不想修改模型，也可以直接调整自动生成的迁移脚本，确保主键列的定义符合SQLite要求：

def upgrade():
    op.create_table('scripts',
        sa.Column('id', sa.INTEGER(), nullable=False),
        sa.Column('filename', sa.Unicode(length=64), nullable=False),
        sa.Column('upload_date', sa.DateTime(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.PrimaryKeyConstraint('id', name=op.f('pk_scripts'))
    )
    # 针对SQLite添加AUTOINCREMENT约束
    op.execute(sa.text("ALTER TABLE scripts MODIFY COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT"))

或者更简洁地直接用原生SQL建表（比先建表再加列更规整）：

def upgrade():
    op.execute(sa.text("""
        CREATE TABLE scripts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            filename VARCHAR(64) NOT NULL,
            upload_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
        )
    """))

这种方式仅在迁移脚本中使用原生SQL，模型层依然保持ORM风格。

3. 自定义SQLAlchemy类型（进阶方案） #

创建一个自定义类型映射SQLite的INTEGER PRIMARY KEY AUTOINCREMENT，让模型可以直接使用：

from sqlalchemy import Integer, TypeDecorator

class SQLiteAutoIncrement(TypeDecorator):
    impl = Integer

    def __repr__(self):
        return "INTEGER PRIMARY KEY AUTOINCREMENT"

# 模型中使用
id = db.Column(SQLiteAutoIncrement(), primary_key=True)

需要注意的是，这种方式可能需要配合SQLAlchemy的编译器扩展，确保生成正确的DDL语句，适合有定制化需求的场景。

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