How do I automate date in SQL query using Python

Question

I have SQL connection and named db_connection that I run in weekly basis

tran= pd.read_sql('SELECT order_id, p.user_id, p.barcode, title, o.timestamp, amount, p.name, qty FROM transactions t left join orders o on t.order_id = o.id left join products p on t.product_id = p.id where date(o.timestamp)<=\'2017-09-27\' and date(o.timestamp)>=\'2017-09-21\' and p.user_id = \'63165\' ', con=db_connection)

tran2= pd.read_sql('SELECT order_id, p.user_id, p.barcode, title, o.timestamp, amount, p.name, qty FROM transactions t left join orders o on t.order_id = o.id left join products p on t.product_id = p.id where date(o.timestamp)<=\'2017-09-27\' and date(o.timestamp)>=\'2017-09-21\' and p.user_id = \'62345\' ', con=db_connection)

...

tran22= pd.read_sql('SELECT order_id, p.user_id, p.barcode, title, o.timestamp, amount, p.name, qty FROM transactions t left join orders o on t.order_id = o.id left join products p on t.product_id = p.id where date(o.timestamp)<=\'2017-09-27\' and date(o.timestamp)>=\'2017-09-21\' and p.user_id = \'78345\' ', con=db_connection)

For example, I want to change all the date to date(o.timestamp)<=\'2017-10-04\' and date(o.timestamp)>=\'2017-09-28\', but it takes time and is prone to mistakes. Is there any solution to make the date is only called once?

Cavaz · Accepted Answer · 2017-10-11 08:23:32Z

1

Python

The dummy solution would be to use a placeholder in order to have a single entry to edit:

start = # evaluate or hardcode date
end = # evaluate or hardcode date

tran = pd.read_sql('... where date(o.timestamp)<=\'{END}\' and date(o.timestamp)>=\'{START}\' ...'.format(START=start, END=end), con=db_connection)
...
tran1 = ...

Panda's parameters

start = # evaluate or hardcode date
end= # evaluate or hardcode date
tran = pd.read_sql('... where date(o.timestamp)<=%(end)s and date(o.timestamp)>=%(start)s ...',
               con=db_connection, params={"start":start, "end":end})

CURDATE

Using the function CURDATE you can parametrize your query. For example, if you run the query every Monday and want the extract the data of the last week (Monday to Sunday), you can set the condition:

WHERE DATE(o.timestamp)<=(CURDATE() - INTERVAL 1 DAY) and DATE(o.timestamp)>=(CURDATE() - INTERVAL 8 DAY)

This obviously depends on when you run the query, but works well if the extraction is automated by a scheduler.

Or, combining it with DAYOFWEEK we can make it independent from the day of the week it is running (again: considering last Monday-to-Sunday)):

WHERE DATE(o.timestamp) >= (CURDATE() - INTERVAL (DAYOFWEEK(CURDATE())+5) DAY) AND DATE(o.timestamp) <= (CURDATE() - INTERVAL (DAYOFWEEK(CURDATE())-1) DAY)

Obviously you'll have to tune the day-offsets according to your needs.

edited Oct 11, 2017 at 8:23

answered Oct 9, 2017 at 11:59

Cavaz

3,13927 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nabih Ibrahim Bawazir Over a year ago

This is amazing, this is the solution

Cavaz Over a year ago

which is the one you were looking for?

Nabih Ibrahim Bawazir Over a year ago

Your first solution is not solution that I expected, but the final answer give me idea

Collectives™ on Stack Overflow

How do I automate date in SQL query using Python

1 Answer 1

Python

Panda's parameters

CURDATE

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Python

Panda's parameters

CURDATE

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related