Python3.8, MySQL5.7: passing active wildcards to LIKE query

Question

I am using python3.8 and mysql.connector. Can I parameterize a query that contains a LIKE clause and pass in active % wildcard(s)?

To tame the incoming pattern I have tried:

pattern = re.sub(r'%+', '%', target)
~and~
pattern = re.sub(r'%+', '%%', target)
~and~
pattern = re.sub(r'%+', '\\%', target)

for the queries I have tried:

cursor.execute(f"""
    DELETE FROM thnigs
    WHERE user =  %(user)s
        AND widget LIKE %(pattern)s
""", dict(user=username, pattern=pattern))

cursor.execute(f"""
    DELETE FROM thnigs
    WHERE user =  %s
        AND widget LIKE %s
""", (username, pattern))

pattern could contain just about anything really. I know that mysql.connector will escape the input but if the input string contains any number of percent symbols the query hangs then dies with: 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

I have tried switching the LIKES for RLIKES aand changing the wildcards to simple .* strings with the same result -- if there is any active wildcard then the query dies.

Values that have not worked so far:

pattern = "%red"
pattern = "red%"

If this is not possible then I suppose I could pull in all values and do the wildcard search locally in the app but that feels wrong. The possible dataset could potentially become large.

Is there a correct or better way?

** Edit ** added another % replacement pattern that i have tried

Linkboy

Answer 1

You must escape % with %% in the sql query text that is passed, this is what I remember at least from long ago.

See also

mysql LIKE with double percent

and there my answer.

Strobila

Yep, tried that too. If I manage to get one or more %s in the string the query does. – Linkboy

@7Reeds Meaning it works now? Or do you mean you tried, but that did not solve it in the end? Just to make sure I understand your comment. – Strobila

just went back to check. If my initial string is "%red" or "red%" and the percent signs are doubled then it works, thank you. If the initial string is "%red%" and the percents are doubled to "%%red%%" then it throws the exception above. – Linkboy

I got RLIKE to work. never did find the magic for regular LIKE and the %s. Cheers – Linkboy

@7Reeds Then this cannot be called completely solved, perhaps someone else will contribute. It could be a duplicate anyway, I had the same issue a year ago and I got it to work with the help of then existing SO threads. If it is not a duplicate, your question will get more attendance anyway. – Strobila