Python MySQL参数化查询与LIKE语句中的％通配符冲突 [英] Python MySQL parameterized query conflicts with % wildcard in LIKE statement

问题描述

我对此执行的查询失败:

My query on execution of this fails:

cursor.execute("SELECT name FROM products WHERE rating > %s AND category like 'Automation %'", (3));

因为对百分比的使用感到困惑，原因有两个-像LIKE通配符和python MySQL db执行参数.

Because it gets confused about the percentage usage for two different reasons -- as a LIKE wildcard and as a parameter on python MySQL db execution.

如果我这样运行此查询，它将起作用:

If I run this query like this, it works:

cursor.execute("SELECT name FROM products WHERE rating > 3 AND category like 'Automation %'");

如果我按以下方式运行查询，它将再次起作用:

If I run the query as below, it again works:

cursor.execute("SELECT name FROM products WHERE rating > %s AND category = 'Automation '", (3));

但这不是解决方案.我想同时使用通配符和参数.

But that's not a solution. I want to use both the wildcard and the parameter.

我找到了一种解决方法，即将我的常量通配符作为变量传递:

I found a workaround, which is to pass in my constant wildcard as a variable:

 cursor.execute("SELECT name FROM products WHERE rating > %s AND category like %s", (3, 'Automation %'));

这可行，但是我需要一个更优雅的解决方案.我不想将常量作为变量传递.我的SQL语句在一个大查询中可能有很多LIKE语句.

This works but I need a more elegant solution. I don't want to pass constants as variables. My SQL statement could have a lot of LIKE statements in a big query.

推荐答案

您可能可以使用额外的%来对其进行转义:

You can probably escape it using an extra %:

cursor.execute("SELECT name FROM products WHERE rating > %s AND category like 'Automation %%'", (3));

这显然适用于MySQLdb ，我希望它也适用于python-mysql. .

This apparently works for MySQLdb and I would expect it to work for python-mysql as well. . .

这篇关于Python MySQL参数化查询与LIKE语句中的％通配符冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！