Python MySQL parameterized query conflicts with % wildcard in LIKE statement

Question

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.

what about doubling the %? e.g. cursor.execute("SELECT name FROM products WHERE rating > 3 AND category like 'Automation %%'"); — mgilson
– mgilson, Commented Oct 23, 2014 at 20:32

Community · Accepted Answer · 2017-05-23 12:04:04Z

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. . .

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Oct 23, 2014 at 21:12

mgilson

312k70 gold badges656 silver badges722 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andre Fonseca · Accepted Answer · 2014-10-23 20:32:30Z

-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.

answered Oct 23, 2014 at 20:32

Andre Fonseca

4664 silver badges4 bronze badges

1 Comment

mgilson Over a year ago

This is also subject to sql injection . . .

Collectives™ on Stack Overflow

Python MySQL parameterized query conflicts with % wildcard in LIKE statement

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related