database.py
4.82 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
import sqlite3
class Database(object):
def __init__(self, db):
self.db = db # sqlite3 filename
def get_count_students(self):
with sqlite3.connect(self.db) as c:
sql = 'SELECT COUNT(*) FROM students'
return c.execute(sql).fetchone()[0]
def reset_password(self, uid):
with sqlite3.connect(self.db) as c:
sql = 'UPDATE students SET password="" WHERE number=?'
c.execute(sql, [uid])
def update_password(self, uid, pw):
# saves pw as is (should be already hashed)
with sqlite3.connect(self.db) as c:
sql = 'UPDATE students SET password=? WHERE number=?'
c.execute(sql, (pw, uid))
def get_student(self, uid):
with sqlite3.connect(self.db) as c:
sql = 'SELECT name,password FROM students WHERE number=?'
try:
name, pw = c.execute(sql, [uid]).fetchone()
except:
return None
else:
return (name, pw)
def get_all_students(self):
with sqlite3.connect(self.db) as c:
sql = 'SELECT number,name,password FROM students ORDER BY number ASC'
students = c.execute(sql).fetchall()
return students
# get all results for a particular test. If a student has submited more than
# one test, returns the highest grade FIXME not tested, not used
# def get_students_grades(self, testid):
# with sqlite3.connect(self.db) as c:
# grades = c.execute('SELECT student_id,MAX(grade) FROM tests WHERE test_id==?', [testid])
# return grades.fetchall()
# get results from previous tests of a student
def get_student_grades_from_all_tests(self, uid):
with sqlite3.connect(self.db) as c:
grades = c.execute('SELECT test_id,grade,finish_time FROM tests WHERE student_id==?', [uid])
return grades.fetchall()
def get_student_grades_from_test(self, uid, testid):
with sqlite3.connect(self.db) as c:
grades = c.execute('SELECT grade,finish_time FROM tests WHERE student_id==? and test_id==?', [uid, testid])
return grades.fetchall()
def save_test(self, t):
with sqlite3.connect(self.db) as c:
# save final grade of the test
sql = 'INSERT INTO tests VALUES (?,?,?,?,?)'
test = (t['ref'], t['student']['number'], t['grade'], str(t['start_time']), str(t['finish_time']))
c.execute(sql, test)
def save_questions(self, t):
with sqlite3.connect(self.db) as c:
# save grades of all the questions (omits questions without grade)
sql = 'INSERT INTO questions VALUES (?,?,?,?,?)'
questions = [(t['ref'], q['ref'], t['student']['number'], q['grade'], str(t['finish_time'])) for q in t['questions'] if 'grade' in q]
c.executemany(sql, questions)
# def insert_student(self, number, name, password=''): # FIXME testar...
# with sqlite3.connect(self.db) as c:
# if password != '':
# password = sha256(password.encode('utf-8')).hexdigest() # FIXME bcrypt
# cmd = 'INSERT INTO students VALUES (?, ?, ?);'
# c.execute(cmd, number, name, password)
# # return list of students and their results for a given test
# def get_test_grades(self, test_id):
# with sqlite3.connect(self.db) as c:
# # with all tests done by each student:
# # cmd = 'SELECT student_id,name,grade FROM students INNER JOIN tests ON students.number=tests.student_id WHERE test_id==? ORDER BY grade DESC;'
# # only the best result for each student
# cmd = '''
# SELECT student_id, name, MAX(grade), finish_time
# FROM students INNER JOIN tests
# ON students.number=tests.student_id
# WHERE test_id==? AND student_id!=0
# GROUP BY student_id
# ORDER BY grade DESC, finish_time DESC;'''
# return c.execute(cmd, [test_id]).fetchall()
# # return list of students and their results for a given test
# def test_grades2(self, test_id):
# with sqlite3.connect(self.db) as c:
# # with all tests done by each student:
# # cmd = 'SELECT student_id,name,grade FROM students INNER JOIN tests ON students.number=tests.student_id WHERE test_id==? ORDER BY grade DESC;'
# # only the best result for each student
# cmd = '''
# SELECT student_id, name, grade, start_time, finish_time
# FROM students INNER JOIN tests
# ON students.number=tests.student_id
# WHERE test_id==?
# ORDER BY finish_time ASC;'''
# return c.execute(cmd, [test_id]).fetchall()
# the following methods update de database data