In [1]:
import requests
import sqlite3
In [2]:
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)
In [4]:
conn = sqlite3.connect("gradebook.sqlite")
In [5]:
c = conn.cursor()
In [10]:
c.execute("""
SELECT * FROM sqlite_master
""").fetchall()
Out[10]:
[('table',
  'courses',
  'courses',
  2,
  'CREATE TABLE "courses" (\n"id" INTEGER,\n  "code" TEXT,\n  "year" INTEGER,\n  "active" INTEGER\n)'),
 ('table',
  'submissions',
  'submissions',
  4,
  'CREATE TABLE "submissions" (\n"id" INTEGER,\n  "assignment_id" INTEGER,\n  "user_id" INTEGER,\n  "grade" REAL\n)'),
 ('table',
  'users',
  'users',
  5,
  'CREATE TABLE "users" (\n"id" INTEGER,\n  "first_name" TEXT,\n  "last_name" TEXT,\n  "email" TEXT\n)'),
 ('table',
  'course_user',
  'course_user',
  6,
  'CREATE TABLE "course_user" (\n"course_id" INTEGER,\n  "user_id" INTEGER\n)'),
 ('table',
  'assignments',
  'assignments',
  7,
  'CREATE TABLE "assignments" (\n"id" INTEGER,\n  "course_id" INTEGER,\n  "code" TEXT,\n  "name" TEXT,\n  "max_grade" INTEGER\n)')]
In [13]:
c.execute("""
PRAGMA table_info(courses)
""").fetchall()
Out[13]:
[(0, 'id', 'INTEGER', 0, None, 0),
 (1, 'code', 'TEXT', 0, None, 0),
 (2, 'year', 'INTEGER', 0, None, 0),
 (3, 'active', 'INTEGER', 0, None, 0)]
In [14]:
c.execute("""
SELECT * FROM courses
""").fetchall()
Out[14]:
[(1, 'nes-ds', 2017, 0),
 (2, 'nes-ds', 2018, 1),
 (3, 'dj-prog', 2018, 1),
 (4, 'python-all', 2015, 0),
 (5, 'python-icef', 2018, 1)]
In [16]:
c.execute("""
SELECT * FROM users
""").fetchall()
Out[16]:
[(1, 'Eddard', 'Stark', '[email protected]'),
 (2, 'Jon', 'Snow', '[email protected]'),
 (3, 'Arya', 'Stark', '[email protected]'),
 (4, 'Tyrion', 'Lannister', '[email protected]'),
 (5, 'Cersei', 'Lannister', '[email protected]'),
 (6, 'Sansa', 'Stark', '[email protected]'),
 (7, 'Daenerys', 'Targaryen', '[email protected]'),
 (8, 'Samwell', 'Tarly', '[email protected]'),
 (9, 'Brienne', None, '[email protected]'),
 (10, 'Ygritte', None, '[email protected]')]
In [18]:
c.execute("""
SELECT * FROM submissions
WHERE user_id == 3
""").fetchall()
Out[18]:
[(6, 4, 3, 10.0), (7, 2, 3, 23.0), (8, 4, 3, 15.0)]
In [19]:
c.execute("""
PRAGMA table_info(submissions)
""").fetchall()
Out[19]:
[(0, 'id', 'INTEGER', 0, None, 0),
 (1, 'assignment_id', 'INTEGER', 0, None, 0),
 (2, 'user_id', 'INTEGER', 0, None, 0),
 (3, 'grade', 'REAL', 0, None, 0)]
In [21]:
c.execute("""
SELECT users.first_name, users.last_name, submissions.grade FROM users
JOIN submissions ON submissions.user_id = users.id
""").fetchall()
Out[21]:
[('Eddard', 'Stark', None),
 ('Eddard', 'Stark', 5.0),
 ('Eddard', 'Stark', 6.0),
 ('Jon', 'Snow', 2.0),
 ('Jon', 'Snow', 3.0),
 ('Arya', 'Stark', 10.0),
 ('Arya', 'Stark', 15.0),
 ('Arya', 'Stark', 23.0),
 ('Tyrion', 'Lannister', 1.0),
 ('Tyrion', 'Lannister', 4.0),
 ('Tyrion', 'Lannister', 5.0),
 ('Tyrion', 'Lannister', 10.0),
 ('Cersei', 'Lannister', 14.0),
 ('Cersei', 'Lannister', 20.0),
 ('Cersei', 'Lannister', 21.0),
 ('Sansa', 'Stark', None),
 ('Sansa', 'Stark', 32.0),
 ('Daenerys', 'Targaryen', 15.0),
 ('Daenerys', 'Targaryen', 15.0),
 ('Daenerys', 'Targaryen', 16.0),
 ('Samwell', 'Tarly', 10.0),
 ('Samwell', 'Tarly', 10.0),
 ('Ygritte', None, 8.0)]
In [27]:
c.execute("""
SELECT u.id, s.id, first_name, last_name, grade FROM users as u
JOIN submissions as s 
ON s.user_id = u.id
""").fetchall()
Out[27]:
[(1, 1, 'Eddard', 'Stark', 6.0),
 (1, 2, 'Eddard', 'Stark', 5.0),
 (1, 3, 'Eddard', 'Stark', None),
 (2, 4, 'Jon', 'Snow', 3.0),
 (2, 5, 'Jon', 'Snow', 2.0),
 (3, 6, 'Arya', 'Stark', 10.0),
 (3, 7, 'Arya', 'Stark', 23.0),
 (3, 8, 'Arya', 'Stark', 15.0),
 (4, 9, 'Tyrion', 'Lannister', 4.0),
 (4, 10, 'Tyrion', 'Lannister', 5.0),
 (4, 11, 'Tyrion', 'Lannister', 1.0),
 (4, 12, 'Tyrion', 'Lannister', 10.0),
 (5, 13, 'Cersei', 'Lannister', 20.0),
 (5, 14, 'Cersei', 'Lannister', 14.0),
 (5, 15, 'Cersei', 'Lannister', 21.0),
 (6, 16, 'Sansa', 'Stark', None),
 (6, 17, 'Sansa', 'Stark', 32.0),
 (7, 18, 'Daenerys', 'Targaryen', 15.0),
 (7, 19, 'Daenerys', 'Targaryen', 15.0),
 (7, 20, 'Daenerys', 'Targaryen', 16.0),
 (8, 21, 'Samwell', 'Tarly', 10.0),
 (8, 22, 'Samwell', 'Tarly', 10.0),
 (10, 23, 'Ygritte', None, 8.0)]
In [30]:
c.execute("""
SELECT u.id, s.id, first_name, last_name, grade FROM users as u
LEFT OUTER JOIN submissions as s 
ON s.user_id = u.id
""").fetchall()
Out[30]:
[(1, 1, 'Eddard', 'Stark', 6.0),
 (1, 2, 'Eddard', 'Stark', 5.0),
 (1, 3, 'Eddard', 'Stark', None),
 (2, 4, 'Jon', 'Snow', 3.0),
 (2, 5, 'Jon', 'Snow', 2.0),
 (3, 6, 'Arya', 'Stark', 10.0),
 (3, 7, 'Arya', 'Stark', 23.0),
 (3, 8, 'Arya', 'Stark', 15.0),
 (4, 9, 'Tyrion', 'Lannister', 4.0),
 (4, 10, 'Tyrion', 'Lannister', 5.0),
 (4, 11, 'Tyrion', 'Lannister', 1.0),
 (4, 12, 'Tyrion', 'Lannister', 10.0),
 (5, 13, 'Cersei', 'Lannister', 20.0),
 (5, 14, 'Cersei', 'Lannister', 14.0),
 (5, 15, 'Cersei', 'Lannister', 21.0),
 (6, 16, 'Sansa', 'Stark', None),
 (6, 17, 'Sansa', 'Stark', 32.0),
 (7, 18, 'Daenerys', 'Targaryen', 15.0),
 (7, 19, 'Daenerys', 'Targaryen', 15.0),
 (7, 20, 'Daenerys', 'Targaryen', 16.0),
 (8, 21, 'Samwell', 'Tarly', 10.0),
 (8, 22, 'Samwell', 'Tarly', 10.0),
 (9, None, 'Brienne', None, None),
 (10, 23, 'Ygritte', None, 8.0)]
In [34]:
c.execute("""
SELECT * FROM assignments
""").fetchall()
Out[34]:
[(1, 1, 'ps01', 'Intro to Python', 10),
 (2, 2, 'ps01', 'Intro', 12),
 (3, 2, 'ps02', 'Functions', 8),
 (4, 1, 'ps01', 'Python, part 1', 22),
 (5, 2, 'ps03', 'Pandas', 10),
 (6, 3, 'ps03', 'Classes', 15),
 (7, 2, 'ps04', 'Advanced Python', 32),
 (8, 3, 'ps01', 'Introduction', 10),
 (9, 4, 'ps01', 'First class', 7),
 (10, 5, 'ps01', 'Welcome to Python', 8)]
In [37]:
%%timeit
c.execute("""
SELECT * FROM courses, assignments
WHERE courses.id = assignments.course_id
""").fetchall()
119 µs ± 698 ns per loop (mean ± std. dev. of 7 runs, 10000 loops each)
In [38]:
%%timeit
c.execute("""
SELECT * FROM courses JOIN assignments
ON courses.id = assignments.course_id
""").fetchall()
118 µs ± 538 ns per loop (mean ± std. dev. of 7 runs, 10000 loops each)
In [42]:
c.execute("""
SELECT users.first_name, users.last_name, courses.code, courses.year FROM
users JOIN course_user ON users.id = course_user.user_id
JOIN courses ON course_user.course_id = courses.id
""").fetchall()
Out[42]:
[('Eddard', 'Stark', 'nes-ds', 2018),
 ('Eddard', 'Stark', 'nes-ds', 2018),
 ('Jon', 'Snow', 'nes-ds', 2017),
 ('Jon', 'Snow', 'dj-prog', 2018),
 ('Jon', 'Snow', 'python-all', 2015),
 ('Jon', 'Snow', 'python-all', 2015),
 ('Arya', 'Stark', 'nes-ds', 2017),
 ('Arya', 'Stark', 'dj-prog', 2018),
 ('Tyrion', 'Lannister', 'nes-ds', 2017),
 ('Tyrion', 'Lannister', 'dj-prog', 2018),
 ('Tyrion', 'Lannister', 'python-icef', 2018),
 ('Tyrion', 'Lannister', 'python-icef', 2018),
 ('Cersei', 'Lannister', 'dj-prog', 2018),
 ('Daenerys', 'Targaryen', 'dj-prog', 2018),
 ('Samwell', 'Tarly', 'python-all', 2015),
 ('Ygritte', None, 'nes-ds', 2018)]
In [ ]: