Python SQL不喜欢，尝试排除多个值

Question

我有一个代码，在下面，用户可以把过敏列表。我希望我的代码列出一个食谱列表，从用户过敏列表中排除这些成分。我编写了一个测试代码，但无法找出如何同时排除所有三种成分

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)

Answer 1

您可以使用MySQL REGEX“或”来排除这三个选项。然后，您的查询应该如下所示：

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

Answer 2

使用REGEXP作为一种干净的方法（尽管您不能直接使用它，请查看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)

一种简单的方法是通过AND操作符连接所有语句（但正如@alexis在评论中提到的，它有自己的问题）：

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

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