我有一个结构如下的 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 提到的:
- 您混淆了年份和月份,并且
- 您不必要地将字符串转换为日期时间。
改为:
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
