Python - Dynamic variable in Redshift SQL Query


I'm developing in a python environment and I want to call an sql query using psycopg2

Lets say I have the following UNLOAD command in an .sql file:

UNLOAD ( ' Some SQL Query ' ) TO 's3://%PATH%' ...

In the sql file the %PATH% should be explicit like: 'folder1/folder3/file_name'.

But I want the python program to set this %PATH% in runtime. which means that the .sql file containts something like %PATH% and will be set only in runtime.

Any idea of how to do it?


You simply specify a <a href="https://docs.python.org/3/library/string.html#format-string-syntax" rel="nofollow">replacement field</a> in your SQL file, and the use a format command.

Create your file like this

UNLOAD ('Some SQL Query') TO 's3://{bucket}/{key}'

And use this file in python like

template = open('file1.sql', 'r').read() query = template.format(bucket='mybucket', key='folder/file.csv')


Implementing it this way will give you a tough time.

The best way to do is to dump the file at a static location:

UNLOAD ( ' Some SQL Query ' ) TO 's3://path/to/static/s3_bucket' ...

and then use (via a shellscript / or opt for a suitable command for any other script)

aws s3 mv $source $destination

Here, you may pass any value for $destination which can be easily populated during run-time.


In short, you've dumped the file in s3 at a fixed location (using UNLOAD) and moved it to the location of your choice or a location populated at run time (using aws s3 mv...)



You would not be able to set up the UNLOAD path dynamically at runtime, however you could put your SQL statement in a something like a shell/python script where you can create variables with the path you'd like and then pass them into the query.

<a href="https://github.com/awslabs/amazon-redshift-utils/blob/master/src/UnloadCopyUtility/redshift-unload-copy.py" rel="nofollow">This</a> UNLOAD utility by AWS will get you started if you decide to go with a python script.


  • How to convert complex JSON to CSV using JQ 1.4
  • Find element and add class (jQuery)
  • What happens if mysql_insert_id called form different places and different browsers at the same time
  • How to grab a string that is between 2 strings
  • How do I chomp a string if I have Perl 4?
  • javascript Confirm replacement with return true/false
  • Insert space after period using sed
  • runtime error when linking ffmpeg libraries in qt creator
  • Use sed with regex and (
  • Ruby on Rails App deployed to heroku showing “We're sorry, but something went wrong”
  • Detecting null parameter in preprocessor macro
  • C# program and C++ DLL compiled for 32-bit system crash on 64-bit system
  • WPF ICommand CanExecute(): RaiseCanExecuteChanged() or automatic handling via DispatchTimer?
  • How solve “Qt: Untested Windows version 10.0 detected!”
  • Retrieving value from sql ExecuteScalar()
  • Read text file and split every line in MSBuild
  • C# - Serializing and deserializing static member
  • Java applet as stand-alone Windows application?
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Javascript Callbacks with Object constructor
  • MySQL WHERE-condition in procedure ignored
  • How can I use Kendo UI with Razor?
  • When should I choose bucket sort over other sorting algorithms?
  • Font Awesome Showing Box instead of Icons
  • Adding custom controls to a full screen movie
  • How do you troubleshoot character encoding problems?
  • Properly structure and highlight a GtkPopoverMenu using PyGObject
  • Comma separated Values
  • KeystoneJS: Relationships in Admin UI not updating
  • Error creating VM instance in Google Compute Engine
  • How to set the response of a form post action to a iframe source?
  • Hits per day in Google Big Query
  • Understanding cpu registers
  • how does django model after text[] in postgresql [duplicate]
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • Is it possible to post an object from jquery to bottle.py?
  • Checking variable from a different class in C#
  • reshape alternating columns in less time and using less memory
  • Python/Django TangoWithDjango Models and Databases