SQLAdmin处理PostgreSQL ARRAY字段报NoConverterFound错误

在FastAPI应用中搭配使用SQLAdmin Python包与SQLModel库，为包含PostgreSQL数组字段的模型开发管理后台时，尝试编辑对应实体会抛出如下异常：

ERROR:    Exception in ASGI application
2022-06-03T19:03:11.662449+00:00 app[web.1]: Traceback (most recent call last):
2022-06-03T19:03:11.662449+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/uvicorn/protocols/http/httptools_impl.py", line 372, in run_asgi
2022-06-03T19:03:11.662450+00:00 app[web.1]: result = await app(self.scope, self.receive, self.send)
2022-06-03T19:03:11.662450+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
2022-06-03T19:03:11.662451+00:00 app[web.1]: return await self.app(scope, receive, send)
2022-06-03T19:03:11.662457+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/fastapi/applications.py", line 261, in __call__
2022-06-03T19:03:11.662457+00:00 app[web.1]: await super().__call__(scope, receive, send)
2022-06-03T19:03:11.662458+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/applications.py", line 112, in __call__
2022-06-03T19:03:11.662458+00:00 app[web.1]: await self.middleware_stack(scope, receive, send)
2022-06-03T19:03:11.662458+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
2022-06-03T19:03:11.662459+00:00 app[web.1]: raise exc
2022-06-03T19:03:11.662459+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
2022-06-03T19:03:11.662460+00:00 app[web.1]: await self.app(scope, receive, _send)
2022-06-03T19:03:11.662460+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/middleware/cors.py", line 84, in __call__
2022-06-03T19:03:11.662461+00:00 app[web.1]: await self.app(scope, receive, send)
2022-06-03T19:03:11.662461+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
2022-06-03T19:03:11.662462+00:00 app[web.1]: raise exc
2022-06-03T19:03:11.662462+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
2022-06-03T19:03:11.662462+00:00 app[web.1]: await self.app(scope, receive, sender)
2022-06-03T19:03:11.662463+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
2022-06-03T19:03:11.662463+00:00 app[web.1]: raise e
2022-06-03T19:03:11.662463+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
2022-06-03T19:03:11.662463+00:00 app[web.1]: await self.app(scope, receive, send)
2022-06-03T19:03:11.662464+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/routing.py", line 656, in __call__
2022-06-03T19:03:11.662464+00:00 app[web.1]: await route.handle(scope, receive, send)
2022-06-03T19:03:11.662464+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/routing.py", line 408, in handle
2022-06-03T19:03:11.662465+00:00 app[web.1]: await self.app(scope, receive, send)
2022-06-03T19:03:11.662465+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/applications.py", line 112, in __call__
2022-06-03T19:03:11.662465+00:00 app[web.1]: await self.middleware_stack(scope, receive, send)
2022-06-03T19:03:11.662465+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
2022-06-03T19:03:11.662466+00:00 app[web.1]: raise exc
2022-06-03T19:03:11.662466+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
2022-06-03T19:03:11.662466+00:00 app[web.1]: await self.app(scope, receive, _send)
2022-06-03T19:03:11.662466+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
2022-06-03T19:03:11.662466+00:00 app[web.1]: raise exc
2022-06-03T19:03:11.662467+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
2022-06-03T19:03:11.662467+00:00 app[web.1]: await self.app(scope, receive, sender)
2022-06-03T19:03:11.662467+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/routing.py", line 656, in __call__
2022-06-03T19:03:11.662467+00:00 app[web.1]: await route.handle(scope, receive, send)
2022-06-03T19:03:11.662468+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/routing.py", line 259, in handle
2022-06-03T19:03:11.662468+00:00 app[web.1]: await self.app(scope, receive, send)
2022-06-03T19:03:11.662468+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/starlette/routing.py", line 61, in app
2022-06-03T19:03:11.662469+00:00 app[web.1]: response = await func(request)
2022-06-03T19:03:11.662469+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/sqladmin/application.py", line 351, in edit
2022-06-03T19:03:11.662471+00:00 app[web.1]: Form = await model_admin.scaffold_form()
2022-06-03T19:03:11.662471+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/sqladmin/models.py", line 834, in scaffold_form
2022-06-03T19:03:11.662471+00:00 app[web.1]: return await get_model_form(
2022-06-03T19:03:11.662472+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/sqladmin/forms.py", line 415, in get_model_form
2022-06-03T19:03:11.662472+00:00 app[web.1]: field = await converter.convert(
2022-06-03T19:03:11.662472+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/sqladmin/forms.py", line 232, in convert
2022-06-03T19:03:11.662472+00:00 app[web.1]: converter = self.get_converter(model=model, prop=prop)
2022-06-03T19:03:11.662473+00:00 app[web.1]: File "/app/.heroku/python/lib/python3.8/site-packages/sqladmin/forms.py", line 116, in get_converter
2022-06-03T19:03:11.662473+00:00 app[web.1]: raise NoConverterFound(  # pragma: nocover
2022-06-03T19:03:11.662474+00:00 app[web.1]: sqladmin.exceptions.NoConverterFound: Could not find field converter for column series (<class 'sqlalchemy.sql.sqltypes.ARRAY'>).

触发错误的模型定义如下：

class ShowCreate(SQLModel):
    title: str
    description: str
    language: Language = Field(sa_column=Column(Enum(Language)))
    show_copyright: str
    category: Category = Field(sa_column=Column(Enum(Category)))
    series: Set[str] = Field(default=None, sa_column=Column(ARRAY(String())))

报错核心原因是series字段使用了PostgreSQL专属的ARRAY类型，SQLAdmin默认没有内置该类型的表单转换器，生成编辑页表单时无法识别字段类型，直接抛出异常。

通过自定义SQLAdmin的模型转换器，实现ARRAY类型字段的表单渲染和双向数据转换即可解决问题，步骤如下：

1. 编写适配字符串数组的表单字段，完成数据库值和表单输入值的双向转换：

   import typing
   from wtforms import TextAreaField, widgets
   from sqlalchemy.sql.sqltypes import ARRAY
   from sqladmin.forms import ModelConverter, converts
   
   class StringArrayField(TextAreaField):
       """适配ARRAY(String)类型的表单字段，每行输入一个字符串元素"""
       widget = widgets.TextArea()
   
       def _value(self) -> str:
           # 数据库存储的数组转表单显示值：用换行符拼接所有元素
           if self.data:
               return "\n".join(self.data)
           return ""
   
       def process_formdata(self, valuelist: typing.List[str]) -> None:
           # 表单提交值转数据库存储值：按换行分割文本，过滤空值和首尾空格，转成集合适配Set[str]类型
           if valuelist:
               raw_input = valuelist[0]
               self.data = {item.strip() for item in raw_input.splitlines() if item.strip()}
           else:
               self.data = set()
   

2. 编写自定义模型转换器，注册ARRAY类型的转换规则：

   class CustomModelConverter(ModelConverter):
       @converts(ARRAY)
       async def parse_array_type(self, model: type, prop: any, field_kwargs: dict) -> tuple:
           # 此处默认处理字符串数组，若需要支持整数、枚举等其他数组类型，可在此处添加类型判断逻辑
           return StringArrayField, field_kwargs
   

3. 初始化SQLAdmin实例时，传入自定义的转换器替换默认实现：

   from sqladmin import Admin
   # 导入项目中已有的FastAPI实例和数据库引擎
   from your_project.main import app
   from your_project.database import engine
   
   admin = Admin(
       app=app,
       engine=engine,
       converter=CustomModelConverter()
   )
   

补充说明 #

• 上述实现默认使用多行文本框作为数组字段的输入形式，每行对应一个数组元素，提交时自动去重、过滤空值，和模型中定义的Set[str]类型完全适配。
• 如果需要更友好的交互（比如标签输入、多选下拉），可以自行替换StringArrayField的前端渲染逻辑，只要保证_value和process_formdata两个方法的双向转换逻辑正确即可。
• 如果项目中存在其他类型的数组字段（比如整数数组），只需要在parse_array_type方法中判断ARRAY内部存储的元素类型，返回对应处理逻辑的表单字段即可。

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