SQLITE3 with Python - Query using multiple variables with wildcards-CodePudding

I can't seem to find out if it's possible to accomplish what I'm trying to do.

I have a database I want to be searchable by users. In the "parts" table, I search via part_number and description. I know that "%Like%" allows matching for strings, but what if the user types the words of a description in the wrong order?

My solution was to chop the words up, query seperately, append the data to lists and compare the lists together to keep only the duplicates.

part_number = 111
description = "test test test"

description = description.split(" ")
for word in description:
     query = ("SELECT * FROM parts WHERE part_number like ? and description like ?;")
     data = cursor.execute(query, ('%'   part_number   '%', '%'   word '%')).fetchall()
     list_of_lists.append(data)

What i'd like to happen:

query = "SELECT * FROM parts WHERE part_number like ?"
for word in description:
     query = query   " and description like ?"

cursor.execute(query, ('%'   part_number   '%', -> amount of words to fill in required wildcards generated above)).fetchall()

Is there any way to use wildcards with lists that are of unknown length? Would it be better to build the query with an arbitrarily high number of wildcards that default to "_" to not interrupt any possible matches?

I'd like to do whatever is secure as users will be conducting searches directly.

Sorry for any errors in the provided code, my original code sample is hundreds of lines long.

CodePudding user response：

Use a list comprehension to concatenate % to each element of description.

cursor.execute(query, ['%'   part_number   '%']   ['%'   d   '%' for d in description'])

CodePudding user response：

Couldn't you do something like this with .format()? I'm guessing you want the ? filled with the 'word' that is looping.

I use pyodbc for my connection and .format() functions fine for my purposes - maybe the same in the package you're using.

I also added in a list creation statement

part_number = 111
description = "test test test"

list_of_lists = []
description = description.split(" ")
for word in description:
     query = "SELECT * FROM parts WHERE part_number like {} and description like {};".format(word, word)
     data = cursor.execute(query).fetchall()
     list_of_lists.append(data)