How to Automate SQL code using Python in efficient way

Viewed 189

I have a sql code which looks like this: Database: RedShift

WITH
X as
(  
 SELECT  distinct pn , pg , ic, sr  , cm, fq , m1  , m2  , m3  , m4
 FROM table1  ORDER BY 1,2,3
),

table2 AS
 (
   Select g,p,t , avg(ss) as ss , avg(ce) as ce , sum(av) as ps
  from
 (
select distinct ic AS g , pn AS p , cm AS t , ss , cast((sum_m1/nullif(sum_m2,0)) as decimal(3,2)) as 
 ce , av
from
(
  select *
  , cast((sum(m3) over (partition by ic, pn,cm)) as decimal) as ss
  , sum(m1) over (partition by ic, pn,cm) as sum_m1
  , sum(m2) over (partition by ic, pn,cm) as sum_m2
  , cast((avg(m2) over (partition by ic, pn,cm)) as decimal) as av
  from X
  ORDER BY 1,2,3
)
order by 1,2,3
 )
 where ss is not null
  group by 1,2,3
  order by 1,2,3
  )

The group by value g,p,t changes every time and so it creates table for every new combination of g,p,t values.

One way to automate is dump this sql code in Python which might be inefficient: Here is my approach-> I replace all values in list by {} braces Example say:

I store all possible group by values in a list.

G=[g1,g2,g3]

P=[p1,p2,p3]

T=[t1,t2,t3]

Connection to databse:

   c= psycopg2.connect(database=db,host=host,port=port,user=user,password=password,sslmode='require')
   data2={}

   for g in G:

     for p in P:

        for t in T:
           sqlstr=( """ WITH
                            X as
                          (  
                 SELECT  distinct pn , pg , ic, sr  , cm, fq , m1  , m2  , m3  , m4
              FROM table1  ORDER BY 1,2,3
                        ),

                  table2 AS
              Select {},{},{} , avg(ss) as ss , avg(ce) as ce , sum(av) as ps
                             from
                         (
                    select distinct ic AS g , pn AS p , cm AS t , ss , cast((sum_m1/nullif(sum_m2,0)) as decimal(3,2)) as 
                      ce , av
                          from
                  (
                       select *
               , cast((sum(m3) over (partition by ic, pn,cm)) as decimal) as ss
                , sum(m1) over (partition by ic, pn,cm) as sum_m1
               , sum(m2) over (partition by ic, pn,cm) as sum_m2
               , cast((avg(m2) over (partition by ic, pn,cm)) as decimal) as av
                      from X
                     ORDER BY 1,2,3
                       )
                   order by 1,2,3
                    )
                 where ss is not null
                   group by 1,2,3
                   order by 1,2,3
                    ), select * from table2 """).format(g,p,t)
  data2[g+"_"+p+"_"+t] = pd.read_sql_query(sqlstr, c)

Is there any better way to pass parameters as in above code sequence of {} should be maintained to pass parameters in order ??

Can we use some other approach other than SQL? In pythonic way?

0 Answers
Related