Python sqlite3 INNER JOIN with a python list

Question

I am trying to select from a table where values of one of the columns is equal to values of a list. For example:

TABLE
------------
ID    price
a     100
b     200
...
z     2600

python list: ["a", "d" , "e"]. And i want to find the prices of each of those IDs. The obvious way of doing this is to do a JOIN on ID but that list is not a table. what should i do?

You do not need a join for this. See: http://stackoverflow.com/questions/283645/python-list-in-sql-query-as-parameter — William Gaul, Oct 10 '13 at 01:50
say both the table and the list are massive. a string joined WHERE clause would be highly inefficient — WindowsMaker, Oct 10 '13 at 02:03

score 3 · Accepted Answer · answered Oct 10 '13 at 08:31

You could write the list to a temporary table, and join with that. To make the join more efficient, ensure that at least one of the join columns (preferrably that of the smaller table) is indexed.

However, if the list is not too long, you can simply use the IN operator:

SELECT *
FROM MyTable
WHERE ID IN ('a', 'd', 'e')

Python sqlite3 INNER JOIN with a python list

1 Answers1