def get_all_courses(self):
courses = []
with dbapi2.connect(self.dbfile) as connection:
cursor = connection.cursor()
cursor.execute(
"""select course.*, faculty.shortened_name, department.shortened_name,
people.name, people.surname, classroom.door_number
from course, classroom, faculty, instructor, department, people
where (course.department_id = department.id
and course.instructor_id = instructor.id
and classroom.faculty_id = faculty.id
and course.classroom_id = classroom.id
and people.tr_id = instructor.tr_id) order by (department.shortened_name);"""
)
for row in cursor:
course = Course(*row[:14])
course.faculty_name = row[14]
course.department_name = row[15]
course.instructor_name = row[16] + " " + row[17]
course.door_number = row[18]
courses.append(course)
return courses
def get_courses_taken_by_student(self, student_id):
courses = []
with dbapi2.connect(self.dbfile) as connection:
cursor = connection.cursor()
query = """select course.*, faculty.shortened_name, department.shortened_name,
people.name, people.surname, classroom.door_number, taken_course.grade
from course, classroom, faculty, instructor, department, people, taken_course
where (course.department_id = department.id
and course.instructor_id = instructor.id
and classroom.faculty_id = faculty.id
and course.classroom_id = classroom.id
and people.tr_id = instructor.tr_id
and taken_course.crn = course.crn
and student_id = %s) order by (course.crn);"""
cursor.execute(query, (student_id, ))
for row in cursor:
course = Course(*row[:14])
course.faculty_name = row[14]
course.department_name = row[15]
course.instructor_name = row[16] + " " + row[17]
course.door_number = row[18]
course.grade = row[19]
courses.append(course)
return courses