How do I use the Postgresql ANY operator in a NOT

Question

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

Normal Working SQL reads (See SQL Fiddle):

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

Using Psycopg2 as below:

Psycopg2: Query 1

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: Query 2

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

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?

Answer 1

When you do

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

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

Use 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

Or != all

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

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

Answer 2

The SQL_IN adapter makes it possible to use the IN operator by adapting a tuple, not a list (lists are adapted to PostgreSQL arrays). To make your query work you should write the code as follows:

id_list = (2,3,4) # Note the tuple!
cursor.execute("SELECT * FROM student WHERE id NOT IN %s", (id_list,));