检查Python值列表在psycopg2/postgresql中是否存在

Question

我有一个 Python 值列表和一个包含某个列的 PostgreSQL 表。我想知道对于我 Python 列表中的每个元素，是否存在该 ID 的任何行在表中。

例如，假设我有以下 Python 列表：

vals = [4, 8, 15, 16, 23, 42]

并且查询语句为：

select my_col from my_table;

给予：

[4, 5, 6, 7, 8]

然后我想要一个返回以下内容的查询：

[True, True, False, False, False, False]

我可以循环遍历该列表，并为每个值执行一个新的 "select exists"，但我想知道是否有一种方法可以在单个调用中完成？

我仅限于postgresql 9.0

- JoeZuntz

---

为什么不在select之后使用2行Python呢？一行将结果转换为集合s，然后只需使用[x in s for x in vals]即可。 - Alex Martelli

那么将整个数据库列拉入Python中？这样做是可行的。在某些时候，它将有数亿条目，因此不确定对性能是否会有一些负面影响？（该列将被索引。） - JoeZuntz

那个例子只是为了说明 - 我的代码中并没有真正选择整列。 - JoeZuntz

Answer 1

这个问题更多地关于 SQL，而不是 Python 或 psycopg。我会使用这样的查询：

SELECT my_col = ANY(your_array_here) FROM my_table;

以"表格顺序"获取结果或者：

SELECT A.x = ANY(SELECT my_col FROM my_table) 
  FROM (SELECT * FROM unnest(your_array_here) x) A;

幸运的是，psycopg提供了一个默认适配器，可以将Python列表转换为PostgreSQL数组，代码非常简单：

要以“值顺序”获取结果。

curs.execute("SELECT my_col = ANY(%s) from my_table", (vals,))

或者：

curs.execute("""SELECT A.x = ANY(SELECT my_col FROM my_table) 
                  FROM (SELECT * FROM unnest(%s) x) A""", (vals,))

请注意，绑定变量参数应该是一个字典或元组，并且您想将完整列表绑定到查询中的单个变量，这意味着您应该使用一个 1 元素元组 ((vals,))，而不是尝试直接传递 vals。

- fog

---

这太完美了 - 非常感谢。你说得对，这更像是一个 SQL 问题！ - JoeZuntz

Answer 2

我认为这需要字符串格式化和占位符的混合使用（因为您需要在“vals”中每个项目都有一个%s）。

vals = [4, 8, 15, 16, 23, 42]

query = 'select distinct(my_col) from my_table where my_col in ('
query += ', '.join(['%s'] * len(vals))
query += ')'

cursor.execute(query, vals)
theset = {t[0] for t in cursor.fetchall()}

theboollist = [v in theset for v in vals]

这种方法应该能够保证你发送到数据库（用于“where ... in”子句）的数据量和从数据库获取的数据量都是O(N)，其中N等于len(vals)；我认为在大O术语中更好的做法是逻辑上不可能的。

- Alex Martelli

---

使用 select distinct my_col 能否保证 len(cursor.fetchall()) <= N？ - unutbu

@unutbu 确定，如果my_col中可能存在重复项--没有考虑到这种情况，现在正在编辑以修复，谢谢。 - Alex Martelli