In [1]:
import os
In [2]:
os.getcwd()
Out[2]:
'C:\\Users\\Student'
In [3]:
import sqlite3
In [4]:
conn = sqlite3.connect("gradebook.sqlite")
In [5]:
c = conn.cursor()
In [9]:
c.execute("""
SELECT * FROM users
""").fetchall()
Out[9]:
[(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 [ ]:
c.execute
In [10]:
c.execute("""
SELECT * FROM assignments
""").fetchall()
Out[10]:
[(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 [13]:
c.execute("""
PRAGMA table_info(submissions)
""").fetchall()
Out[13]:
[(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 [14]:
# для каждого submission найти оценку с учётом максимума по assignment
In [33]:
c.execute("""
SELECT u_id, fname, lname, final_grade FROM (
    SELECT users.id as u_id, users.first_name as fname, users.last_name as lname, 
    min(submissions.grade, assignments.max_grade) as final_grade 
    FROM submissions 
    JOIN assignments ON assignment_id = assignments.id
    JOIN users ON user_id == users.id
)
""").fetchall()
Out[33]:
[(1, 'Eddard', 'Stark', 6.0),
 (1, 'Eddard', 'Stark', 5.0),
 (1, 'Eddard', 'Stark', None),
 (2, 'Jon', 'Snow', 3.0),
 (2, 'Jon', 'Snow', 2.0),
 (3, 'Arya', 'Stark', 10.0),
 (3, 'Arya', 'Stark', 12),
 (3, 'Arya', 'Stark', 15.0),
 (4, 'Tyrion', 'Lannister', 4.0),
 (4, 'Tyrion', 'Lannister', 5.0),
 (4, 'Tyrion', 'Lannister', 1.0),
 (4, 'Tyrion', 'Lannister', 10),
 (5, 'Cersei', 'Lannister', 15),
 (5, 'Cersei', 'Lannister', 14.0),
 (5, 'Cersei', 'Lannister', 15),
 (6, 'Sansa', 'Stark', None),
 (6, 'Sansa', 'Stark', 10),
 (7, 'Daenerys', 'Targaryen', 7),
 (7, 'Daenerys', 'Targaryen', 8),
 (7, 'Daenerys', 'Targaryen', 10),
 (8, 'Samwell', 'Tarly', 10),
 (8, 'Samwell', 'Tarly', 10.0),
 (10, 'Ygritte', None, 8.0)]
In [34]:
c.execute("""
SELECT u_id, fname, lname, avg(final_grade) FROM (
    SELECT users.id as u_id, users.first_name as fname, users.last_name as lname, 
    min(submissions.grade, assignments.max_grade) as final_grade 
    FROM submissions 
    JOIN assignments ON assignment_id = assignments.id
    JOIN users ON user_id == users.id
)
GROUP BY u_id
""").fetchall()
Out[34]:
[(1, 'Eddard', 'Stark', 5.5),
 (2, 'Jon', 'Snow', 2.5),
 (3, 'Arya', 'Stark', 12.333333333333334),
 (4, 'Tyrion', 'Lannister', 5.0),
 (5, 'Cersei', 'Lannister', 14.666666666666666),
 (6, 'Sansa', 'Stark', 10.0),
 (7, 'Daenerys', 'Targaryen', 8.333333333333334),
 (8, 'Samwell', 'Tarly', 10.0),
 (10, 'Ygritte', None, 8.0)]
In [43]:
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()
Out[43]:
[('Eddard', 'Stark', 11.0),
 ('Jon', 'Snow', 5.0),
 ('Arya', 'Stark', 48.0),
 ('Tyrion', 'Lannister', 20.0),
 ('Cersei', 'Lannister', 55.0),
 ('Sansa', 'Stark', 32.0),
 ('Daenerys', 'Targaryen', 46.0),
 ('Samwell', 'Tarly', 20.0),
 ('Brienne', None, None),
 ('Ygritte', None, 8.0)]
In [45]:
c.execute("""
SELECT users.first_name, users.last_name, sum(grade) as sum_grade
FROM users LEFT JOIN submissions
ON users.id == submissions.user_id
GROUP BY users.id
""").fetchall()
Out[45]:
[('Eddard', 'Stark', 11.0),
 ('Jon', 'Snow', 5.0),
 ('Arya', 'Stark', 48.0),
 ('Tyrion', 'Lannister', 20.0),
 ('Cersei', 'Lannister', 55.0),
 ('Sansa', 'Stark', 32.0),
 ('Daenerys', 'Targaryen', 46.0),
 ('Samwell', 'Tarly', 20.0),
 ('Brienne', None, None),
 ('Ygritte', None, 8.0)]
In [46]:
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()
Out[46]:
[('Eddard', 'Stark'),
 ('Jon', 'Snow'),
 ('Arya', 'Stark'),
 ('Tyrion', 'Lannister'),
 ('Ygritte', None)]
In [47]:
c.execute("""
SELECT courses.id FROM courses WHERE code = "nes-ds"
""").fetchall()
Out[47]:
[(1,), (2,)]
In [54]:
c.execute("""
SELECT * FROM course_user
""")
Out[54]:
<sqlite3.Cursor at 0x141f06a38f0>
In [59]:
c.execute("""
SELECT DISTINCT users.id, users.first_name, users.last_name FROM users
JOIN course_user ON users.id == course_user.user_id
JOIN courses ON courses.id == course_user.course_id
WHERE code == "nes-ds"
""").fetchall()
Out[59]:
[(2, 'Jon', 'Snow'),
 (3, 'Arya', 'Stark'),
 (4, 'Tyrion', 'Lannister'),
 (1, 'Eddard', 'Stark'),
 (10, 'Ygritte', None)]
In [61]:
c.execute("""
SELECT DISTINCT code FROM courses
""").fetchall()
Out[61]:
[('nes-ds',), ('dj-prog',), ('python-all',), ('python-icef',)]
In [62]:
c.execute("""
SELECT code FROM courses
""").fetchall()
Out[62]:
[('nes-ds',), ('nes-ds',), ('dj-prog',), ('python-all',), ('python-icef',)]
In [71]:
c.execute("""
SELECT grade, 
CASE 
    WHEN grade IS NULL THEN NULL
    WHEN grade < 4 THEN "bad"
    WHEN grade < 6 THEN "udovl"
    WHEN grade < 8 THEN "good"
    ELSE "very good"
END
from submissions
""").fetchall()
Out[71]:
[(6.0, 'bad'),
 (5.0, 'bad'),
 (None, None),
 (3.0, 'bad'),
 (2.0, 'bad'),
 (10.0, 'very good'),
 (23.0, 'very good'),
 (15.0, 'very good'),
 (4.0, 'bad'),
 (5.0, 'bad'),
 (1.0, 'bad'),
 (10.0, 'very good'),
 (20.0, 'very good'),
 (14.0, 'very good'),
 (21.0, 'very good'),
 (None, None),
 (32.0, 'very good'),
 (15.0, 'very good'),
 (15.0, 'very good'),
 (16.0, 'very good'),
 (10.0, 'very good'),
 (10.0, 'very good'),
 (8.0, 'very good')]
In [75]:
c.execute("""
SELECT * FROM courses
WHERE code LIKE "p_thon-%"
""").fetchall()
Out[75]:
[(4, 'python-all', 2015, 0), (5, 'python-icef', 2018, 1)]
In [77]:
c.execute("""
SELECT * FROM USERS
""").fetchall()
Out[77]:
[(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 [79]:
c.execute("""
UPDATE users
SET
last_name = "Snow"
WHERE first_name = "Ygritte"
""").fetchall()
Out[79]:
[]
In [80]:
c.execute("""
SELECT * FROM USERS
""").fetchall()
Out[80]:
[(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', 'Snow', '[email protected]')]
In [81]:
conn.commit()
In [82]:
import pandas as pd
In [83]:
df = pd.DataFrame({'x': [1, 2, 3], 'y': [10, 20, 30]})
In [84]:
df
Out[84]:
x y
0 1 10
1 2 20
2 3 30
In [85]:
df.to_sql("newtable", conn)
In [87]:
c.execute("""
SELECT * FROM newtable
""").fetchall()
Out[87]:
[(0, 1, 10), (1, 2, 20), (2, 3, 30)]
In [88]:
conn.commit()
In [ ]: