python - 如何使用 Python 自动执行 SQL 查询中的日期

Question

我有 SQL 连接并命名为 db_connection我每周运行一次

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)

比如我想把所有的日期改成date(o.timestamp)<=\'2017-10-04\' and date(o.timestamp)>=\'2017-09-28\' ，但需要时间，而且容易出错。有什么解决方案可以使日期只被调用一次吗？

Answer 1

python

虚拟解决方案是使用占位符以便编辑单个条目:

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 = ...

Pandas 的参数

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

使用函数CURDATE您可以参数化您的查询。比如每周一查询，想提取上周(周一到周日)的数据，可以设置条件:

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

这显然取决于何时您运行查询，但如果提取由调度程序自动进行，则效果很好。

或者，将其与 DAYOFWEEK 结合使用我们可以让它独立于它运行的星期几(再次:考虑上周一到周日):

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

显然，您必须根据需要调整日期偏移量。