如何使用日期范围在python拉/查询数据在mySQL [英] How to use date range in python to pull /query data in mySQL

问题描述

如何使用python date在mySQL中提取数据？
我想要 day1 和 day2 （或 day1 <因此，我需要在where中的日期SQL语句，以便在SQL语句中使用SQL语句。在每个迭代中看起来像下面的列表（ n times）

  day1> ; ='2012-01-01'和day2< '2012-01-02'（n = 1）
 day1> ='2012-01-02'和第2天< '2012-01-03'（n = 2）
。 
。 
 day1> = yesterday和day2<今天（n次）
  

。

  Start_date ='2012-01-01'<  - 如何写在python 
 End_date = Today（）& - 和这
  

为了写：

  
 con.execute（select * from table where date> = day1 and date< day2）
  




解决方案

您需要 datetime 模块： -

  import datetime 
 start = datetime.date（2012,01,01）
 next = start + datetime.date.resolution 
 
 while next< = datetime.date.today（）：
 print start，next 
 
 con.execute（
 select * from table where date> ; =％s和日期<％s 
（开始，下一步））
 
 start = next 
 next = start + datetime.date.resolution 
  

重要通知：我更新了答案以解决严重问题。从不曾使用字符串格式（也就是％）构建SQL查询，因为它会遇到严重问题，包括 SQL注入 。使用 Python-< db_driver> api其中几乎所有RDMBes都提供相同的语法

  execute（select * from blah where x =％s AND y =％s，（x，y））
 ^ ^ ^ 
 1 1 2 
  

1]无引号，

2]无字串格式

How can I pull data in mySQL by day using python date? Say I want day1 and day2 ( or a day after day1 ) iterate for n times

So I need the date in "where" SQL statement to look like below list in each iteration (n times )

day1 >= '2012-01-01'  and  day2 < '2012-01-02'    ( n = 1 )
day1 >= '2012-01-02'  and  day2 < '2012-01-03'    ( n = 2 )
.
.
day1 >= yesterday    and day2  < today            ( n times ) 

.

Start_date = '2012-01-01'   <- How can I write this in python
End_date = Today()   <- and this 

So as to write:

for each iteration ..
    con.execute("select * from table where date >= day1 and date < day2" )

解决方案

You need to datetime module:-

import datetime
start = datetime.date(2012,01,01) 
next = start + datetime.date.resolution

while next <= datetime.date.today():
    print start, next

    con.execute("""
        select * from table where date >= %s and date < %s
    """, (start, next))

    start = next
    next = start + datetime.date.resolution

IMPORTANT NOTICE: I updated the answer to fix a serious problem. Never ever use string formatting (a.k.a. %) for building SQL queries since it is open to serious problems including SQL injection. Use Python-<db_driver> api where nearly all RDMBes offers the same syntax

execute("select * from blah where x=%s AND y=%s", (x, y))
                                     ^       ^  ^
                                     1       1  2

1] No quote,
2] No string formatting

这篇关于如何使用日期范围在python拉/查询数据在mySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！