Passing arrays as parameters to SQLite

tl;dr

conn.execute(
    "select * from ... where ... in (select value from json_each(?))",
    (json.dumps(list_of_values),),
)

Lists of values in SQLite

SQLite doesn’t have an array type as Postgres does, but we can exploit the JSON extension to do operations with arrays.

An example of why this may be needed. Let’s say you have a table with names, and you want to select all the id’s of people from a list of names. Something like:

SELECT id FROM user WHERE name IN ('bob', 'alice')

This can work if you only have a fixed list of names, but if that list is variable and long, the options end up being building a query string with the correct number of parameters and then passing our list to the query. For instance:

names = ["bob", "alice"]
query = f"SELECT id FROM user WHERE name IN ({','.join(['?']*len(names))})"

# supposing that `conn` is an sqlite3 connection
# and the table mentioned above exists
conn.execute(query, names)

Using the JSON extension

I found this a bit inconvenient and error-prone. Instead of doing this, we can use the [json_each](https://www.sqlite.org/json1.html#jeach) function from the JSON extension. That will turn a JSON object into a table of values. Then we can just select the value column from that table.

This will let us pass a list of values as a single parameter.

Example:

import sqlite3
import json

conn = sqlite3.connect(":memory:", isolation_level=None)

conn.execute(
    """
CREATE TABLE IF NOT EXISTS user(
  id integer primary key,
  name text)
"""
)

conn.execute(
    """
INSERT INTO user(name) VALUES
  ('tom'),
  ('julia'),
  ('alex'),
  ('john'),
  ('alice'),
  ('bob')
"""
)

names_to_retrieve = ["alice", "bob"]

r = conn.execute(
    "SELECT id FROM user WHERE name IN (SELECT value FROM json_each(?))",
    (json.dumps(names_to_retrieve),),
).fetchall()

print(r)

# [(5,), (6,)]

This approach has a few advantages:

The list names_to_retrieve can change in size, and we won’t need to change the query
It’s easier to understand and debug the query. (This is just a personal preference)
We don’t need to build the query string each time. This can let use prepare the statement only once. Apart from the reasons mentioned, this technique can also be used for inserting values:

import sqlite3
import json

conn = sqlite3.connect(":memory:", isolation_level=None)

conn.execute(
    """
CREATE TABLE IF NOT EXISTS user(
  id integer primary key,
  name text)
"""
)

names_to_insert = ["tom", "julia", "alex", "john", "alice", "bob"]

conn.execute(
    "INSERT INTO user(name) SELECT value FROM json_each(?)",
    (json.dumps(names_to_insert),),
)

names_to_retrieve = ["alice", "bob"]

r = conn.execute(
    "SELECT id FROM user WHERE name IN (SELECT value FROM json_each(?))",
    (json.dumps(names_to_retrieve),),
).fetchall()

print(r)

# [(5,), (6,)]

rand[om]

Passing arrays as parameters to SQLite

tl;dr

Lists of values in SQLite

Using the JSON extension