python - MySQLdb Like operator with wildcard

Question

I am having problems executing a query with a wildcard in it with MySQLdb in python.

My code

sql = "SELECT COUNT(id) FROM table WHERE name LIKE '%s%'"
with con:
    cur.execute(sql, (name,))

The complicated thing about this is the % after the string %s. Based on the documentation, if there's no wildcard operator %, I could just do LIKE %s but I don't know how to proceed from there for my problem.

I found this question on stack Python MySQL parameterized query conflicts with % wildcard in LIKE statement which is similar to my question but doesn't take a dynamic argument. I tried escaping the wildcard operator like in the link above, but I still get a SQL syntax error.

Anyone has any idea?

Thanks

the % character is a special character and has to be escaped as %% — e4c5, Aug 24 '18 at 08:48

score 3 · Accepted Answer · answered Aug 24 '18 at 08:54

You should put the wildcard into the parameter.

param = '{}%'.format(name)
sql = "SELECT COUNT(id) FROM table WHERE name LIKE %s"
with con:
    cur.execute(sql, (param,))

Note, you shouldn't use quotes for the placeholder inside the SQL; those are added by the db-api.

python - MySQLdb Like operator with wildcard

1 Answers1