在Redshift查询中转义引号

Question

我正在尝试将我的代码重构到符合PEP8标准以达到更好的可读性，但是我在转义SQL查询中的引号方面遇到了困难。 我有两个查询。第一个是简单的SQL查询。第二个是Redshift UNLOAD命令。

query = '''SELECT * FROM redshift_table
           LEFT JOIN
           (SELECT DISTINCT * FROM redshift_view) v
           ON redshift_table.account_number = v.card_no
           WHERE timestamp < date_trunc('day', CURRENT_DATE)
           AND timestamp >=  (CURRENT_DATE - INTERVAL '1 days')'''

unload = '''UNLOAD ('%s') to '%s'
            credentials 'aws_access_key_id=%s;aws_secret_access_key=%s'
            delimiter as '%s'parallel off ALLOWOVERWRITE''' % (query, s3_path, access_key, aws_secret, file_delimiter)

由于 SQL 查询嵌入在 UNLOAD 命令中，我只能通过在引号前面加上三个反斜杠来转义引号使其正常工作：'day' 变成了 ///'day///'。这并不是最理想的解决方案，我想知道是否有其他方法可以解决这个问题。

非常感谢您的帮助。谢谢。

- Zihs

Answer 1

Masashi的答案并不总是适用，例如如果查询中已经有转义字符串（例如在正则表达式中）。

为了获得更强大的解决方案，您可以利用美元引用。 Redshift文档指向此页面的“美元引用字符串常量”部分。您可以使用美元符号引用您的查询，而不是手动转义引号，如下所示：

unload = """UNLOAD ($$ %s $$) ...as""" % (query, ...)

如果您在查询中使用$$，这种方法也会失效。如果想要更加安全，请在美元符号内部使用随机标签：

import uuid

# Leading underscore is necessary since this needs to be a valid
# identifier, i.e. can only start with a letter or underscore.
quote_tag = '_' + uuid.uuid4().hex

unload = 'UNLOAD ($%s$ %s $%s$) ...'.format(quote_tag, query, quote_tag, ...)

- Diego

---

你知道如果查询中有反斜杠会发生什么吗？需要对它们进行转义吗？ - camikiller

如果你正在使用美元引用符，我不知道。 - Diego

Answer 2

由于您仅需要在卸载命令中引号前插入反斜杠，因此使用转义函数可以解决问题。以下是一个示例。

def escape_quote(value):
  return value.replace("'", "\\'")

query = '''SELECT * FROM redshift_table
           LEFT JOIN
           (SELECT DISTINCT * FROM redshift_view) v
           ON redshift_table.account_number = v.card_no
           WHERE timestamp < date_trunc('day', CURRENT_DATE)
           AND timestamp >=  (CURRENT_DATE - INTERVAL '1 days')'''

unload = '''UNLOAD ('%s') to '%s'
            credentials 'aws_access_key_id=%s;aws_secret_access_key=%s'
            delimiter as '%s'parallel off ALLOWOVERWRITE''' % (escape_quote(query), s3_path, access_key, aws_secret, file_delimiter)

- Masashi M

---

这绝对是一个优雅的解决方案。不过我在想，是否有一种方法可以利用已经执行 SQL 查询转义的 psycopg2 的功能，以避免添加额外的代码呢？ - Zihs

这段代码将有效地取消转义反斜杠。阅读Friedl的《精通正则表达式》以获取标准的正则表达式，以匹配和转义带有嵌入式转义字符的引用字符串。 - bluesmoon