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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With