How do I use the Postgresql ANY operator in a NOT IN statement

2024/9/29 1:26:17

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

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? 
----------tselect not 2 = any(array[2,3,4]);?column? 
----------f

Or != all

select 1 != all(array[2,3,4]);?column? 
----------tselect 2 != all(array[2,3,4]);?column? 
----------f
https://en.xdnf.cn/q/71267.html

Related Q&A

pass command-line arguments to runpy

I have two files, one which has a side effect I care about that occurs within the if __name__ == "__main__" guard:# a.py d = {} if __name__ == "__main__":d[arg] = helloThe second fi…

Altering my python path: helloworld.py returns command not found—

Massive apologies for this embarrassing question—Im using my MacBook Pro, running snow leopard, and using Python 2.7.1. Trying to run my first script and all the first pages of all my tutorials are la…

ImportError: cannot import name force_text

I have installed Python 2.7 and Django 1.4 in my CentOS machine and installed all dependencies for my existing project. When I run python manage.py runserver, I am getting the following traceback in my…

How can I select the pixels that fall within a contour in an image represented by a numpy array?

VI have a set of contour points drawn on an image which is stored as a 2D numpy array. The contours are represented by 2 numpy arrays of float values for x and y coordinates each. These coordinates are…

Get the value of specific JSON element in Python

Im new to Python and JSON, so Im sorry if I sound clueless. Im getting the following result from the Google Translate API and want to parse out the value of "translatedText":{"data"…

How does setuptools decide which files to keep for sdist/bdist?

Im working on a Python package that uses namespace_packages and find_packages() like so in setup.py:from setuptools import setup, find_packages setup(name="package",version="1.3.3.7"…

How to implement multivariate linear stochastic gradient descent algorithm in tensorflow?

I started with simple implementation of single variable linear gradient descent but dont know to extend it to multivariate stochastic gradient descent algorithm ?Single variable linear regression impo…

How to do os.execv() in Python in Windows without detaching from the console?

Im using Python 2.6 on Windows 7. I have Windows .cmd file which invokes Python to run the CherryPy Web server (version 3.1.2). I start this .cmd file by executing it at the prompt in a Windows CMD she…

Django queryset permissions

I am building a quite complex Django application to be used on top of and email scanning service. The Django application is written using Python 3.5+This application primarily uses Django Rest Framewor…

How to extract certain parts of a web page in Python

Target web page: http://www.immi.gov.au/skilled/general-skilled-migration/estimated-allocation-times.htmThe section I want to extract:<tr><td>Skilled &ndash; Independent (Residence) sub…