fastapi将字符串数组和字典序列化为JSON字符串存储到数据库

近期的fastapi项目中，在设计数据结构时发现有将字符串数组和字典对象固化到数据库的需求。fastapi的数据结构以Pydantic为核心，所以解决方案必然要去Pydantic的序列化函数文档找。下面直接给出示例代码：

#!/usr/bin/python3
# coding: utf-8
# model.py

from datetime import datetime
from sqlalchemy import Column, String
from sqlalchemy.ext.declarative import declarative_base, declared_attr
import re

class CustomBase:
    """https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html"""
    # @declared_attr
    # def __tablename__(cls):
    #     return cls.__name__.lower()
    # 驼峰命名转为小写下划线命名: https://blog.csdn.net/mouday/article/details/90079956
    @declared_attr.directive
    def __tablename__(cls) -> str:
        snake_case = re.sub(r"(?P<key>[A-Z])", r"_\g<key>", cls.__name__)
        return snake_case.lower().strip('_')
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_collate': 'utf8mb4_general_ci'
    }

BaseModel = declarative_base(cls=CustomBase)

class CommonMixin:
    is_deleted: Mapped[Optional[int]] = \
        mapped_column(SmallInteger, server_default=text('0'), comment="是否删除: 0 未删除 1 已删除")
    create_time: Mapped[Optional[datetime]] = \
        mapped_column(insert_default=func.now(), comment="创建时间")
    update_time: Mapped[Optional[datetime]] = \
        mapped_column(server_default=func.now(), onupdate=func.now(), comment="更新时间")

class Waypoint(BaseModel, CommonMixin):
    id: Mapped[str] = mapped_column(String(256), primary_key=True, comment="主键ID")
    map_id: Mapped[str] = mapped_column(String(256) , comment="点位所属地图")
    key: Mapped[str] = mapped_column(String(256),unique=True, comment="点位标识")
    name: Mapped[str] = mapped_column(String(256),unique=True, comment="点位名称")
    point_type: Mapped[Optional[int]] = \
        mapped_column(Integer, server_default=text('4'), comment="点位类型 0:普通点 1:休息点 2:充电点 3:数据采集点点 4:任务终点")
    adjacent_points: Mapped[Optional[str]] = mapped_column(String(1024), comment="拓扑地图邻接点位,用Json String保存相应List")
    robot_payload: Mapped[Optional[str]] = mapped_column(String(1024), comment="机器人点位运行负载,用Json String保存动作参数")

上述model.py代码除了构建常规的sqlachemy BaseModel，主要还定义了Waypoint这个ORM数据结构，adjacent_points和robot_payload这两个字段将在schema.py里映射为List[str]和dict. 然后是schema.py代码：

#!/usr/bin/python3
# coding: utf-8
# schema.py

from pydantic import BaseModel, Tag, Field, ConfigDict, field_serializer, field_validator
from typing import List, Optional
from typing_extensions import Annotated
from datetime import datetime
from pydantic.alias_generators import to_snake
import json

class InDBMixin(BaseModel):
    class Config:
        orm_mode = True  # 通过Pydantic将ORM对象转化为Pydantic对象

class BaseWaypoint(BaseModel):
    id: str = Field(description='点位ID')
    mapId: str = Field(description='点位地图ID')
    key: str = Field(description='点位标识')
    name: str = Field(description='点位名称')
    pointType: int = Field(description='点位类型 0:普通点 1:休息点 2:充电点 3:数据采集点点 4:任务终点')
    adjacentPoints: Optional[List[str]] = Field(description='拓扑地图邻接点位')
    robotPayload: Optional[dict] = Field(description='机器人点位运行负载,用Json String保存动作参数',default=None)

    @field_serializer('adjacentPoints', when_used='always')
    def serialize_adjacent_points_to_json(adjacentPoints: List[str]):
        return json.dumps(adjacentPoints)
    
    @field_validator('adjacentPoints', mode="before")
    def parse_adjacent_points(cls, value):
        if isinstance(value,str):
            return json.loads(value)
        return value
    
    @field_serializer('robotPayload', when_used='always')
    def serialize_robot_payload_to_json(adjacentPoints: List[str]):
        return json.dumps(adjacentPoints)
    
    @field_validator('robotPayload', mode="before")
    def parse_robot_payload(cls, value):
        if isinstance(value,str):
            return json.loads(value)
        return value

    model_config = ConfigDict(
        alias_generator=to_snake,
        populate_by_name=True
    )

class WaypointSchema(BaseWaypoint, InDBMixin):
    createTime: datetime
    updateTime: datetime

class CreateWaypointSchema(BaseWaypoint):
    pass

class UpdateWaypointSchema(BaseWaypoint):
    pass

可以看到上述schema.py的代码adjacentPoints和robotPayload已经不再是字符串。细心的读者会发现代码风格变了，是因为通过Pydantic的alias_generator和populate_by_name配置，自动生成了snake_case的alias，这在跨语言跨风格的接口对接下比较有用。

上述代码中，通过field_serializer注解实现了序列化为JSON字符串的需求。需要注意点是，配置了该注解后在任何类似model_dump的场景，模型导出的都将是JSON字符串。反方向地，在从数据库中的JSON字符串构建WaypointSchema的过程中，字符串将被赋值给WaypointSchema的构造函数。通过field_validator注解最终实现了将JSON字符串转换为对应数据结构的过程。这个注解值得注意的是：与序列化只能输出字符串不同，对象的构造过程可以接收不同类型的数据，比如通过API去构造时就可以直接接收对象而不用先转成字符串。