0

I have the below SQL query to fetch data based on month_id which contains the year and month:

sql_hours = str("""select * from monthly_hours where month_id IN  \
                          (201701,201702,201703,201704,201705,201706,201707,201708,201709,201710,201711,201712) and class = 'Optimal';""")

the base table looks like:

device_id   month_id    group   hours   tier
53          201705      TX      11.31   Optimal
28          201701      WS      31.56   Sub - Optimal
32          201706      WX      2.93    Optimal
57          201709      TS      2.68    Optimal 
28          201702      OE      0.70    Optimal

Here based on the number of months user wants to take data of and current month the values inside IN() would change. Ex. Current month_id is 201802 and if no_of_months = 6, my query would become:

sql_hours = str("""select * from monthly_hours where month_id IN  \
                          (201707,201708,201709,201710,201711,201712) and class = 'Optimal';""")

There is a lag of 2 months for data and hence the latest data would be of 201712(for current month of Feb.However in March the latest data would be of 201801). so the month_id's have to fall within 201707 and 201712. If the no_of_months = 12 then the month_id's have to fall within 201701 and 201712.

Can someone please help me with this? I am not sure how so much parameterization of the sql query can be done in python!!

Shuvayan Das
  • 1,198
  • 3
  • 20
  • 40
  • I'm sorry but I just can't get what your question is _exactly_ ? – bruno desthuilliers Feb 09 '18 at 11:36
  • select the months for the IN clause based on the number of months specified. So if number of months is 6, from Feb,2018(current_dates_month) to Sep,2017 will be the list of months used for filtering on month_id. – Shuvayan Das Feb 09 '18 at 12:18
  • What does it have to do with SQL then ? You just want to generate a list of "YYYYMM" strings (hint: you want strings here, if you're using ints you're doing it wrong, those are numeric codes but NOT ints) starting from one YYYYMM and going back N months. There's nothing magical nor overly complex here and there are already quite a few questions about such date manipulations, cf https://stackoverflow.com/questions/3424899/whats-the-simplest-way-to-subtract-a-month-from-a-date-in-python for example – bruno desthuilliers Feb 09 '18 at 12:34

1 Answers1

1

you could update the query string with the variable, using .format() to parametrize, example:

dates = '201707,201708,201709,201710,201711,201712'

sql_hours = str("select * from monthly_hours where month_id IN  \
                          ({}) and class = 'Optimal';".format(dates))
lorenzori
  • 737
  • 7
  • 23
  • 3
    Only do this if you're 101% sure the variable comes from your very own code and not from any external (user, file, request, whatever) input - unless you don't mind opening your app to SQL injections of course (http://bobby-tables.com/) – bruno desthuilliers Feb 09 '18 at 11:35