CODEWITHSUNDEEP
pythonpostgresqlpsycopg2postgresql-9.0
JoeZuntz
JoeZuntz

Reputation: 1179

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

Upvotes: 3

Views: 3669

Answers (2)

fog
fog

Reputation: 3391

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.

Upvotes: 3

Alex Martelli
Alex Martelli

Reputation: 882023

I think this requires a mix of string formatting and placeholders (because you need one %s per item in vals):

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

query = 'select distinct(my_col) from my_table where my_col in ('
query += ', '.join(['%s'] * len(vals))
query += ')'

cursor.execute(query, vals)
theset = {t[0] for t in cursor.fetchall()}

theboollist = [v in theset for v in vals]

This approach should guarantee that the amount of data you're sending to the DB (for the where ... in clause) and the amount you're getting back from it are both O(N) where N equals len(vals); I think it would be logically impossible to do better than that in big-O terms.

Upvotes: 1

Related Questions