How to ask a query to SQL when the LIKE variable/pattern can change (?, '%?%', ...)

Question

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!

Tim Biegeleisen · Accepted Answer · 2019-12-13 03:58:11Z

1

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%'))

answered Dec 13, 2019 at 3:58

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

milie531 Over a year ago

This works! But I just use 'Adventure' as an example... It needs to change depending on what the user writes. It's gonna be «catego» : def find_a_game(max_playtime, max_players, catego)

Tim Biegeleisen Over a year ago

Then just build your wildcard expression, e.g. input = 'Adventure' ... exp = '%' + input + '%'

milie531 Over a year ago

OMG I swear I tried it sooner, but it didin't work... Must have done something wrong bcs it works now! Thank you verry much! :) cur.execute(query, (max_players, max_playtime, '%'+catego+'%'))

Collectives™ on Stack Overflow

How to ask a query to SQL when the LIKE variable/pattern can change (?, '%?%', ...)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related