比较Python列表和表行

Question

我有一个Python列表，看起来像这样:

[{'id': 5, 'field1': True}, {'id': 6, 'field1': False}]

它只是一个包含键/值对的字典列表。我想在Postgres数据库中查找与id匹配且在field1上不同的表行。

假设我的表看起来像:

id   field1
-----------
5    True
6    True

结果中应该只有第二行:它在id = 6上匹配，但在field1 = true上不匹配

是否有任何方法来实现这与SQL或我需要通过它手动循环?我的用例涉及很多这个过程会重复很多次，所以我试图找到最有效的方法。

Answer 1

在PostgreSQL中有几种方法可以做到这一点，使用WHERE IN子句:

SELECT *
FROM test
WHERE id IN (5, 6) AND (id, field1) NOT IN ((5, True), (6, False))

或JOIN到values表:

SELECT test.*
FROM test
JOIN (VALUES (5, True), (6, False)) AS v(id, field1)
ON test.id = v.id AND test.field1 != v.field1

在这两种情况下，示例数据的结果都是

id  field1
6   t

对于大量的数据，我希望JOIN查询是最有效的。您可以使用以下命令构建查询的VALUES部分:

ll = [{'id': 5, 'field1': True}, {'id': 6, 'field1': False}]
','.join(str(tuple(d.values())) for d in ll)

对于您的示例数据给出:

(5, True),(6, False)

Answer 2

您的数据看起来像一个JSON数组，您可以这样传递:

SELECT t.*
FROM   jsonb_array_elements('[{"id": 5, "field1": true}, {"id": 6, "field1": false}]'::jsonb) j(obj)
JOIN   tbl t ON t.id     =  (obj->>'id')::int
AND t.field1 <> (obj->>'field1')::bool

与预处理语句相同(Python对此有自己的特性):

PREPARE qr1(json) AS 
SELECT t.*
FROM   json_array_elements($1) j(obj)
JOIN   tbl t ON t.id     =  (obj->>'id')::int
AND t.field1 <> (obj->>'field1')::bool;

:

EXECUTE qr1('[{"id": 5, "field1": true}, {"id": 6, "field1": false}]');

db<此处小提琴>

确保在tbl (id, field1)上有索引。