CODEWITHSUNDEEP
sqlamazon-redshiftdynamic-sql
dark horse
dark horse

Reputation: 3709

SQL - Passing where clause as a varible (Redshift)

I have a SQL query that I use that have only the where clause changes. I would like to store the SQL query as one variable and each of the where clauses as separate variable.

Given below is the SQL query that I am using:

Query 1: 

select a.prod_name,a.prod_cat,b.sale_date 
from products a 
   join sales b on a.id = b.sale_id and b.type=new

Query 2: 

select a.prod_name,a.prod_cat,b.sale_date 
from products a 
  join sales b on a.id = b.sale_id and b.type=modify

I plan to write a Python script that would execute this query using variable.

Upvotes: 0

Views: 1087

Answers (1)

Red Boy
Red Boy

Reputation: 5719

Here you go to pass the variable in case of Python. IF you simple would like to go in python, though if your case is this much simple, why to introduce even python, plain simple shell script should work for you. Anyways, 

import psycopg2


def redshift(type_var):


    conn = psycopg2.connect(dbname='*********', host='*********.redshift.amazonaws.com', port='5439', user='****', password='*******8')
    cur = conn.cursor();

    cur.execute("begin;")

    sql ="select a.prod_name,a.prod_cat,b.sale_date from products a   join sales b on a.id = b.sale_id and b.type='%s'" %(type_var)
    cur.execute(sql)
    results = cur.fetchall()
    print("Copy executed fine!"+results)




"""Call one with new"""
redshift('new');
"""Call two with modify"""
redshift('modify');

Upvotes: 1

Related Questions