Geek Answers Handbook

Quoting Quotes in a Redshift Request


I'm trying to refactor my code to PEP8 standards for readability, but I try my best to avoid quotes in my SQL queries. I have 2 questions. The first is a simple SQL query. The second Redshift UNLOAD team.

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)

Since the sql query is embedded inside the UNLOAD command, I can make it work by avoiding quotes by adding them with three backslashes: 'day' becomes /// 'day ///'.
This is not perfect, and I was wondering if there was a way around it.

Any help is appreciated. Thank.

+4
source share
1 answer

unload, escape. .

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)
0

More articles:


All Articles