How to escape the % and \ signs in pymysql using LIKE clause?

Question

I want to find something like "probability: 10%" or "10% high" in my 'events' column, but when I used the code below:

conn = pymysql.connect(host="localhost", port=3306, user='myid', passwd='mypwd', db='mydb', charset='utf8')
curs = conn.cursor()

key = "%"
curs.execute(
        "SELECT count(*) AS number FROM city WHERE events LIKE %s",
        ("%" + key + "%",)
    )

it returned every row in the table. It executed this query:

  SELECT count(*) AS number FROM city WHERE events LIKE '%%%'

like this, which I didn't intend.

Searching for the backslash sign also gave me incorrect results.

What should I do to get the correct result?

Thanks in advance.

MySQL uses C-style escaping so to get a backslash in a string (before it gets sent to `LIKE`) you need two backslashes. Try `SELECT count(*) AS number FROM city WHERE events LIKE '%\\%%'` — Nick, May 12 '19 at 08:01
https://stackoverflow.com/questions/5020130/how-to-escape-literal-percent-sign-when-no-backslash-escapes-option-is-enabled — thebjorn, May 12 '19 at 08:06
So do I need to define a new function to escape that wildcard chracters? I heard that pymysql execute function escape the string, but it doesn't? — Park, May 12 '19 at 08:08
it does, but like with `%` is a special case. pymysql can't see the difference between the `%` signs in '`%s%s%s'` which is what you're passing in. — thebjorn, May 12 '19 at 08:11

score 11 · Accepted Answer · edited Feb 05 '21 at 12:37

11

instead of the concat the wildchar in param you could use concat in SQL and pass the value

 curs.execute(
    "SELECT count(*) AS number FROM city WHERE events LIKE CONCAT('%', %s, '%')",
     (key ,)
)

or as uggested by @Notinlist

curs.execute( "SELECT count(*) AS number FROM city WHERE events LIKE CONCAT('%%', %s, '%%')", (key ,) )

edited Feb 05 '21 at 12:37

Notinlist

16,144
10
57
99

answered May 12 '19 at 08:11

ScaisEdge

131,976
10
91
107

2

It gave an error to me `unsupported format character ''' (0x27) at index ...`. The solution was to use `"SELECT ... LIKE CONCAT('%%', %s, '%%')"` – Notinlist Feb 05 '21 at 07:53
@Notinlist .. thanks ..answer updated with your suggestion – ScaisEdge Feb 05 '21 at 08:44

score 1 · Answer 2 · answered May 12 '19 at 07:48

1

You ought to use SQL ESCAPE clause:

curs.execute(
    "SELECT count(*) AS number FROM city WHERE events LIKE '%#%%' ESCAPE '#'"
)

answered May 12 '19 at 07:48

reartnew

247
1
9

so there is no function in pymysql that helps escaping the word like '\' or '%'? I thought when using string to second parameter of excute function, pymysql automatically escape string. it doesn't? – Park May 12 '19 at 07:58

How to escape the % and \ signs in pymysql using LIKE clause?

2 Answers2