def get_grades_by_student_id(self, student_id):
sql = "SELECT * FROM grades WHERE student_id = ?"
with cm.MyDBManager(self.db) as db:
result = db.execute(sql, [student_id]).fetchone()
if result:
return dict(zip(result.keys(), tuple(result)))
else:
return False
def get_all_grades(self):
sql = "SELECT * FROM grades"
with cm.MyDBManager(self.db) as db:
result = db.execute(sql).fetchall()
rows_list = []
for row in result:
rows_list.append(dict(zip(row.keys(), tuple(row))))
return rows_list
def get_faculty_by_id(self, id):
sql = "SELECT * FROM faculty WHERE id = ?"
with cm.MyDBManager(self.db) as db:
result = db.execute(sql, [id]).fetchone()
if result:
return dict(zip(result.keys(), tuple(result)))
else:
return False
def update_faculty(self, id, faculty_data):
sql = "UPDATE faculty SET faculty = ? WHERE id = ?"
try:
with cm.MyDBManager(self.db, "w") as db:
db.executemany(sql, [(faculty_data['faculty'], id)])
except (TypeError, KeyError):
return False
return True
def update_tutor(self, id, tutor_data):
sql = "UPDATE tutors SET tutor = ? WHERE id = ?"
try:
with cm.MyDBManager(self.db, "w") as db:
db.executemany(sql, [(tutor_data['tutor'], id)])
except (TypeError, KeyError):
return False
return True
def add_faculties(self, list_of_dict):
sql = "INSERT INTO faculty ('faculty') VALUES (?)"
for record in list_of_dict:
try:
with cm.MyDBManager(self.db, "w") as db:
db.execute(sql, [record['faculty']])
except TypeError:
return False
return True
def update_grade(self, grade_id, grade_data):
sql = "UPDATE grades SET grade = ?, subject = ?, student_id = ? WHERE id = ?"
try:
with cm.MyDBManager(self.db, "w") as db:
db.executemany(sql,
[(grade_data['grade'], grade_data['subject'],
grade_data['student_id'], grade_id)])
except (TypeError, KeyError):
return False
return True
def add_grades(self, list_of_dict):
sql = "INSERT INTO grades ('grade', 'subject', 'student_id') VALUES (?,?,?)"
for record in list_of_dict:
try:
with cm.MyDBManager(self.db, "w") as db:
db.executemany(sql, [(record['grade'], record['subject'],
record['student_id'])])
except TypeError:
return False
return True
def fill_students_table(self, num_of_records):
names = [str(self.names_data[x][0].value) for x in range(1, self.names_data.max_row)]
surnames = [str(self.names_data[x][1].value) for x in range(1, self.names_data.max_row)]
faculties_id = self.define_existent_data(self.sql_faculty)
tutors_id = self.define_existent_data(self.sql_tutors)
with cm.MyDBManager(self.db, "w") as db:
for record in range(num_of_records):
db.executemany(self.sql_insert_students, [(choice(names), choice(surnames), choice(self.groups),
choice(faculties_id), choice(tutors_id))])
def get_all_students(self):
sql = "SELECT st.id, st.name, st.surname, st.group_num, f.faculty, t.tutor " \
"FROM students as st " \
"INNER JOIN faculty as f ON st.faculty_id=f.id " \
"INNER JOIN tutors as t ON st.tutor_id=t.id"
with cm.MyDBManager(self.db) as db:
result = db.execute(sql).fetchall()
rows_list = []
for row in result:
rows_list.append(dict(zip(row.keys(), tuple(row))))
return rows_list
def get_excellent_students_by_faculty_id(self, faculty_id):
sql = "SELECT st.id, st.name, st.surname, st.faculty_id, st.group_num, st.tutor_id " \
"FROM students as st " \
"INNER JOIN grades as gr ON st.id=gr.id " \
"WHERE gr.grade=10 AND st.faculty_id=?"
with cm.MyDBManager(self.db) as db:
result = db.execute(sql, [faculty_id]).fetchall()
rows_list = []
for row in result:
rows_list.append(dict(zip(row.keys(), tuple(row))))
return rows_list
def delete_student(self, student_id):
sql_select = "SELECT * FROM students WHERE id = ?"
sql_delete = "DELETE FROM students WHERE id = ?"
with cm.MyDBManager(self.db, "w") as db:
try:
if db.execute(sql_select, [student_id]).fetchall():
db.execute(sql_delete, [student_id]).fetchall()
return True
else:
return False
except (TypeError, KeyError):
return False
def get_student_by_id(self, student_id):
sql = "SELECT st.id, st.name, st.surname, st.group_num, f.faculty, t.tutor " \
"FROM students as st " \
"INNER JOIN faculty as f ON st.faculty_id=f.id " \
"INNER JOIN tutors as t ON st.tutor_id=t.id" \
" WHERE st.id=?"
with cm.MyDBManager(self.db) as db:
result = db.execute(sql, [student_id]).fetchone()
if result:
return dict(zip(result.keys(), tuple(result)))
else:
return False
def update_student(self, student_id, student_data):
sql = "UPDATE students SET name = ?, surname = ?, group_num = ?, faculty_id = ?, tutor_id = ? WHERE id = ?"
try:
with cm.MyDBManager(self.db, "w") as db:
faculty_id = list(
db.execute(self.sql_faculty,
[student_data['faculty']]).fetchone())[0]
tutor_id = list(
db.execute(self.sql_tutor,
[student_data['tutor']]).fetchone())[0]
db.executemany(sql,
[(student_data['name'], student_data['surname'],
student_data['group_num'], faculty_id,
tutor_id, student_id)])
except (TypeError, KeyError):
return False
return True
def add_students(self, list_of_dict):
sql = "INSERT INTO students ('name', 'surname', 'group_num', 'faculty_id', 'tutor_id') VALUES (?,?,?,?,?)"
for record in list_of_dict:
try:
with cm.MyDBManager(self.db, "w") as db:
faculty_id = list(
db.execute(self.sql_faculty,
[record['faculty']]).fetchone())[0]
tutor_id = list(
db.execute(self.sql_tutor,
[record['tutor']]).fetchone())[0]
db.executemany(
sql, [(record['name'], record['surname'],
record['group_num'], faculty_id, tutor_id)])
except (TypeError, KeyError):
return False
return True
def define_existent_data(self, sql):
with cm.MyDBManager(self.db) as db:
result = db.execute(sql).fetchall()
id_list = [x[0] for x in result]
return id_list
