How to Loop Through Dates in Python to pass into PostgresSQL query

Question

I have 2 date variables which I pass into a SQL query via Python. It looks something like this:

start = '2019-10-01'
finish = '2019-12-22'

code_block = '''select sum(revenue) from table
where date between '{start}' and '{finish}'
'''.format(start = start, finish = finish)

That gets me the data I want for the current quarter, however I want to be able to loop through this same query for the previous 5 quarters. Can someone help me figure out a way so that this runs for the current quarter, then updates both start and finish to previous quarter, runs the query, and then keeps going until 5 quarters ago?

Parfait · Accepted Answer · 2019-12-22 15:38:59Z

Consider adding a year and quarter grouping in aggregate SQL query and avoid the Python looping. And use a date difference of 15 months (i.e., 5 quarters) even use NOW() for end date. Also, use parameterization (supported in pandas) and not string formatting for dynamic querying.

code_block = '''select concat(date_part('year', date)::text,
                              'Q', date_part('quarter', date)::text) as yyyyqq, 
                       sum(revenue) as sum_revenue
                from table
                where date between (%s::date - INTERVAL '15 MONTHS') and NOW()
                group by date_part('year', date),
                         date_part('quarter', date)
             '''

df = pd.read_sql(code_block, myconn, params=[start])

If you still need separate quarterly data frames use groupby to build a dictionary of data frames for the 5 quarters.

# DICTIONARY OF QUARTERLY DATA FRAMES
df_dict = {i:g for i,g in df.groupby(['yyyyqq'])}

df_dict['2019Q4'].head()
df_dict['2019Q3'].tail()
df_dict['2019Q2'].describe()
...

marc_s · Accepted Answer · 2019-12-23 05:25:22Z

0

Just define a list with start dates and a list with finish dates and loop through them with:

for date_start, date_finish in zip(start_list, finish_list):
    start = date_start
    finish = date_finish

    # here you insert the query

Hope this is what you are looking for =)

edited Dec 23, 2019 at 5:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 22, 2019 at 15:05

RafaJM

4876 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to Loop Through Dates in Python to pass into PostgresSQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related