3

My query on execution of this fails:

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

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 %'));

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.

user3422637
  • 3,967
  • 17
  • 49
  • 72
  • what about doubling the %? e.g. `cursor.execute("SELECT name FROM products WHERE rating > 3 AND category like 'Automation %%'");` – mgilson Oct 23 '14 at 20:32

2 Answers2

5

You can probably escape it using an extra %:

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

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

Community
  • 1
  • 1
mgilson
  • 300,191
  • 65
  • 633
  • 696
-3

Try to use format. If you have a string, against use %s you can use {}. 

sql = "SELECT name FROM products WHERE rating > {0} AND category like 'Automation %'"
slq = sql.format(3)
cursor.execute(sql);

This is much better and compliance to new versions of python. And, plus, you can get annoyed of % in your query.

Andre Fonseca
  • 356
  • 3
  • 4