7

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?

Improve this question
2
  • what about using just one question mark " IN ( VALUES ? )" just an idea idk if would work Commented Mar 11, 2020 at 18:49
  • @z44.nelther Unfortunately that gives me an OperationalError: near "?": syntax error Commented Mar 11, 2020 at 18:56

3 Answers 3

Reset to default
6

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 (?,?),(?,?),(?,?),(?,?),(?,?))
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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.
Oops just fixed it.
It works! I had to fix a few commands before accepting in order to test it properly, but it works. Thank you so much!
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
I just need a simple WHERE software IN ({supported_software}) and supported_software is a tuple of strings.
1

@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)
Improve this answer

3 Comments

Using sum for joining a sequence of tuples is terribly inefficient.
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.
@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!
1

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)
Improve this answer

2 Comments

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!
No problem at all and no hard feelings. I'm just glad my answer was able to help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.