Pydantic与Sqlalchemy数据模型的枚举类型实践

说明：

• 乍看时以为很容易的东西，摸索实践后才知道窍门
• 实战中我是用Tinyint / Interger 作为保存 enum对象数值的字段类型， 而不是用数据库中提供的ENUM类型字段，因为有更好的扩展性，性能，节省空间作为使用理由
• Pydantic Model (PM) 与 Sqlalchemy Model (SM) 的关系是数据模型的外在与内部持久化的两种表现形式， PM负责数据模型的输入输出（规则验证 / 过滤 / 表达转换），SM负责数据模型的具体存储表达

开始时遇到的问题:

• 因为没有打开use_enum_values = True, 致使保存时总出错： “sqlalchemy.exc.DataError: (asyncmy.errors.DataError) (1366, "Incorrect integer value: 'DeviceType.HOST' for column 'type' at row 1")”
• 怀疑人生后，用官方的例子用数据库的ENUM类型做保存，成功后觉得不符合要求，弃之

  from sqlalchemy import Enum
  ...
  type = mapped_column(Enum(DeviceType), nullable=False, default=DeviceType.HOST, comment='类型: 1 IOT传感器 Sensor 2 交互设备 Host 3 软件应用 App')

修改好的实例：

有一个设备表，里面有一个type列(Tinyint)，用于保存设备类型，结构如下：

（Sqlalchemy Model）

from typing import List, Optional

from sqlalchemy import BigInteger, Column, DateTime, ForeignKeyConstraint, Index, DECIMAL, Integer, String, Table, Text, text
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.orm import Mapped, declarative_base, mapped_column, relationship
from sqlalchemy.orm.base import Mapped

from sqlalchemy.orm import relationship, Mapped, mapped_column
from db.db_base import BaseModel
from .data_types import DeviceType

class Device(BaseModel):
    __tablename__ = 'ia_iot_device'
    __table_args__ = (
        Index('Index_1', 'label'),
        Index('Index_2', 'name'),
        Index('Index_3', 'code')
    )

    id = mapped_column(BigInteger, primary_key=True)
    type = mapped_column(TINYINT, default=DeviceType.HOST, comment='类型: 1 IOT传感器 Sensor 2 交互设备 Host 3 软件应用 App')
    code = mapped_column(String(255, 'utf8mb4_general_ci'), comment='编码,唯一')
    name = mapped_column(String(255, 'utf8mb4_general_ci'), comment='设备名, 如 dev@ip:port')
    label = mapped_column(String(255, 'utf8mb4_general_ci'), comment='设备标签,客户自定义')
    product_id = mapped_column(BigInteger, server_default=text("'0'"), comment='产品id')
    secret = mapped_column(String(255, 'utf8mb4_general_ci'), comment='访问密钥,暂不启用')
    mac = mapped_column(String(255, 'utf8mb4_general_ci'), comment='物理地址')
    geo_longitude = mapped_column(String(255, 'utf8mb4_general_ci'), comment='经度')
    geo_latitude = mapped_column(String(255, 'utf8mb4_general_ci'), comment='纬度')
    height = mapped_column(DECIMAL(10, 2), server_default=text("'0.00'"), comment='高度')
    deployment_id = mapped_column(BigInteger, server_default=text("'0'"), comment='部署地方id, 暂没启用')
    register_ip = mapped_column(String(255, 'utf8mb4_general_ci'), comment='注册IP')
    env = mapped_column(Text(collation='utf8mb4_general_ci'), comment='环境参数, json格式')
    memo = mapped_column(Text(collation='utf8mb4_general_ci'), comment='备注')
    is_bound = mapped_column(TINYINT, server_default=text("'0'"), comment='是否已绑定')
    state = mapped_column(String(255, 'utf8mb4_general_ci'), comment='实时状态编码')
    status = mapped_column(TINYINT, server_default=text("'1'"), comment='实体记录状态 1活动 2停用 3伪删除')
    position = mapped_column(Integer, server_default=text("'0'"), comment='排序优先极, 数字高优先')
    # created_at = mapped_column(DateTime)
    # updated_at = mapped_column(DateTime)

data_types.py

import enum

class DeviceType(enum.Enum):
    '''
    设备类型
    '''
    SENSOR = 1
    HOST = 2
    APP = 3

(Pydantic Model) apps\vadmin\iot\schemas\Device.py

要点：告诉Pydantic使用枚举值

class Config:  

        use_enum_values = True

from pydantic import BaseModel, Field, ConfigDict
from core.data_types import DatetimeStr
from apps.vadmin.iot.models.data_types import DeviceType


class Device(BaseModel):
    type: DeviceType | None = Field(None, title="类型: 1 IOT传感器 Sensor 2 交互设备 Host 3 软件应用 App")
    code: str | None = Field(None, title="编码,唯一")
    name: str | None = Field(None, title="设备名, 如 dev@ip:port")
    label: str | None = Field(None, title="设备标签,客户自定义")
    product_id: int | None = Field(None, title="产品id")
    secret: str | None = Field(None, title="访问密钥,暂不启用")
    mac: str | None = Field(None, title="物理地址")
    geo_longitude: str | None = Field(None, title="经度")
    geo_latitude: str | None = Field(None, title="纬度")
    height: float | None = Field(None, title="高度")
    deployment_id: int | None = Field(None, title="部署地方id, 暂没启用")
    register_ip: str | None = Field(None, title="注册IP")
    env: str | None = Field(None, title="环境参数, json格式")
    memo: str | None = Field(None, title="备注")
    is_bound: int | None = Field(None, title="是否已绑定")
    state: str | None = Field(None, title="实时状态编码")
    status: int | None = Field(None, title="实体记录状态 1活动 2停用 3伪删除")
    position: int | None = Field(None, title="排序优先极, 数字高优先")
    
    class Config:  
        use_enum_values = True

参考：

- python - Pydantic enum field does not get converted to string - Stack Overflow

- The Type Hierarchy — SQLAlchemy 2.0 Documentation