时间戳

首页 > 解决方案 > SQLAlchemy has_any()

python - SQLAlchemy has_any()

问题描述

我无法让 SQLAlchemy 的has_any()功能正常工作。我只是想在 jsonb 列中包含的列表中过滤我的结果：

db.session.query(models.Record.id).\
    filter(models.Record.record_metadata["teams"].has_any(team_ids))

models.Record.record_metadata["teams"]record_metadata作为我想要与之比较的 jsonb 列 ( )team_ids中的 id 列表，这也是 id 列表

生成的查询是：

SELECT record.id AS record_id
FROM record
WHERE ((record.record_metadata -> 'teams')) ?| '["id1", "id2"]';

这给出了错误

DataError: (psycopg2.DataError) malformed array literal: "["id1", "id2"]"
LINE 3: WHERE ((record.record_metadata -> 'teams')) ?| '["id1", "id2"]'
                                                       ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.

但是关于表 9.44。Postgres 官方文档上的附加 jsonb 运算符|?，运算符后面应该跟一个array[]

事实上，当我手动编写查询时：

SELECT record.id AS record_id
FROM record
WHERE ((record.record_metadata -> 'teams')) ?| array['id1', 'id2'];

它工作得很好。

SQLAlchemy关于这个函数的文档很差，我找不到任何例子。

那么是我在这个功能上做错了吗？我应该在这种情况下使用其他东西吗？还是它has_any()坏了？

编辑 ： 

SELECT record.id AS record_id
FROM record
WHERE ((record.record_metadata -> 'teams')) ?| '{"id1", "id2"}';

工作得很好，我发现它也has_any()接受字符串。

所以它是一个（非常）讨厌的修复，但是如果我用所需的 id 格式化一个字符串：

db.session.query(models.Record.id).\
    filter(models.Record.record_metadata["teams"].has_any(
        str(team_ids).
        replace('[', '{').
        replace(']', '}').
        replace('\'', '\"')))

有用...

标签： pythonpostgresqlsqlalchemyjsonb

解决方案

似乎默认情况下传递的值是 JSON 编码的，如果它是 alist或 a dict。您可以通过传递显式array文字来解决此问题：

In [15]: from sqlalchemy.dialects.postgresql import array

In [16]: session.query(Foo).\
    ...:     filter(Foo.data['test'].has_any(array(['1', '2']))).all()
2018-06-18 09:07:20,780 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-06-18 09:07:20,780 INFO sqlalchemy.engine.base.Engine SELECT foo.id AS foo_id, foo.data AS foo_data 
FROM foo 
WHERE ((foo.data -> %(data_1)s)) ?| ARRAY[%(param_1)s, %(param_2)s]
2018-06-18 09:07:20,781 INFO sqlalchemy.engine.base.Engine {'data_1': 'test', 'param_1': '1', 'param_2': '2'}
Out[16]: []

这样您就不需要从 Python 的字符串表示中手动格式化数组文字list，这可能很容易出错。

错误本身是 Postgresql 试图将传递的文字（包含 JSON）解析为数组的结果。如错误所示，数组的文本表示可能以指定数组下标范围的维度装饰开始：

'[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[]

推荐阅读