Skip to main content
 首页 » 编程设计

python之仅选择日期和年份 SQLite

2025年04月02日17TianFang

我有一个结构如下的 SQLite 数据库:

ID      Date              Time           Cost 
"1" "2016-05-14"    "17:39:35.925973"   "1.98" 
"2" "2016-05-14"    "17:39:54.181083"   "2.84" 
"3" "2016-05-14"    "17:40:26.408492"   "2.99" 
"4" "2016-05-14"    "17:41:28.197353"   "3.39" 
"25"    "2016-05-19"    "14:44:27.235790"   "2.5" 
"26"    "2016-05-19"    "14:46:35.177696"   "1.58" 
"27"    "2016-05-19"    "14:49:12.902651"   "0.1"    
"28"    "2016-05-20"    "21:35:32.446997"   "2.25"   

我想按月和年计算成本,忽略日期。

我在 python 中执行此操作,但我什至不知道仅选择月份和年份的 sqlite 代码。

我试过这段代码:

def SumByMonth(): 
    usrDate = raw_input('Enter yymm: ') 
    dateyear = datetime.datetime.strptime(usrDate, "%y%m").date() 
    month_sql = '''SELECT cost FROM Finance WHERE strftime('%m%y', date) = ( ?  )''' 
    month_price = [t[0] for t in cur.execute(month_sql, ( dateyear,) )] 
    sum_total = sum(month_price) 
    print 'sum is for', dateyear,'is', sum_total 

我得到的只是 sum is for 2016-05-01 is 0 所以它似乎是在没有提供日期的情况下选择该月的第一天。我怎样才能只选择月份和年份?

谢谢

请您参考如下方法:

SQLite 没有用于两位数年份的格式说明符。引用:https://www.sqlite.org/lang_datefunc.html

而且,正如@DanielRoseman 提到的:

  1. 您混淆了年份和月份,并且
  2. 您不必要地将字符串转换为日期时间。

改为:

def SumByMonth(): 
    yyyymm = raw_input('Enter yyyymm: ') 
    month_sql = '''SELECT cost FROM Finance WHERE strftime('%Y%m',date) = ?''' 
    month_price = [t[0] for t in cur.execute(month_sql,(yyyymm,))] 
    sum_total = sum(month_price) 
    print 'sum is for', yyyymm,'is', sum_total 

更简洁的做法是:

def SumByMonth(): 
    yyyymm = raw_input('Enter yyyymm: ') 
    month_sql = '''SELECT SUM(cost) FROM Finance WHERE strftime('%Y%m',date) = ?''' 
    sum_total = cur.execute(month_sql,(yyyymm,)).fetchone()[0] 
    print 'sum is for', yyyymm,'is', sum_total