CODEWITHSUNDEEP
pythonsqlsqlite
WindowsMaker
WindowsMaker

Reputation: 3400

INNER JOIN with a Python list

I am trying to select from a table where values of one of the columns are equal to values of a list:

SQLite table:

ID price
a 100
b 200
… …
z 2600

Python list:

["a", "d" , "e"]

I want to find the prices of each of those ID's. The obvious way is JOIN on ID but that list is not a table. How can I do this?

Upvotes: 1

Views: 1357

Answers (2)

Will Manley
Will Manley

Reputation: 2484

SQLite has Virtual Tables, but this is not directly exposed to Python with the built-in sqlite3 python bindings. There is a workaround though: Use the built in table-valued function json_each().

First in python serialise your list to JSON:

import json
j = json.dumps(["a", "d", "e"])

Then pass it to SQLite as a parameter. json_each will turn it back from a JSON string into a table:

db.execute("""
    WITH PythonList AS (SELECT value AS ID FROM json_each(?))
    SELECT *
    FROM MyTable
    INNER JOIN PythonList USING (ID)""",
    [j])

I've used a common table expression (WITH clause) allowing us to treat the JSON as a table named PythonList. The table has a single column: "ID".

For the purposes of this answer I've assumed that you do need virtual tables and JOIN. As @CL. has suggested using IN is more appropriate here, but it's also supported with JSON:

db.execute("""
    WITH PythonList AS (SELECT value AS ID FROM json_each(?))
    SELECT ID, price
    FROM MyTable
    WHERE ID IN PythonList""",
    [j])

To understand how it's working see the output here:

sqlite> SELECT * FROM json_each('["a", "d", "e"]');
+-----+-------+------+------+----+--------+---------+------+
| key | value | type | atom | id | parent | fullkey | path |
+-----+-------+------+------+----+--------+---------+------+
| 0   | a     | text | a    | 1  | NULL   | $[0]    | $    |
| 1   | d     | text | d    | 3  | NULL   | $[1]    | $    |
| 2   | e     | text | e    | 5  | NULL   | $[2]    | $    |
+-----+-------+------+------+----+--------+---------+------+

Advantages of json_each virtual tables vs. (...):

  • It's easier to use with parametrised queries when the number of elements varies - you don't need to generate the SQL dynamically, and a single prepared statement will work for any list length
  • You can use it for more complex queries when you really do need to JOIN (maybe you're augmenting data in SQL with data in Python)
  • It can be used when the number of elements gets too large to be used with parametrised queries. The limit is 999, so this is probably not an issue.

Disadvantages of json_each:

  • It's SQLite specific
  • It's harder to understand than IN (?, ?, ?)

Tested on SQLite fiddle with input:

.headers on
.mode table

CREATE TABLE MyTable(
    ID TEXT PRIMARY KEY NOT NULL,
    price INTEGER NOT NULL
);
INSERT INTO MyTable(ID, price) VALUES ('a', 100);
INSERT INTO MyTable(ID, price) VALUES ('b', 200);
INSERT INTO MyTable(ID, price) VALUES ('c', 300);
INSERT INTO MyTable(ID, price) VALUES ('d', 400);
INSERT INTO MyTable(ID, price) VALUES ('e', 500);
INSERT INTO MyTable(ID, price) VALUES ('f', 600);

-- Basic example
SELECT MyTable.ID, price
FROM MyTable
INNER JOIN json_each('["a", "d", "e"]') AS python_list ON python_list.value == MyTable.ID;

-- Using CTE
WITH PythonList AS (SELECT value AS ID FROM json_each('["a", "d", "e"]'))
SELECT *
FROM MyTable
INNER JOIN PythonList USING (ID);

-- Using IN
SELECT ID, price
FROM MyTable
WHERE ID IN (SELECT value FROM json_each('["a", "d", "e"]'));

-- How does json_each work?
SELECT * FROM json_each('["a", "d", "e"]');

Upvotes: 1

CL.
CL.

Reputation: 180070

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

Upvotes: 3

Related Questions