How to ask a query to SQL when the LIKE variable/pattern can change (?, ‘%?%’, …) Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of How to ask a query to SQL when the LIKE variable/pattern can change (?, ‘%?%’, …) without wasting too much if your time.

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%'))
We are here to answer your question about How to ask a query to SQL when the LIKE variable/pattern can change (?, ‘%?%’, …) - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji