我正在使用 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 查询
发布评论