检查 psycopg2/postgresql 中 python 值列表的存在 [英] Checking existence of list of python values in psycopg2/postgresql

问题描述

我有一个 Python 值列表和一个包含特定列的 postgresql 表.我想知道我的 python 列表中的每个元素在表中是否有具有该 ID 的行.

I have a python list of values and a postgresql table with a certain column in it. I'd like to know for each element in my python list whether there is any row in the table with that ID.

例如，假设我有这个 python 列表:

For example, suppose I have this python list:

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

还有那个查询:

select my_col from my_table;

给出:

[4, 5, 6, 7, 8]

然后我想要一个返回的查询:

Then I'd like a query that returns:

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

我可以遍历列表并为每个值执行一个新的选择存在"，但我想知道是否有办法在一次调用中完成它?

I could loop through the list and execute a new "select exists" for each value, but I wondered if there was a way to do it in a single call?

我只能使用 postgresql 9.0

I am restricted to postgresql 9.0

推荐答案

这个问题更多地是关于 SQL 而不是 aboyt Python 或 psycopg.我会使用如下查询:

This question is more about SQL than aboyt Python or psycopg. I'd use a query like:

SELECT my_col = ANY(your_array_here) FROM my_table;

以表格顺序"获得结果或:

to get result in "table order" or:

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

以vals order"获得结果.

to get the result in "vals order".

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

Fortunately enough psycopg provides a default adapter that converts Python lists to PostgreSQL arrays and the code is extremely simple:

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,))

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

Note that the bound variable argument should be a dict or a tuple and you want to bind the full list to a single variable in the query, meaning that you should use a 1-element tuple ((vals,)) instead of trying to pass vals directly.

这篇关于检查 psycopg2/postgresql 中 python 值列表的存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！