SQL query with a variable amount of parameters

[Antares]

I am trying to create SQL query with variable amount of parameters, and can't figure out how to do it. I have this:

def sql_select(query, *args):
    database = sqlite3.connect(DATABASE_FILE)
    database.row_factory = sqlite3.Row
    db = database.cursor()
    db.execute(query, args)
    result = [dict(row) for row in db.fetchall()]
    database.close()
    return result

@app.route("/checkout", methods=["GET", "POST"])
def checkout():
    if request.method == "POST":
        items = json.loads(request.form["cart_checkout"])
        items_IDs = list(items)
        sqlQuery = "SELECT * FROM Items WHERE item_id IN (" + ",".join("?" * len(items_IDs)) + ") ORDER BY item_id ASC"
        cart_items = sql_select(sqlQuery, items_IDs)
        ...

And I get an error: Incorrect number of bindings supplied. The current statement uses 3, and there are 1 supplied.
So how do I make SQL see that items_IDs is actually a list of parameters instead of just considering it as one parameter itself?

[noisefloor]

Hi,

generally speaking, use the format method of strings or, for Python 3.6 and up, f-strings. Building strings with + works, but is bad style since ever.

This should work:

>>> ids = [1, 2, 4]
>>> query = 'SELECT foo, bar, spam FROM the_table WHERE item_id IN ({}) ORDER BY item_id ASC'
>>> placeholder = '?'
>>> placeholders = ', '.join([placeholder for _ in ids])
>>> query.format(placeholders)
'SELECT foo, bar, spam FROM the_table WHERE item_id IN (?, ?, ?) ORDER BY item_id ASC'
>>>

Hint: when it is getting more complex, I would recommend to think about using an ORM to access the database. SQLAlchemy is the leading ORM for Python (outside Django), but PeeWee is also a nice solution and a bit easier (and less powerful) than SQLAlchemy.

Regards, noisefloor

[Antares]

Well, this doesn't really help with the issue. You've given another example of building a query itself. But my query is correct, I have the same result as the one you've shown at the end of your code example. The issue is in this line of code:

cart_items = sql_select(sqlQuery, items_IDs)

As I need to provide the db.execute() with some actual parameters for the placed ?,?,?... in the query. And as I pass items_IDs, which is a list, I get that error that for my few ?... in the querry I only provide 1 parameter (items_IDs). So how I tell the query that there are actually several items in that list?

The weird thing is, I've seen similar examples of code for this problem on some forums, so it apparently should be like a working solution, but for some reason it doesn't work for me.

[noisefloor]

Hi,

ok, understood it wrong.

I would suggest to at a print function printing items_IDs to actually see what it looks like.
Please post the output here. Please also print items

Regards, noisefloor

[perfringo]

Unpacking?

[Antares]

Items:
{'6': 3, '7': 1, '8': 2}

Items_IDs:
['6', '7', '8']

[perfringo]

Probably I am off the mark, but 'classical' example of usage unspecified number of arguments / unpacking:

>>> def average(*args): 
...     return sum(args) / len(args) 
>>> grades = [99, 80, 85, 89]                                                     
>>> average(*grades)                                                              
88.25

[noisefloor]

Hi,

Probably I am off the mark,

Yes you are. It doesn't work with the DB API 2.0, as you need to have a matching number of placeholders in the query.

@Antares: Just a guess - make items a tuple, not a list. By the way: To get a list of the keys of a dict, use keys = my_dict.keys()

Regards, noisefloor

[Antares]

This code is at work, I'll see at Monday how it'll behave with a tuple.
About .keys(), from what I've gathered, it was a fine way to do it in Python 2.x, now the output of that command is a bit ugly. Doing list(dict) is mentioned among all the possibilities of how to get the keys of a dict as a list, together with [*dict].

[noisefloor]

Hi,

it was a fine way to do it in Python 2.x, now the output of that command is a bit ugly. Doing list(dict) is mentioned among all the possibilities of how to get the keys of a dict as a list, together with [*dict].

???

>>> a = {'1': 1, '2': 2}
>>> a.keys()
dict_keys(['1', '2'])
>>> tuple(a.keys())
('1', '2')
>>> 

Regards, noisefloor