Selecting data by week in SQLite3, Python

Question

I'm trying to make specific data entries within a week get selected, however, I'm unsure how to get my program to recognize the dates where the week would have started and ended. This is my current code for the function:

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()

Any help would be appreciated, thank you! EDIT: I am now trying to get a separate function working for month. However, the like function isn't going well for me! Code for month function below:

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()

https://stackoverflow.com/questions/19216334/python-give-start-and-end-of-week-data-from-a-given-date — Chris, Jan 09 '20 at 22:08

Yann · Accepted Answer · 2020-01-10T08:23:04.050

1

You can get the week start and end dates with Python:

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

Then if you need to get the records for this week use between in the query:

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

EDIT: For month range update the code this way:

# 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))

edited Jan 10 '20 at 08:23

answered Jan 09 '20 at 22:08

Yann

2,426
1
16
33

Ok, thank you, would the code in the answer posted below work?? – Keez Jan 09 '20 at 22:17
@Keez edit your question and post the code there, not as an answer. – forpas Jan 09 '20 at 22:20
Just tested, it worked! Thank you very much for your help!! – Keez Jan 09 '20 at 22:26
Would it be possible to use something similar to use for month start and end dates?? – Keez Jan 09 '20 at 22:44
@Keez sure, in this case, you just need to set your `day` variable to 1 and update `month` to the next month, like this for instance - `next_month = (month + 1) % 12`. – Yann Jan 09 '20 at 23:10
Sorry, that somehow confused me.. I put an edit in my question with what I'm trying to do.. Could you please show me what you mean because I'm trying to do it with the LIKE statement which isn't working! – Keez Jan 09 '20 at 23:37
@Keez Sorry for confusing, I didn't mean `like` as a key word, I updated the reply. Let me know if anything is unclear for you. – Yann Jan 10 '20 at 08:25

score 1 · Answer 2 · answered Jan 09 '20 at 22:13

I understand that you want to filter on the week to which the date given by the user belongs.

In SQLite, you can use date(mydate, 'weekday, 0') for that: it gives you the first day of the current week that date mydate belongs to. Then you can do:

SELECT CategoryID, AmountSpent 
FROM tblFinance 
WHERE FinanceDate >= date(?, 'weekday, 0') AND FinanceDate  < date(?, 'weekday 0', '7 days')

Both ? should be replaced with the same input value (the date provided by the user).

This expression uses an half-open interval interval for filtering, so it would properly handle the time part of FinanceDate, if any.

Selecting data by week in SQLite3, Python

2 Answers2