The question is published on by Tutorial Guruji team.
To begin, I’m sorry if I don’t use the right terms, it’s for a class (the only programming class I ever took) and I’m french. I want to make an app in Python where I enter a game category (and other information) to find a game that correspond to the criteria. The apps then search in a SQL database. The problem is that since it will be a graphic app, the category can change at “any” moment. So I wanted to put it as an ? in my SQL query and fill it later (with the user entry), but with this way it only finds the games that have only the one category that I wrote in their category description (some have more than 1 category).
query = ('''
SELECT "attributes.boardgamecategory", "details.name", "details.description" FROM BoardGames
WHERE "details.maxplayers" <= ?
AND "details.maxplaytime" <= ?
AND "game.type" = 'boardgame'
AND "attributes.boardgamecategory" LIKE ?
ORDER BY RANDOM() LIMIT 1
''')
cur.execute(query, (max_players, max_playtime, 'Adventure'))
So I tried replace the ‘?’ by ‘%?%’, but it won’t work:
query = ('''
SELECT "attributes.boardgamecategory", "details.name", "details.description" FROM BoardGames
WHERE "details.maxplayers" <= ?
AND "details.maxplaytime" <= ?
AND "game.type" = 'boardgame'
AND "attributes.boardgamecategory" LIKE '%?%'
ORDER BY RANDOM() LIMIT 1
''')
cur.execute(query, (max_players, max_playtime, 'Adventure'))
>>>sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 3 supplied.
I don’t know what else to try. Please help me!
Answer
You should be binding %something% to the wildcard LIKE placeholder:
query = """
SELECT attributes.boardgamecategory, details.name, details.description
FROM BoardGames
WHERE details.maxplayers <= ? AND
details.maxplaytime <= ? AND
game.type = 'boardgame' AND
attributes.boardgamecategory LIKE ?
ORDER BY RANDOM() LIMIT 1
"""
cur.execute(query, (max_players, max_playtime, '%Adventure%'))