FastAPI/SQLModel继承Base类时如何将id列设为表首列

现有schemas.py代码如下：

from typing import Optional
from datetime import date
from sqlmodel import SQLModel, Field

class EimBase(SQLModel):
    fimno: Optional[str]
    spv_component_id: Optional[int] 
    eim_technology_name: Optional[str]
    eim_technology_version: Optional[str]
    obsolete_start_date: Optional[date]
    actual_remediation_date: Optional[date]
    intended_remediation_date: Optional[date]
    issuestatus: Optional[str]
    current_manu_phase: Optional[str]
    current_manu_phase_start: Optional[date]
    current_manu_phase_end: Optional[date]
    remediation_type: Optional[str]
    issue_component_id: Optional[str]
    middleware_instance_name: Optional[str]

class Eim(EimBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    spv_component_id: Optional[int] = Field(default=None, index=True)

Postgres生成的建表SQL如下，可见id列位于最后：

CREATE TABLE stage_eim (
        fimno VARCHAR,
        spv_component_id INTEGER,
        eim_technology_name VARCHAR,
        eim_technology_version VARCHAR,
        obsolete_start_date DATE,
        actual_remediation_date DATE,
        intended_remediation_date DATE,
        issuestatus VARCHAR,
        current_manu_phase VARCHAR,
        current_manu_phase_start DATE,
        current_manu_phase_end DATE,
        remediation_type VARCHAR,
        issue_component_id VARCHAR,
        middleware_instance_name VARCHAR,
        id SERIAL NOT NULL,
        PRIMARY KEY (id)
)

需求是让id列为表的第一列，但直接将Eim类定义在EimBase之上会触发spv_component_id相关报错，如何在保留现有Schema结构的前提下实现？

可以通过新增一个仅包含主键id的基类，让Eim优先继承该基类，再继承原有的EimBase，这样就能让id列排在最前面，同时保留原有结构且避免报错。

修改后的代码如下：

from typing import Optional
from datetime import date
from sqlmodel import SQLModel, Field

# 新增仅包含主键的基类
class EimIdBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)

class EimBase(SQLModel):
    fimno: Optional[str]
    spv_component_id: Optional[int] 
    eim_technology_name: Optional[str]
    eim_technology_version: Optional[str]
    obsolete_start_date: Optional[date]
    actual_remediation_date: Optional[date]
    intended_remediation_date: Optional[date]
    issuestatus: Optional[str]
    current_manu_phase: Optional[str]
    current_manu_phase_start: Optional[date]
    current_manu_phase_end: Optional[date]
    remediation_type: Optional[str]
    issue_component_id: Optional[str]
    middleware_instance_name: Optional[str]

# 先继承EimIdBase，再继承EimBase
class Eim(EimIdBase, EimBase, table=True):
    spv_component_id: Optional[int] = Field(default=None, index=True)

修改后生成的建表SQL中，id列会成为第一列：

CREATE TABLE stage_eim (
        id SERIAL NOT NULL,
        fimno VARCHAR,
        spv_component_id INTEGER,
        eim_technology_name VARCHAR,
        eim_technology_version VARCHAR,
        obsolete_start_date DATE,
        actual_remediation_date DATE,
        intended_remediation_date DATE,
        issuestatus VARCHAR,
        current_manu_phase VARCHAR,
        current_manu_phase_start DATE,
        current_manu_phase_end DATE,
        remediation_type VARCHAR,
        issue_component_id VARCHAR,
        middleware_instance_name VARCHAR,
        PRIMARY KEY (id)
)

原理说明 #

SQLModel会按照继承顺序排列字段：先处理第一个父类的字段，再依次处理后续父类的字段，最后处理子类自身定义的字段。通过让Eim先继承包含id的基类，就能让主键列排在所有业务字段之前，同时子类中重写的spv_component_id会覆盖父类的定义，保留索引配置且不会引发报错。

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