Writing to file from Redshift using Psycopg2 throwing exception

Question

I am using Python 2.6 where I want to read data from Redshift from a table called "user_data" & write to a file using psycopg2. I get the records in "cursor" & in this way I try to write to file :

fout = open('user_data', 'w')
cursor.copy_to(fout,"user_data", sep='|')

When I execute this,it throws following error:

psycopg2.ProgrammingError: syntax error at or near "stdout"
LINE 1: COPY user_data TO stdout WITH DELIMITER AS

What does it wants to tell ?? Whats the solution to this one ??

Answer 1

Unfortunatelly COPY TO STDOUT is not supported by Amazon Redshift. You can achieve your task in 2 ways. First one is to do as @kadalamittai suggested (iterate cursor and write to file in python) and second is to use the UNLOAD command. I would recommend the latter when dealing with huge amounts of data.

UNLOAD enables you to output the result of your query directly to Amazon S3 in CSV file format. Example:

UNLOAD ('select * from users where last_seen_dt>\'2016-11-10\'')
TO 's3://object-path/name-prefix'

More info about the UNLOAD command here.

You can then use boto to download the file from S3:

import boto3
s3 = boto3.resource('s3')
s3_client = boto3.client('s3')

s3_client.download_file('QueryResultsBucket', 'remote_query_results.csv', 'query_results.csv')
print(open('query_results.csv').read())