How do I SELECT WHERE IN VALUES with tuples in Python sqlite3?

Question

I have an SQLite database with three columns, and I'm trying to use parameter substitution for tuples to SELECT rows. This is my table:

conn = sqlite3.connect("SomeDb.sqlite3")

conn.execute("""
CREATE TABLE RoadSegmentDistribution(
    Source INTEGER,
    Destination INTEGER,
    Distribution TEXT
)
""")

I know how to substitute with non-tuples, but I cannot figure out how to do it with tuples.

Based on this answer, I thought I simply had to substitute each and every value across the list of tuples:

for e in conn.execute("""
    SELECT *
    FROM RoadSegmentDistribution
    WHERE (
        Source, Destination
    ) IN (VALUES (?,?), (?,?), (?,?), (?,?), (?,?))
    """,
    [(1, 2),(2, 3),(4, 5),(6, 7),(8, 9)]
):
    print(e)

but then I get the error

ProgrammingError: Incorrect number of bindings supplied. The current statement uses 10, and there are 5 supplied.

Obviously this means that I only need one question mark per tuple, right?:

for e in conn.execute("""
    SELECT *
    FROM RoadSegmentDistribution
    WHERE (
        Source, Destination
    ) IN (VALUES (?), (?), (?), (?), (?))
    """,
    [(1, 2),(2, 3),(4, 5),(6, 7),(8, 9)]
):
    print(e)

But then I get this error:

OperationalError: sub-select returns 1 columns - expected 2

I cannot insert the values manually like in the linked answer, since I don't know what the list parameter contains. This means that I need to do some kind of ",".join() based on the length of the list, but I'll figure that out once I know how to do substitution with a fixed-length list.

How would I do this?

Answer 1

Using the str.join method is indeed a good way to achieve this, given the lack of native support for container-based placeholders in SQL engines:

values = [(1, 2), (2, 3), (4, 5), (6, 7), (8, 9)]

for e in conn.execute(f"""
    SELECT *
    FROM RoadSegmentDistribution
    WHERE (
        Source, Destination
    ) IN (VALUES {','.join(f'({",".join("?" * len(t))})' for t in values)})
    """,
    [i for t in values for i in t]
):
    print(e)

where, with the given values:

f"""
    SELECT *
    FROM RoadSegmentDistribution
    WHERE (
        Source, Destination
    ) IN (VALUES {','.join(f'({",".join("?" * len(t))})' for t in values)})
"""

would expand into:

SELECT *
FROM RoadSegmentDistribution
WHERE (
    Source, Destination
) IN (VALUES (?,?),(?,?),(?,?),(?,?),(?,?))

Answer 2

You can try structuring your query differently and generating it based on the parameters you pass.

query_head ="SELECT * FROM RoadSegmentDistribution WHERE "
params = [(1, 2),(2, 3),(4, 5),(6, 7),(8, 9)]

def add_condition(pair):
    condition = f"(Source = {pair[0]} AND Destination = {pair[1]})"
    return condition

def build_query(query_head,params):
    conditions = ' OR '.join([add_condition(param) for param in params])
    query = query_head+conditions
    return query

query = build_query(query_head,params)
conn.execute(query)

Answer 3

---

@jmkjaer: Not an answer, but needed for clarification

You got it the other way around -
Your query needs 10 bindings and it does not care how they are spread around the query.
All you need to do is to supply a list of 10 elements:

[1,2, 2,3, 4,5, 6,7, 8,9]

demo

import sqlite3

conn = sqlite3.connect(':memory:')
vals = [(1, 2),(2, 3),(4, 5),(6, 7),(8, 9)]
flat_vals = [e for t in vals for e in t ]

for e in conn.execute("values (?,?),(?,?),(?,?),(?,?),(?,?)",flat_vals):
    print(e)


(1, 2)
(2, 3)
(4, 5)
(6, 7)
(8, 9)