Python3.8、MySQL5.7:将活动通配符传递给 LIKE 查询 [英] Python3.8, MySQL5.7: passing active wildcards to LIKE query

问题描述

我正在使用 python3.8 和 mysql.connector.我可以参数化包含 LIKE 子句的查询并传入活动的 % 通配符吗?

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)

对于我尝试过的查询:

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 几乎可以包含任何东西.我知道 mysql.connector 会转义输入，但如果输入字符串包含任意数量的百分比符号，查询就会挂起，然后终止: 1205 (HY000): Lock wait timeout exceeded;尝试重新启动事务

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

我尝试将 LIKES 切换为 RLIKES a 并将通配符更改为简单的 .* 字符串，结果相同——如果有任何活动的通配符则查询终止.

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

推荐答案

你必须在传递的 sql 查询文本中用 %% 转义 %，这是我至少从很久以前就记得的.

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

另见

mysql LIKE 与双百分比

还有我的答案.

这篇关于Python3.8、MySQL5.7:将活动通配符传递给 LIKE 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！