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.

Answer 1

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 ,) )

Answer 2

You ought to use SQL ESCAPE clause:

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