如何在NOT IN语句中使用Postgresql ANY运算符 [英] How do I use the Postgresql ANY operator in a NOT IN statement

问题描述

使用Pyscopg2，如何使用Python将列表传递到SQL语句 ANY运算符？

Using Pyscopg2, how do I pass a Python list into an SQL statement using the ANY Operator?

正常工作的SQL读取（请参阅SQL Fiddle ）：

Normal Working SQL reads (See SQL Fiddle):

SELECT * FROM student WHERE id NOT IN (3);

使用Psycopg2如下：

Using Psycopg2 as below:

Psycopg2：查询1

下面的查询失败，并出现 psycopg2.ProgrammingError：语法错误，位于 ANY附近或附近

The query below fails with psycopg2.ProgrammingError: syntax error at or near "ANY"

id_list = [2,3,4]
cursor.execute("SELECT * FROM student WHERE id NOT IN ANY(%s)) %(id_list); 

Psycopg2：查询2

下面的查询不会引发错误，但是会给出错误的结果，因为它没有排除列表中的ID。好像它是一个等于运算符，或者像它的 IN 语句一样具体，而我想要一个 NOT IN

The query below doesn't throw an error but it gives a wrong result because it doesn't exclude the IDs in the list. It behaves as if its an Equal To operator or to be specific like its an IN statement while I want a NOT IN

id_list = [2,3,4]
cursor.execute("SELECT * FROM student WHERE id != ANY(%s)), (id_list,); 

此外，在我的搜索中，我遇到了 pyscopg2扩展SQL_IN 。可以在这种情况下使用吗？如果是这样，我该如何使用它？

Also, in my search I've come across pyscopg2 extension SQL_IN. Can it be used in this situation? If so, how do I use it?

推荐答案

当您这样做

select 2 != any(array[2,3,4]);
 ?column? 
----------
 t

2 将与所有数组项进行比较，如果有任何 2 不等于的项目，则其值将为 true 。

2 will be compared to all array items and if there is any to which 2 is not equal it will evaluate to true.

使用 not id = any（array [2,3,4]）

select not 1 = any(array[2,3,4]);
 ?column? 
----------
 t

select not 2 = any(array[2,3,4]);
 ?column? 
----------
 f

或！= all

select 1 != all(array[2,3,4]);
 ?column? 
----------
 t

select 2 != all(array[2,3,4]);
 ?column? 
----------
 f

这篇关于如何在NOT IN语句中使用Postgresql ANY运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！