def foreign_key_constraint(cursor: Cursor):
''' Turn on foreign key constraints
Args:
cursor (Cursor): sqlite3 Cursor object
'''
cursor.execute(___('PRAGMA foreign_keys = ON;'))
def plot_batch_performance(cursor: Cursor, exam: int) -> None:
''' Plot the performance of all students in a particular exam
Args:
cursor (Cursor): sqlite3 Cursor object
exam (int): uid of the exam concerned
'''
cursor.execute(___(f'SELECT student,marks FROM marks WHERE exam={exam}'))
marks_list = cursor.fetchall()
if not marks_list:
logger.warning(f'No marks entries exist for exam with uid = {exam}')
return
x_axis = [f'{i[0]}' for i in marks_list]
y_axis = [i[1] for i in marks_list]
plt.stem(x_axis, y_axis)
logger.info(f'Plotting stem graph with \nx = {x_axis} \n\ny = {y_axis}')
plt.xlabel('Students')
plt.ylabel('Marks')
plt.show()
def delete(self, **conds) -> None:
''' Deletes the entries satisfying given condtions
'''
cond_str = gen_kv_str(conds)
self.cursor.execute(
___(f'''DELETE FROM {self.table}
WHERE {cond_str} '''))
def analyse_exam(cursor: Cursor, exam_id: int) -> dict:
''' Gives all stats about the exam
Args:
cursor (Cursor): sqlite3 Cursor object
exam_id (int): the unique id of the exam
Returns:
dict: all the stats
'''
logger.info('Analyzing exam with id ')
cursor.execute(___(f'SELECT AVG(marks) FROM marks WHERE exam={exam_id}'))
average = cursor.fetchone()[0]
cursor.execute(___(f'SELECT MAX(marks) FROM marks WHERE exam={exam_id}'))
highest = cursor.fetchone()[0]
return {'average': average, 'highest': highest}
def get_exam_name(self) -> str:
''' Fetches the name of the exam of the current Mailer object
Returns:
[str]: name of exam
'''
self.cursor.execute(
___(f'SELECT name FROM exams WHERE uid={self.exam_uid}'))
exam_name = self.cursor.fetchone()[0]
return exam_name
def insert(self, values: tuple) -> None:
''' Inserts the values provided in the table
Args:
values (tuple): values to insert
'''
self.cursor.execute(
___(f'''INSERT INTO {self.table}
VALUES{values} '''))
logger.info(f'{values} were inserted using {self}')
def fetch(self, **conds) -> list:
''' Fetches a list of results based on condition parameters
Returns:
list: [description]
'''
cond_str = gen_kv_str(conds)
self.cursor.execute(
___(f'''SELECT * FROM {self.table}
{'WHERE' if cond_str else ''} {cond_str}'''))
return self.cursor.fetchall()
def query(self, query_string: str):
''' Execute a query using the Cursor and return all results
Args:
query_string (str): the query to execute
Returns:
list: results
'''
self.cursor.execute(___(query_string))
return self.cursor.fetchall()
def update(self, set_dict: dict, **conds) -> None:
''' Updates the table using set_string, based on given conditions
Args:
set_string (str): the string to put beside SET keyword in SQL
'''
set_string = gen_kv_str(set_dict, delim=',')
cond_str = gen_kv_str(conds)
self.cursor.execute(
___(f'''UPDATE {self.table}
SET {set_string}
WHERE {cond_str} '''))
def mail_all_students(self):
''' Method to loop over all students marks entries for the exam and send them emails.
Updates the self.recipients and calls self.send_mail in every iteration.
'''
self.cursor.execute(
___(f'SELECT student,marks FROM marks WHERE exam={self.exam_uid} ORDER BY marks DESC'))
students_roll_list = self.cursor.fetchall()
os.makedirs('temp', exist_ok=True)
rank = 1
for roll_marks in track(students_roll_list, description='Sending emails'):
self.cursor.execute(
___(f'SELECT * FROM students WHERE roll={roll_marks[0]}'))
student = self.cursor.fetchone()
self.recipient['roll'] = student[0]
self.recipient['name'] = student[1]
self.recipient['email'] = student[2]
self.recipient['rank'] = rank
self.recipient['marks'] = roll_marks[1]
self.send_mail()
rank += 1
def exists(self, **conds) -> tuple or None:
''' Checks whether an entry exists, based on given condition
Returns:
tuple or None: returns the tuple containing the entity if exists
else None
'''
cond_str = gen_kv_str(conds)
self.cursor.execute(
___(f'''SELECT *
FROM {self.table}
WHERE {cond_str}'''))
return self.cursor.fetchone()
def create_tables(cursor: Cursor):
''' Create all tables required by marksman
Args:
cursor (Cursor): a sqlite3 Cursor object
'''
logger.info('Starting creation of tables')
try:
cursor.executescript(
___('''
DROP TABLE IF EXISTS students ;
CREATE TABLE students(
roll INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL
);
DROP TABLE IF EXISTS exams ;
CREATE TABLE exams(
uid INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL
);
DROP TABLE IF EXISTS marks ;
CREATE TABLE marks(
student INTEGER NOT NULL,
exam INTEGER NOT NULL,
marks INTEGER NOT NULL,
FOREIGN KEY (student)
REFERENCES students(roll)
ON DELETE CASCADE,
FOREIGN KEY (exam)
REFERENCES exams(uid)
ON DELETE CASCADE,
PRIMARY KEY (student,exam)
);
'''))
except Exception as err:
logger.warning(
'Failed to create tables. Delete the database file and try again')
logger.exception(err)
else:
logger.info('Successfully created tables')
def plot_student_performance(cursor: Cursor,
roll: int,
exam: int,
analysis: dict,
path='') -> None:
''' Plots students performance against the batch
Args:
cursor (Cursor): sqlite3 Cursor object
roll (int): roll no of student
exam_id (int): unique id of exam
'''
cursor.execute(
___(f'SELECT marks FROM marks WHERE exam={exam} AND student={roll} '))
marks = cursor.fetchone()
if not marks:
logger.warning(f'''Marks entry does not exist ...
for exam with uid = {exam} and student with roll = {roll} ''')
return
x_axis = ['student', 'highest', 'average']
y_axis = [marks[0], analysis.get('highest'), analysis.get('average')]
plt.bar(x_axis, y_axis, width=0.4)
logger.info(
f'Plottng horizontal bar grapth with \nx = {x_axis} \n\ny = {y_axis}')
for index, value in enumerate(y_axis):
plt.text(value, index, str(value))
plt.xlabel('Comparison')
plt.ylabel('Marks')
if path:
plt.savefig(path)
plt.close()
else:
plt.show()