Why won't this Mysql statement work? Trying to select column where

Hi all hope everyone is good!

I wonder if someone can help me to get an execute statement to work.

I’m trying to make a function that gets the last 7 days from an sql database.

This is the function:

def get_last_7_days(mycursor):
    current_date = dt.now().date()
    date_minus_7 = dt.today() - td(days=7)
    dates = pd.date_range(date_minus_7.date(), current_date)
    for date in dates:
        row = mycursor.execute(f"SELECT * FROM work_times WHERE date = {date.date()}")
        print(row)

The problem is that the execute statement is returning ‘None’ even though the column name is correct and there are rows in the database that match the corresponding dates queried.

This is the table being queried:

+------------+------------+----------+--------------+------+
| date       | start_time | end_time | hours_worked | pay  |
+------------+------------+----------+--------------+------+
| 2023-08-09 | 07:00:00   | 14:30:00 |            7 |   81 |
| 2023-08-08 | 14:30:00   | 22:00:00 |            7 |   81 |
| 2023-08-07 | 07:00:00   | 14:30:00 |            7 |   81 |
| 2023-08-06 | 14:00:00   | 22:00:00 |            8 |   89 |
+------------+------------+----------+--------------+------+

I’ve tried everything I could find on the net but from what I can see this statement should be correct.

These are some of the things I’ve tried:

• I’ve tried typecasting the variable ‘date’ to a str
• I’ve tried adding quote marks around the ‘date.date()’ variable
• I’ve tried using place holders in the execute function (%s)
• I’ve tried using a sub query with a date range
• I’ve tried using back ticks on the column name like this (date) just in case it was clashing with the sql keywords.
• Also tried using back ticks and quote marks around the column name and value

The next thing I will do is convert it to a data-frame first and then query the data using pandas. I assume this will work but, out of curiosity, I would like to know why this statement doesn’t work first.

Thanks all!

Edit:

If it helps in any way this statement in the terminal works fine:

SELECT * FROM work_times WHERE date = '2023-08-09';

>Solution :

you need also to fetch the data like in the below code

def get_last_7_days(mycursor):
    current_date = dt.now().date()
    date_minus_7 = dt.today() - td(days=7)
    dates = pd.date_range(date_minus_7.date(), current_date)
    for date in dates:
        mycursor.execute(f"SELECT * FROM work_times WHERE date = {date.date()}")
        row = mycursor.fetchone()
        while row is not None:
            print(row)
            return row