FastAPI搜索接口报错asyncpg.AmbiguousParameterError求助

错误信息 #

asyncpg.exceptions.AmbiguousParameterError: could not determine data type of parameter $2

相关代码 #

查询语句（queries.py） #

GET_LOG_QUERY = """
SELECT *
FROM activities
WHERE entity = :entity
AND types_id = :types_id
AND (
    :search IS NULL OR :search = '' 
    OR full_name ILIKE :search
    OR email ILIKE :search
)
"""

仓库实现代码 #

class LogActivitiesRepository(BaseRepository):
    def __init__(self, db):
        super().__init__(db=db)

    async def get_activities(self, types_id: str, entity: str, search: Optional[str] = None) -> List[LogActivityModel]:
        search_pattern = f"%{search}%" if search else "%"
        records = await self.db.fetch_all(
            query=GET_LOG_QUERY,
            values={
                "entity": entity_name,
                "types_id": types_id,
                "search": search_pattern
            }
        )
        if not records:
            return []
        return [LogActivityModel(**record._mapping) for record in records]

无search字段时的正常查询语句 #

GET_LOG_QUERY = """
SELECT *
FROM user_activities
WHERE entity_name = :entity_name
AND tenant_id = :tenant_id;
"""

问题原因与解决办法 #

原因 #

当search参数为None时，代码会把search_pattern设为"%"，但PostgreSQL无法自动推断这个参数的数据类型——ILIKE操作需要字符串类型，但单纯的%没有明确的类型标识，导致asyncpg抛出类型不确定的错误。另外代码存在笔误：values字典里的entity对应值写了entity_name，但方法参数是entity，会引发未定义变量错误。

解决办法 #

方案一：显式指定参数类型

修改SQL查询语句，给:search参数加上::text，明确告诉PostgreSQL这是字符串类型：

GET_LOG_QUERY = """
SELECT *
FROM activities
WHERE entity = :entity
AND types_id = :types_id
AND (
    :search IS NULL OR :search = '' 
    OR full_name ILIKE :search::text
    OR email ILIKE :search::text
)
"""

方案二：优化参数处理逻辑

当search为None时直接传递None，同时简化查询条件，避免空字符串判断：

1. 修改Python代码：

async def get_activities(self, types_id: str, entity: str, search: Optional[str] = None) -> List[LogActivityModel]:
    search_pattern = f"%{search}%" if search else None
    records = await self.db.fetch_all(
        query=GET_LOG_QUERY,
        values={
            "entity": entity,  # 修正笔误
            "types_id": types_id,
            "search": search_pattern
        }
    )
    if not records:
        return []
    return [LogActivityModel(**record._mapping) for record in records]

2. 修改SQL查询语句：

GET_LOG_QUERY = """
SELECT *
FROM activities
WHERE entity = :entity
AND types_id = :types_id
AND (
    :search IS NULL
    OR full_name ILIKE :search
    OR email ILIKE :search
)
"""

这样当search为None时，搜索条件直接跳过，PostgreSQL能明确参数类型，同时避免无效的空字符串判断。

必做修正

一定要把代码中values字典里的"entity": entity_name改为"entity": entity，否则会出现entity_name未定义的错误。

内容的提问来源于stack exchange，提问作者Lutaaya Huzaifah Idris