Python SQL NOT LIKE , trying to exclude multiple values

Question

I have a code, below, where a user can put a list of allergies. I want my code to come up with a list of recipes, that exclude the ingredients from the user allergy list. I made a test code but cannot figure out how to exclude all three ingredients at the same time.

userallergy = conn.execute ('SELECT Allergies from User where userid = 4')
userallergy = userallergy.fetchall()
userallergy = userallergy[0][0].replace(" ","")
listallergy=list(userallergy.split(","))

listallergy = ["'%" + i.capitalize() + "%'" for i in listallergy]

print([listallergy])
query='SELECT RecipeName FROM Recipess where Ingredients Not LIKE {}'.format(listallergy[1])
print(query)

aller = conn.execute(query)
saferecipe = aller.fetchall()
print(saferecipe)

Maybe you should try REGEX? – Seyi Daniel Apr 27 '21 at 08:25 — Seyi Daniel, Apr 27 '21 at 08:25

Shivam Agrawal · Accepted Answer · 2021-04-27T10:09:38.253

Use REGEXP for a clean approach(Though you can't use it directly, check out the answer of Problem with regexp python and sqlite ):

def regexp(expr, item):
    reg = re.compile(expr)
    return reg.search(item) is not None

conn.create_function("REGEXP", 2, regexp)

allergies = "|".join([i.capitalize() for i in listallergy])

print(allergies)
query='SELECT RecipeName FROM Recipess where Ingredients Not REGXP \'{}\''.format(allergies)
print(query)

A simple approach is to join all the statements by AND operator(but as @alexis mentioned in comments it comes with it's own issues):

allergies = " AND ".join(["Ingredients Not LIKE '%" + i.capitalize() + "%'" for i in listallergy])

print(allergies)
query='SELECT RecipeName FROM Recipess where {}'.format(allergies)
print(query)

The query is good, but this will get ugly when an allergy name with an apostrophe arrives... and downright dangerous if you put it in production. SQL injection danger! (And you can't just use prepared statements with a variable length list like here...) — alexis, Apr 27 '21 at 09:50

score 0 · Answer 2 · answered Apr 27 '21 at 09:12

0

You may use MySQL REGEX 'OR' to exclude all three. Your query should then look like this:

query = f"SELECT RecipeName FROM `Recipess` WHERE Ingredients NOT REGEXP '{('|').join(listallergy)}'"

answered Apr 27 '21 at 09:12

Seyi Daniel

2,259
2
8
18

Python SQL NOT LIKE , trying to exclude multiple values

2 Answers2