I have the following piece of code which joins two tables and prints in a 2D list
print("Resident:\tRoom:\t\tLast check-in:")
print(' ')
join = "SELECT users.name, users.room, attendance.clock_in FROM users JOIN attendance ON users.id = attendance.user_id"
cursor.execute(join)
output = cursor.fetchall()
for x in range(len(output)):
for y in range(len(output[x])):
print(output[x][y], end = '\t | \t')
print()
giving me the following output:
Resident: Room: Last check-in:
Name 1 | W235 | 2020-05-10 17:57:17 |
Name 2 | E289 | 2020-05-10 17:58:08 |
Name 1 | W235 | 2020-05-10 18:18:22 |
Name 2 | E289 | 2020-05-10 18:18:36 |
Name 1 | W235 | 2020-05-10 18:18:41 |
Name 1 | W235 | 2020-05-16 16:01:38 |
Name 2 | E289 | 2020-05-16 16:07:31 |
Name 2 | E289 | 2020-05-16 17:13:50 |
Name 2 | E289 | 2020-05-16 17:13:53 |
Name 2 | E289 | 2020-05-16 17:13:58 |
Name 1 | W235 | 2020-06-29 22:41:23 |
Name 2 | E289 | 2020-07-15 16:27:13 |
Name 1 | W235 | 2020-07-15 16:27:23 |
I need the output to be only those rows which checked-in today (in my output it's the last two rows). I tried manipulating with my "join" line, for example, changing it to:
join = "SELECT users.name, users.room, attendance.clock_in FROM users JOIN attendance ON users.id = attendance.user_id AND attendance.clock_in LIKE CURRENT_DATE"
but in all my attempts I get empty output instead of what I need:
Resident: Room: Last check-in:
So how do I type it correctly so that cursor selects only rows which include today's date?
join = "SELECT users.name, users.room, attendance.clock_in FROM users JOIN attendance ON users.id = attendance.user_id WHERE attendance.clock_in >= CURDATE() AND attendance.clock_in < CURDATE() + INTERVAL 1 DAY"
join = "SELECT users.name, users.room, attendance.clock_in FROM users " \
"JOIN attendance ON users.id = attendance.user_id " \
"WHERE attendance.clock_in >= CURDATE() " \
"AND attendance.clock_in < CURDATE() + INTERVAL 1 DAY"