import sqlite3
import requests
r = requests.get("http://math-info.hse.ru/f/2018-19/nes-ds/gradebook.sqlite")
with open("gradebook.sqlite", "wb") as f:
f.write(r.content)
conn = sqlite3.connect("db/gradebook.sqlite")
c = conn.cursor()
c.execute(
"""
SELECT users.first_name, avg(grade)
FROM users LEFT JOIN submissions ON users.id = submissions.user_id
GROUP BY users.id
"""
).fetchall()
[('Eddard', 5.5), ('Jon', 2.5), ('Arya', 16.0), ('Tyrion', 5.0), ('Cersei', 18.333333333333332), ('Sansa', 32.0), ('Daenerys', 15.333333333333334), ('Samwell', 10.0), ('Brienne', None), ('Ygritte', 8.0)]
c.execute(
"""
SELECT u_id, fn, ln, sum(gr)
FROM (
SELECT users.id as u_id, users.first_name as fn, users.last_name as ln, submissions.grade, assignments.max_grade, min(submissions.grade, assignments.max_grade) as gr
FROM users LEFT JOIN submissions ON users.id = submissions.user_id
JOIN assignments ON submissions.assignment_id == assignments.id
)
GROUP BY u_id
"""
).fetchall()
[(1, 'Eddard', 'Stark', 11.0), (2, 'Jon', 'Snow', 5.0), (3, 'Arya', 'Stark', 37.0), (4, 'Tyrion', 'Lannister', 20.0), (5, 'Cersei', 'Lannister', 44.0), (6, 'Sansa', 'Stark', 10), (7, 'Daenerys', 'Targaryen', 25), (8, 'Samwell', 'Tarly', 20.0), (10, 'Ygritte', None, 8.0)]
%%timeit
c.execute(
"""
SELECT users.first_name, users.last_name, (
SELECT sum(grade) FROM submissions WHERE submissions.user_id == users.id
) sum_grade
FROM users
"""
).fetchall()
50.1 µs ± 2.12 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)
%%timeit
c.execute(
"""
SELECT users.first_name, users.last_name, sum(grade)
FROM users LEFT JOIN submissions ON users.id == submissions.user_id
GROUP BY users.id
"""
).fetchall()
71.5 µs ± 10.1 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)
c.execute("""
PRAGMA table_info(courses)
""").fetchall()
[(0, 'id', 'INTEGER', 0, None, 0), (1, 'code', 'TEXT', 0, None, 0), (2, 'year', 'INTEGER', 0, None, 0), (3, 'active', 'INTEGER', 0, None, 0)]
%%timeit
c.execute("""
SELECT users.first_name, users.last_name FROM users
WHERE users.id IN (
SELECT course_user.user_id FROM course_user WHERE course_user.course_id IN
(
SELECT courses.id FROM courses WHERE code == "nes-ds"
)
)
"""
).fetchall()
47 µs ± 6.38 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)
%%timeit
c.execute("""
SELECT DISTINCT users.first_name, users.last_name FROM users JOIN course_user
ON users.id == course_user.user_id JOIN courses ON course_user.course_id == courses.id
WHERE courses.code == "nes-ds"
"""
).fetchall()
59.3 µs ± 377 ns per loop (mean ± std. dev. of 7 runs, 10000 loops each)