使用 psycopg2 为 Postgres 转义 SQL“LIKE”值

Question

psycopg2 是否具有为 Postgres 转义 LIKE 操作数的值的功能？

例如，我可能想要匹配以字符串“20% of all”开头的字符串，所以我想这样写：

 sql = '... WHERE ... LIKE %(myvalue)s'
cursor.fetchall(sql, { 'myvalue': escape_sql_like('20% of all') + '%' }

我可以在此处插入现有的 escape_sql_like 函数吗？

（与 How to quote a string value explicitly (Python DB API/Psycopg2) 类似的问题，但我在那里找不到答案。）

原文由 EMP 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

是的，这真是一团糟。默认情况下，MySQL 和 PostgreSQL 都为此使用反斜杠转义。如果您还使用反斜杠而不是使用参数化再次转义字符串，这将是一个可怕的痛苦，而且根据 ANSI SQL:1992，这也是不正确的，它表示默认情况下在正常字符串转义之上没有额外的转义字符，并且因此无法包含文字 % 或 _ 。

如果您使用 NO_BACKSLASH_ESCAPE MySQL 中的 sql_mode 或 standard_conforming_strings 关闭反斜杠转义符（它们本身不符合 ANSI SQL），我认为简单的反斜杠替换方法也会出错 --- PostgreSQL 中的 conf（PostgreSQL 开发人员现在已经威胁要为几个版本做）。

唯一真正的解决方案是使用鲜为人知的 LIKE...ESCAPE 语法为 LIKE 模式指定一个显式转义字符。这被用来代替 MySQL 和 PostgreSQL 中的反斜杠转义，使它们符合其他人所做的并提供一种有保证的方式来包含带外字符。例如，使用 = 符号作为转义符：

 # look for term anywhere within title
term= term.replace('=', '==').replace('%', '=%').replace('_', '=_')
sql= "SELECT * FROM things WHERE description LIKE %(like)s ESCAPE '='"
cursor.execute(sql, dict(like= '%'+term+'%'))

这适用于 PostgreSQL、MySQL 和 ANSI SQL 兼容的数据库（当然，模数 paramstyle 在不同的数据库模块上会发生变化）。

MS SQL Server/Sybase 可能仍然存在问题，它显然也允许 [a-z] LIKE 表达式中的样式字符组。在这种情况下，您还希望使用 .replace('[', '=[') 转义文字 [ 字符。但是根据 ANSI SQL 转义一个不需要转义的字符是无效的！ （啊！）因此，尽管它可能仍然可以跨真实的 DBMS 工作，但您仍然不符合 ANSI。叹…

原文由 bobince 发布，翻译遵循 CC BY-SA 2.5 许可协议