def instructor_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Instructor)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
i.id,
i.slack_handle,
i.specialty,
i.cohort_id,
i.user_id,
u.first_name,
u.last_name
FROM exercisesapp_instructor i
JOIN auth_user u ON i.user_id = u.id
ORDER BY u.last_name
""")
all_instructors = db_cursor.fetchall()
template_name = 'instructors/list.html'
context = {
'all_instructors': all_instructors
}
return render(request, template_name, context)
def get_students(request, exercise_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Student)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
s.id,
s.first_name,
s.last_name,
s.slack_handle,
s.cohort_id
FROM exercisesapp_student s
JOIN exercisesapp_instructor i ON i.cohort_id = s.cohort_id
WHERE i.id = ? AND s.id NOT IN (
SELECT
s.id
FROM exercisesapp_student s
LEFT JOIN exercisesapp_assignment a ON a.student_id = s.id
WHERE a.exercise_id =?
)
ORDER BY s.last_name
""", (request.user.instructor.id, exercise_id))
return db_cursor.fetchall()
def get_cohorts():
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Cohort)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
c.id,
c.name
FROM exercisesapp_cohort c
""")
return db_cursor.fetchall()
def get_cohort(cohort_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Cohort)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
c.id,
c.name
FROM exercisesapp_cohort c
WHERE c.id = ?
""", (cohort_id,))
return db_cursor.fetchone()
def exercise_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Exercise)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
e.id,
e.name,
e.language
FROM exercisesapp_exercise e
ORDER BY e.language, e.name
""")
all_exercises = db_cursor.fetchall()
grouped_exercises = {}
for exercise in all_exercises:
if exercise.language not in grouped_exercises:
grouped_exercises[exercise.language] = {
"language": exercise.language,
"exercise_list": [exercise]
}
else:
grouped_exercises[
exercise.language]["exercise_list"].append(exercise)
template_name = 'exercises/list.html'
context = {'all_exercises': grouped_exercises.values()}
return render(request, template_name, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO exercisesapp_exercise
(
name, language
)
VALUES (?, ?)
""", (form_data['name'], form_data['language']))
return redirect(reverse('exercisesapp:exercises'))
def get_exercise(exercise_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Exercise)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
e.id,
e.name,
e.language
FROM exercisesapp_exercise e
WHERE e.id = ?
""", (exercise_id, ))
return db_cursor.fetchone()
def get_student(student_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Student)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
s.id,
s.first_name,
s.last_name,
s.slack_handle,
s.cohort_id
FROM exercisesapp_student s
WHERE s.id = ?
""", (student_id, ))
return db_cursor.fetchone()
def student_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Student)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
s.id,
s.first_name,
s.last_name,
s.slack_handle,
s.cohort_id
FROM exercisesapp_student s
ORDER BY s.last_name
""")
all_students = db_cursor.fetchall()
template_name = 'students/list.html'
context = {
'all_students': all_students
}
return render(request, template_name, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute("""
INSERT INTO exercisesapp_student
(
first_name, last_name, slack_handle,
cohort_id
)
VALUES (?, ?, ?, ?)
""",
(form_data['first_name'], form_data['last_name'],
form_data['slack_handle'], form_data['cohort']))
return redirect(reverse('exercisesapp:students'))
def get_instructor(instructor_id):
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Instructor)
db_cursor = conn.cursor()
db_cursor.execute(
"""
SELECT
i.id,
i.slack_handle,
i.specialty,
i.cohort_id,
u.first_name,
u.last_name
FROM exercisesapp_instructor i
JOIN auth_user u ON i.user_id = u.id
WHERE i.id = ?
""", (instructor_id, ))
return db_cursor.fetchone()
def cohort_list(request):
if request.method == 'GET':
with sqlite3.connect(Connection.db_path) as conn:
conn.row_factory = model_factory(Cohort)
db_cursor = conn.cursor()
db_cursor.execute("""
SELECT
c.id,
c.name
FROM exercisesapp_cohort c
ORDER BY c.name
""")
all_cohorts = db_cursor.fetchall()
template_name = 'cohorts/list.html'
context = {'all_cohorts': all_cohorts}
return render(request, template_name, context)
elif request.method == 'POST':
form_data = request.POST
with sqlite3.connect(Connection.db_path) as conn:
db_cursor = conn.cursor()
db_cursor.execute(
"""
INSERT INTO exercisesapp_cohort
(
name
)
VALUES (?)
""", (form_data['name'], ))
return redirect(reverse('exercisesapp:cohorts'))