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?

Chrysoprase

what about using just one question mark " IN ( VALUES ? )" just an idea idk if would work – Felicitous

@Felicitous Unfortunately that gives me an OperationalError: near "?": syntax error – Chrysoprase

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 (?,?),(?,?),(?,?),(?,?),(?,?))

Prolix

That was my first guess as well, but I just get the first listed ProgrammingError: Incorrect number of bindings supplied. The current statement uses 10, and there are 5 supplied. – Chrysoprase

Oops just fixed it. – Prolix

It works! I had to fix a few commands before accepting in order to test it properly, but it works. Thank you so much! – Chrysoprase

My bad ... (-- insert a face palm here -- ). I was focused on the OP wrong conclusion in the beginning of the post and missed the bottom – Hoashis

I just need a simple WHERE software IN ({supported_software}) and supported_software is a tuple of strings. – Footbridge

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)

Knobloch

That seems like a better idea than the one I'm going with currently! I'll have to try that tomorrow! But I do unfortunately have to give the answer to @blhsing, as it works for me with no further reordering. Thank you very much, though! – Chrysoprase

No problem at all and no hard feelings. I'm just glad my answer was able to help! – Knobloch

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)

Shipowner

Using sum for joining a sequence of tuples is terribly inefficient. – Prolix

Either with a list comprehension like how it is my example or itertools.chain like what the official documentation says. sum is terribly inefficient because it creates a new tuple for each addition operation. – Prolix

@DavidדודוMarkovitz Thank you! I don't know why I didn't realise that I just needed to flatten my list of tuples into a list. I somehow had it in my mind that tuples in the query had to correspond to tuples in the input list. I'll keep your answer in mind for the next queries! – Chrysoprase