How to escape single quotes in Unload

Question

    conn_string = "dbname='{}' port='{}' user='{}' password='{}' host='{}'"\
            .format(dbname,port,user,password,host_url) 

    sql="""UNLOAD ('select col1,col2 from %s.visitation_hourly_summary_us where col4= '2018-07-10' and col5= '1';') TO 's3://%s/%s/%s.csv' \
            credentials 'aws_access_key_id=%s;aws_secret_access_key=%s' \
            MANIFEST GZIP ALLOWOVERWRITE;Commit;""" \
            % (schema_name,s3_bucket_name, schema,table,aws_access_key_id,\
            aws_secret_access_key)

con = psycopg2.connect(conn_string)
cur = con.cursor()
cur.execute(sql)

I'm trying to execute the above script to read the table and then create a file in S3

Since my columns are string I'm not able to skip the single quotes and I'm getting error as syntax error near where

Also, I've tried giving \ in where condition still it showing the same error.

Any help would be highly appreciated.

Thanks

what is the error you are getting? in my opinion your `conn_string` is wrong here — DanielM, Sep 25 '18 at 12:42
Also, do *not* use string formatting operations to build a query. Pass a parameterized query and a tuple of arguments separately to `execute` instead. — chepner, Sep 25 '18 at 12:44

score 13 · Answer 1 · answered May 03 '19 at 02:36

13

You can also use postgres style :

unload 
($$
select * from table where id='ABC'
$$)
to 's3://bucket/queries_results/20150324/table_dump/'
credentials 'aws_access_key_id=;aws_secret_access_key='
;

answered May 03 '19 at 02:36

Fact

1,308
12
24

This approach lets you have syntax highlighting for the query. – BiGYaN Feb 05 '20 at 01:19
This approach avoids escaping character hell, thanks ! – Cedias Sep 14 '20 at 10:11

krchun · Answer 2 · 2019-09-08T08:43:19.020

You would want to use two single quotes to enclose the value.

If your query contains quotes (for example to enclose literal values), put the literal between two sets of single quotation marks—you must also enclose the query between single quotation marks:

Example:

UNLOAD ('select * from venue where venuestate=''NV''')

Taken from the redshift documentation: https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html

score 3 · Accepted Answer · answered Sep 25 '18 at 15:11

As Sarang says, simply by replacing single quotes by double quotes in col4 and col5 values of your query should do the trick.

However I would suggest you to break your string down in smaller chunks easier to read and maintain. This way, you should be able to use execute as chepner suggests (and MySQL documentation):

# Create the inner SQL statement. Notice the single quotes for the general
# string and the double quotes for the col4 and col5 values
sql_stmt = ('SELECT col1, col2 '
            'FROM %s.visitation_hourly_summary_us '
            'WHERE col4 = "2018-07-10" AND col5= "1";' % schema_name)

# Format the s3 path
s3_target = 's3://%s/%s/%s.csv' % (s3_bucket_name, schema, table)

# Format credentials string
s3_credentials = 'aws_access_key_id=%s;aws_secret_access_key=%s' % (
    aws_access_key_id, aws_secret_access_key)

# Create a tuple with all preformatted strings
data = (sql_stmt, s3_target, s3_credentials)

# Format the s3 query skeleton
s3_stmt = ("UNLOAD ('%s') TO '%s' "
           "CREDENTIALS '%s' "
           "MANIFEST GZIP ALLOWOVERWRITE;Commit;")

con = psycopg2.connect(conn_string)
cur = con.cursor()
cur.execute(s3_stmt, data)

Aditya Vikram Singh · Answer 4 · 2019-01-05T09:01:25.273

' (single quotes can be sent as ) -> \\\\'

I had used this in the R as well as python Please find solutions

if your sql QUERY is

Select * from sample_table where register_date='2018-12-31'

then for unload command write it like this

sql=     """unload ('Select * from tnltemp.otpsuccess_details where register_date=\\\\'2018-12-31\\\\' ')
        to 's3://my-bucket/migration/exported_sample_table_' credentials 
        'aws_access_key_id=12234123;aws_secret_access_key=12345'
        DELIMITER AS ','
        NULL AS ''
        parallel off;""""



cur = con.cursor()
cur.execute(sql)

score 0 · Answer 5 · answered Sep 25 '18 at 12:53

0

You can put values in double quotes. 'select col1,col2 from %s.visitation_hourly_summary_us where col4= "2018-07-10" and col5= "1";'

answered Sep 25 '18 at 12:53

Sarang

126
7

How to escape single quotes in Unload

5 Answers5