Reputation: 71
I am trying to get the records from my database where studentID, and lessonDate are equal to specific results. The StudentID seems to work fine, but lessonDate does not. Because of date formats, I have converted all dates to strings to be put into the database. I have set up database file so that the field is text field. I am trying to get the lesson name and rating that a student got for all exercises that they performed on a particular date. Database diagram: https://docs.google.com/drawings/d/16IqlDN2iqzVCeaUUGgeCe1R98yhUI7vQswH0tK39cAk/edit?usp=sharing
I am certain that the StudentID is correct, as I use it in other parts of this function. I am certain that the date is correct, as the 'date' variable used in the 4th line results in the correct output written to the file, and I originally had the date in the SQL query as this variable, but it didn't work. I have tried printing the lessons where this student is the foreign key, and the date is '8-10-2016'. I really have no idea why this is happening. Any suggestion or hypothesis from anyone would be greatly appreciated.
template = ("{0:50}{1:50} \n")
print(lessonDateSet)
for date in list(lessonDateSet):
target.write("Lesson date: {} \n".format(date))
target.write("\n")
target.write(template.format("Exercise name:","rating:"))
self.cur.execute("""SELECT b.roadExerciseName, a.rating
FROM lessonExercises a LEFT JOIN roadExerciseInfo b ON a.roadExerciseID=b.roadExerciseID
LEFT JOIN lessons c ON c.lessonID=a.lessonID WHERE c.StudentID = {0} AND c.lessonDate = {1}""".format(studentInfo[0][0],"8-10-2016"))
fetch = self.cur.fetchall()
print(fetch, "fetch")
'fetch' is an empty list. after this. I have double and tripple checked my data. my data is definitely correct.
Upvotes: 0
Views: 44
Reputation: 599530
Your parameters are not being quoted correctly.
This is why you should not use string interpolation to add data into your queries. You should use the db-api's parameter substitution instead:
self.cur.execute("""SELECT b.roadExerciseName, a.rating
FROM lessonExercises a LEFT JOIN roadExerciseInfo b ON a.roadExerciseID=b.roadExerciseID
LEFT JOIN lessons c ON c.lessonID=a.lessonID
WHERE c.StudentID = ? AND c.lessonDate = ?""",
[studentInfo[0][0], "8-10-2016"])
Upvotes: 2