SQLAlchemy从DataFrame建表未生成主键，如何设置自增主键？

前置操作：备份数据 #

操作前务必备份symbols表及所有关联表的数据，避免操作失误导致数据丢失。

方法一：原生SQL直接修改表结构（最快捷） #

根据你使用的数据库类型，执行对应的SQL语句：

MySQL/MariaDB #

-- 1. 填充id列的空值（若存在）
UPDATE symbols SET id = (SELECT MAX(id) FROM symbols) + 1 WHERE id IS NULL;

-- 2. 将id设为非空自增主键
ALTER TABLE symbols
MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (id);

PostgreSQL #

-- 1. 填充id列的空值（若存在）
UPDATE symbols SET id = (SELECT COALESCE(MAX(id), 0) + 1 FROM symbols) WHERE id IS NULL;

-- 2. 将id设为非空自增主键
ALTER TABLE symbols
ALTER COLUMN id SET NOT NULL,
ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (START WITH (SELECT MAX(id)+1 FROM symbols)),
ADD PRIMARY KEY (id);

SQL Server #

-- 1. 先查询当前最大id值
SELECT MAX(id) FROM symbols;

-- 2. 填充id列的空值（若存在）
UPDATE symbols SET id = (SELECT ISNULL(MAX(id), 0) + 1 FROM symbols) WHERE id IS NULL;

-- 3. 设置id为非空
ALTER TABLE symbols
ALTER COLUMN id BIGINT NOT NULL;

-- 4. 添加主键约束
ALTER TABLE symbols
ADD CONSTRAINT PK_symbols_id PRIMARY KEY CLUSTERED (id);

-- 5. 设置自增（替换下方的[当前最大id+1]为步骤1查询到的值+1）
ALTER TABLE symbols
ALTER COLUMN id BIGINT IDENTITY([当前最大id+1], 1);

方法二：用SQLAlchemy代码化修改（适合集成到脚本） #

如果需要通过Python代码完成修改，可使用SQLAlchemy的元数据操作：

from sqlalchemy import MetaData, Table, PrimaryKeyConstraint, Identity

# 加载现有表结构
metadata = MetaData()
symbols_table = Table('symbols', metadata, autoload_with=engine)

# 先处理id列空值（可选，若已确保无空值可跳过）
with engine.connect() as conn:
    conn.execute("UPDATE symbols SET id = (SELECT COALESCE(MAX(id), 0) + 1 FROM symbols) WHERE id IS NULL")
    conn.commit()

# 修改id列为非空
symbols_table.c.id.alter(nullable=False)

# 根据数据库类型设置自增和主键
dialect = engine.dialect.name
if dialect == 'mysql':
    symbols_table.c.id.alter(autoincrement=True)
elif dialect == 'postgresql':
    max_id = engine.execute("SELECT MAX(id)+1 FROM symbols").scalar() or 1
    symbols_table.c.id.alter(identity=Identity(start=max_id))
elif dialect == 'mssql':
    max_id = engine.execute("SELECT ISNULL(MAX(id), 0) + 1 FROM symbols").scalar()
    symbols_table.c.id.alter(identity=(max_id, 1))

# 添加主键约束
symbols_table.append_constraint(PrimaryKeyConstraint('id'))

# 应用修改
metadata.create_all(engine, checkfirst=True)

后续新增标的的注意事项 #

• 后续向symbols表插入新标的时，不要手动指定id字段值，让数据库自动生成自增id
• 若要保证关联表数据一致性，可考虑给关联表的id字段添加外键约束指向symbols.id（可选）

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