Cannot select events from SQLite table with WHERE and LIKE queries

Question

Hi I am working on a project where I am trying to select data from a SQLite table and display them on a simple webpage. I am trying to display events for the current week with today's events displayed at the top of the page followed by tomorrow's events, etc. I am trying to select the events in my SQLite table that match the date, but when I load the webpage it is blank. No events show (whereas if I simply SELECT * FROM the table all the events are there, so I know the error has something to do with my SQL query. Could someone please explain what I am doing wrong?

If it helps, variables date1 through date7 all return a string like YYYY-MM-DD which I use to select events from the SQLite table that match this date. I have to use LIKE because in the table <date> is stored as YYYY-MM-DD 00:00:00, but I do not care about the time only the day. Then I pass <rows> in as <events> which is the variable I use in my HTML file. Thank you very much!

# 1st day
date1 = str((datetime.datetime.now() + datetime.timedelta(days=0)).date())
rows1 = db.execute ("SELECT * FROM events WHERE date LIKE :date ORDER BY date", date = date1)

# 2nd day
date2 = str((datetime.datetime.now() + datetime.timedelta(days=1)).date())
rows2 = db.execute ("SELECT * FROM events WHERE date LIKE :date ORDER BY date", date = date2)

# 3rd day
date3 = str((datetime.datetime.now() + datetime.timedelta(days=2)).date())
rows3 = db.execute ("SELECT * FROM events WHERE date LIKE :date ORDER BY date", date = date3)

# 4th day
date4 = str((datetime.datetime.now() + datetime.timedelta(days=3)).date())
rows4 = db.execute ("SELECT * FROM events WHERE date LIKE :date ORDER BY date", date = date4)

# 5th day
date5 = str((datetime.datetime.now() + datetime.timedelta(days=4)).date())
rows5 = db.execute ("SELECT * FROM events WHERE date LIKE :date ORDER BY date", date = date5)

# 6th day
date6 = str((datetime.datetime.now() + datetime.timedelta(days=5)).date())
rows6 = db.execute ("SELECT * FROM events WHERE date LIKE :date ORDER BY date", date = date6)

# 7th day
date7 = str((datetime.datetime.now() + datetime.timedelta(days=6)).date())
rows7 = db.execute ("SELECT * FROM events WHERE date LIKE :date ORDER BY date", date = date7)

return render_template("index.html", events1 = rows1, events2 = rows2, events3 = rows3, evetns4 = rows4, events5 = rows5, events6 = rows6, events7 = rows7)

Use a datetime comparison. http://stackoverflow.com/questions/1975737/sqlite-datetime-comparison — McNets, Dec 05 '16 at 23:10

arturro · Accepted Answer · 2016-12-05T23:36:53.340

0

Change:

rows1 = db.execute ("SELECT * FROM events WHERE date LIKE :date ORDER BY date", date = date1)

to (add date(date) to remove time part).

   rows1 = db.execute ("SELECT * FROM events WHERE date(date) =:date ORDER BY date", date = date1)

edited Dec 05 '16 at 23:36

answered Dec 05 '16 at 23:07

arturro

1,598
1
10
13

Hi, before I actually had it without the string conversion but when I tried to load my webpage I got the error: rows1 = db.execute ("SELECT * FROM events WHERE date LIKE :date ORDER BY date", date = date1) File "/usr/lib/python3/dist-packages/cs50/sql.py", line 41, in execute raise RuntimeError(e) RuntimeError: Don't know how to literal-quote value datetime.date(2016, 12, 5) – A. Sharma Dec 05 '16 at 23:10
@A.Sharma ok, so leave str() but change SELECT like I did in the answer above (add date(date) to remove time part from datetime). – arturro Dec 05 '16 at 23:38
Hi, thanks for your help! We actually were fiddling around with it and found that we had to add the '%' onto our date variables in order for the SQL query to work. – A. Sharma Dec 06 '16 at 05:02

Cannot select events from SQLite table with WHERE and LIKE queries

1 Answers1