用 LIKE 中的通配符替换 Python SQLite 参数 [英] Python SQLite parameter substitution with wildcards in LIKE

问题描述

我正在尝试对 Python 的 Sqlite 库使用参数化 LIKE 查询，如下所示:

I am attempting to use a parametrized LIKE query with Python's Sqlite library as below:

self.cursor.execute("select string from stringtable where string like '%?%' and type = ?", (searchstr,type))

但是?通配符内部没有被评估，让我出现这个错误:

but the ? inside of the wildcard is not being evaluated leaving me with this error:

"sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied."

我还尝试使用标记版本的查询:

I also tried to use the tagged version of querying with:

like '%:searchstr%' 并且在具有 {"searchstr":searchstr...

但是当我这样做时，查询会运行但从不返回任何结果，即使手动输入 "like '%a%'"... 应该返回数百个结果

but when I do that the query runs but never returns any results even though manually putting in "like '%a%'"... return hundreds of results as it should

请问有什么建议吗?

推荐答案

引号保护 ? 或 :name 不被当作占位符——它们'从字面上看.您需要在传递的字符串周围放置百分号，并使用不带引号的普通占位符.即:

The quotes protect either ? or :name from being taken as a place-holder -- they're taken literally. You need to place the percent signs around the string you're passing, and use the plain placeholder without quotes. I.e.:

self.cursor.execute(
  "select string from stringtable where string like ? and type = ?",
  ('%'+searchstr+'%', type))

请注意，? 都没有放在引号中——这正是将它们用作占位符的方式.

Note that neither ? is in quotes -- and that's exactly as it should be for them to be taken as placeholders.

这篇关于用 LIKE 中的通配符替换 Python SQLite 参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！