如何在SQLAlchemy中用mapped_column声明枚举以支持类型提示？

问题场景 #

尝试在SQLAlchemy 2.0中结合mapped_column使用PostgreSQL枚举时，编写了如下代码：

from sqlalchemy.dialects.postgresql import ENUM as pgEnum
import enum
from sqlalchemy.orm import Mapped, mapped_column, Base
import uuid
from uuid import UUID
import datetime as dt

class CampaignStatus(str, enum.Enum):
    activated = "activated"
    deactivated = "deactivated"

CampaignStatusType: pgEnum = pgEnum(
    CampaignStatus,
    name="campaignstatus",
    create_constraint=True,
    metadata=Base.metadata,
    validate_strings=True,
)

class Campaign(Base):
    __tablename__ = "campaign"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid.uuid4)
    created_at: Mapped[dt.datetime] = mapped_column(default=dt.datetime.now)
    status: Mapped[CampaignStatusType] = mapped_column(nullable=False)

运行时触发如下错误：

Traceback (most recent call last):
  File "<stdin>", line 27, in <module>
    class Campaign(Base):
...
AttributeError: 'ENUM' object has no attribute '__mro__'

注：旧版SQLAlchemy的枚举方案不适用，因为当前使用2.x版本并采用mapped_column/Mapped类型；移除CampaignStatus继承的str也无法解决问题。

解决方法 #

错误核心在于Mapped[]中应传入Python枚举类本身（CampaignStatus），而非你创建的pgEnum实例（CampaignStatusType）。pgEnum实例需要作为参数传递给mapped_column()，而非放在类型注解中。

修改后的代码如下：

from sqlalchemy.dialects.postgresql import ENUM as pgEnum
import enum
from sqlalchemy.orm import Mapped, mapped_column, Base
import uuid
from uuid import UUID
import datetime as dt

class CampaignStatus(str, enum.Enum):
    activated = "activated"
    deactivated = "deactivated"

# 定义PostgreSQL枚举类型
CampaignStatusType: pgEnum = pgEnum(
    CampaignStatus,
    name="campaignstatus",
    create_constraint=True,
    metadata=Base.metadata,
    validate_strings=True,
)

class Campaign(Base):
    __tablename__ = "campaign"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid.uuid4)
    created_at: Mapped[dt.datetime] = mapped_column(default=dt.datetime.now)
    # 类型注解用枚举类，mapped_column传入pgEnum实例
    status: Mapped[CampaignStatus] = mapped_column(CampaignStatusType, nullable=False)

额外说明 #

• SQLAlchemy 2.x的Mapped[]要求传入Python原生类型，而非SQLAlchemy的数据库类型对象（如pgEnum实例）
• 保留CampaignStatus继承str是合理设计，既保证枚举的类型安全性，又能让枚举值直接作为字符串使用

内容的提问来源于stack exchange，提问作者Javier Guzmán