python postgresql psycopg2 postgresql-9.0

Checking existence of list of python values in psycopg2/postgresql

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.

For example, suppose I have this python list:

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

and also that the query:

select my_col from my_table;

gives:

[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?

I am restricted to postgresql 9.0

Solution

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;

to get the result in "vals order".

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

or:

curs.execute("""SELECT A.x = ANY(SELECT my_col FROM my_table) 
                  FROM (SELECT * FROM unnest(%s) x) A""", (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.