How to query date values from sql using python where I only have the year and month

Question

I have these records in my database:

and I want to select them using python, however I only have the year and month which is stored in a variable:

Here is my code:

However this does not work, and I've only gotten it to work when using a fully specific date, e.g. 2022-06-11.

Answer 1

The values of the column Date in the table are strings in the proper format yyyy-MM-dd and you can use the operator LIKE:

c.execute("SELECT Category, Amount FROM transactions WHERE Date LIKE ? || '%'", (month_selected,))

or, with the function strftime():

c.execute("SELECT Category, Amount FROM transactions WHERE strftime('%Y-%m', Date) = ?", (month_selected,))