python - 在 SQLite3、Python 中按周选择数据

Question

我试图选择一周内的特定数据条目，但是，我不确定如何让我的程序识别一周开始和结束的日期。这是我当前的函数代码:

elif report_option == "C":
    print("Expense Report by Week: \n")
    year = int(input("Enter the year of the week's expenses you'd like to view (YYYY): \n"))
    month = int(input("Enter the month the week's expenses you'd like to view (MM): \n"))
    day = int(input("Enter the day the week's expenses you'd like to view (DD): \n"))
    date = datetime.date(year, month, day)
    c.execute("SELECT * FROM tblFinance")
    conn.commit()
    for row in c.fetchall():
        print(row)
    categories = []
    amountspent = []

    for row in c.execute('SELECT CategoryID, AmountSpent from tblFinance WHERE FinanceDate=?', (date,)):
        print(row[0])
        print(row[1])
        categories.append(row[0])
        amountspent.append(row[1])

        plt.plot(categories, amountspent, '-')

        plt.ylabel('Amount Spent')
        plt.xlabel('Category ID')
    plt.show()
    menu()

如有任何帮助，我们将不胜感激，谢谢!编辑:我现在正在尝试让一个单独的功能工作一个月。然而，like 功能对我来说并不顺利!月份函数代码如下:

elif report_option == "D":
    print("Expense Report by Month: \n")
    month_input = input("Enter the year and month of the month's expenses you'd like to view (YYYY-MM): \n")
    c.execute("SELECT * FROM tblFinance")
    conn.commit()
    for row in c.fetchall():
        print(row)
    categories = []
    amountspent = []

    for row in c.execute('SELECT CategoryID, AmountSpent from tblFinance WHERE FinanceDate LIKE ?',
                         (month_input, )):
        print(row[0])
        print(row[1])
        categories.append(row[0])
        amountspent.append(row[1])

        plt.plot(categories, amountspent, '-')

        plt.ylabel('Amount Spent')
        plt.xlabel('Category ID')
    plt.show()
    menu()

Answer 1

您可以使用 Python 获取一周的开始和结束日期:

week_start = date - datetime.timedelta(days=date.weekday())
next_week_start = week_start + datetime.timedelta(days=7)

然后，如果您需要获取本周的记录，请在查询中使用 Between :

c.execute('SELECT CategoryID, AmountSpent from tblFinance WHERE FinanceDate BETWEEN ? AND ?', (week_start, next_week_start))

编辑:对于月份范围，请按以下方式更新代码:

# Leave all the user input the same except you don't need to input day  for month range query
month_start = datetime.date(year, month, 1)
next_month_start = datetime.date(year, (month + 1) % 12, 1)
c.execute('SELECT CategoryID, AmountSpent from tblFinance WHERE FinanceDate BETWEEN ? AND ?', (month_start, next_month_start))