python - 使用 UTF-8 的 JSON 字段中的 SQLAlchemy 文本匹配数据

Question

我有一个名为 Message 的表，其中包含 JSON 类型的列内容

我的模型定义如下

class Message(db.Model):
    ...
    content = db.Column(JSON)
    ...

现在我使用以下查询执行文本匹配以进行简单搜索

Message.query.filter(Message.content['summary'].cast(Unicode).match(term))

它工作得很好，直到 term 有一个 utf-8 字符，比如德语变音符号或法语口音。

这里的解决方案是什么？

还要记住我使用的是 Python 3

Answer 1

问题在于 Postgresql json 列的 cast(Unicode)。如果是 Postgresql VARCHAR，它只是将 json 转换为 SQLAlchemy 的 Unicode 下的文本类型。换句话说，它生成 JSON 的字符串表示形式，而不是提取文本内容。如果您的输入包含转义的 unicode 代码点，它们将按本例输出。给定一个带有 json 列数据的简单测试模型:

In [7]: t = Test(data={'summary': 'Tämä on summary.'})

In [8]: session.add(t)

In [9]: session.commit()

In [11]: session.query(Test.data['summary'].cast(Unicode)).scalar()
Out[11]: '"T\\u00e4m\\u00e4 on summary."'

很明显为什么与未转义的 unicode 字符匹配会失败。提取文本内容、取消转义转义 unicode 的正确方法是使用 astext ，它使用 ->> operator在 PostgreSQL 中:

In [13]: session.query(Test.data['summary'].astext).scalar()
Out[13]: 'Tämä on summary.'

引用 JSON 函数和运算 rune 档:

Note: Many of these functions and operators will convert Unicode escapes in JSON strings to the appropriate single character. This is a non-issue if the input is type jsonb, because the conversion was already done; but for json input, this may result in throwing an error, as noted in Section 8.14.

所以在你的情况下:

Message.query.\
    filter(Message.content['summary'].astext.match(term))

请注意，这仅适用于 json 类型，不适用于 jsonb，因为 json 类型不会在输入时转换 unicode 转义。 jsonb 另一方面converts all unicode escapes to equivalent ASCII or UTF-8 characters for storage .如果我们的 Test 模型包含第二列 data2 jsonb，具有完全相同的输入，那么结果将是:

In [11]: session.query(Test.data['summary'].cast(Unicode),
    ...:               Test.data2['summary'].cast(Unicode)).first()
Out[11]: ('"T\\u00e4m\\u00e4 on summary."', '"Tämä on summary"')

不过，如果您需要文本而不是 JSON 的字符串表示，您应该使用 astext。