diff options
Diffstat (limited to 'Smarker/database.py')
| -rw-r--r-- | Smarker/database.py | 66 |
1 files changed, 62 insertions, 4 deletions
diff --git a/Smarker/database.py b/Smarker/database.py index a0e3640..f17c285 100644 --- a/Smarker/database.py +++ b/Smarker/database.py @@ -1,5 +1,6 @@ from dataclasses import dataclass import pymysql +import yaml @dataclass class SmarkerDatabase: @@ -12,7 +13,8 @@ class SmarkerDatabase: def __enter__(self): try: self.__connection = self.__get_connection() - except pymysql.err.OperationalError as e: + except Exception as e: + print(e.args[1]) if e.args[0] == 1049: self.__build_db() return self @@ -62,7 +64,7 @@ class SmarkerDatabase: student_no VARCHAR(10) NOT NULL, assessment_name VARCHAR(30) NOT NULL, submission_dt DATETIME NOT NULL default CURRENT_TIMESTAMP, - submission_zip_path TEXT NOT NULL, + report_yaml TEXT NOT NULL, FOREIGN KEY (student_no) REFERENCES students(student_no), FOREIGN KEY (assessment_name) REFERENCES assessment(assessment_name) ); @@ -79,14 +81,13 @@ class SmarkerDatabase: CREATE TABLE submitted_files( submission_id INT UNSIGNED NOT NULL, file_id INT UNSIGNED NOT NULL, - file_text TEXT NOT NULL, + file_text TEXT NULL, FOREIGN KEY (submission_id) REFERENCES submissions(submission_id), FOREIGN KEY (file_id) REFERENCES assessment_file(file_id), PRIMARY KEY(submission_id, file_id) ); """) - self.__connection.commit() return self.__connection @@ -95,3 +96,60 @@ class SmarkerDatabase: with self.__connection.cursor() as cursor: cursor.execute("SHOW TABLES;") return cursor.fetchall() + + def create_assessment(self, name, yaml_f, num_enrolled, files): + with self.__connection.cursor() as cursor: + cursor.execute("INSERT INTO assessment VALUES (%s, %s, %s);", ( + name, yaml_f, num_enrolled + )) + + for file_ in files: + cursor.execute("INSERT INTO assessment_file (assessment_name, file_name) VALUES (%s, %s);", ( + name, file_ + )) + self.__connection.commit() + + def remove_assessment(self, name): + with self.__connection.cursor() as cursor: + cursor.execute("DELETE FROM assessment_file WHERE assessment_name = %s;", (name, )) + cursor.execute("DELETE FROM assessment WHERE assessment_name = %s;", (name, )) + self.__connection.commit() + + def get_assessments(self): + with self.__connection.cursor() as cursor: + cursor.execute(""" + SELECT assessment.assessment_name, num_enrolled, COUNT(assessment.assessment_name) + FROM assessment + INNER JOIN assessment_file + ON assessment.assessment_name = assessment_file.assessment_name + GROUP BY assessment.assessment_name; + """) + return cursor.fetchall() + + def get_assessment_yaml(self, name): + with self.__connection.cursor() as cursor: + cursor.execute("SELECT yaml_path FROM assessment WHERE assessment_name = %s;", (name, )) + return yaml.safe_load(cursor.fetchone()[0]) + + def add_student(self, student_id, name, email): + with self.__connection.cursor() as cursor: + cursor.execute("INSERT INTO students VALUES (%s, %s, %s);", + (student_id, name, email)) + self.__connection.commit() + + def add_submission(self, student_id, assessment_name, report_yaml, files): + with self.__connection.cursor() as cursor: + cursor.execute("INSERT INTO submissions (student_no, assessment_name, report_yaml) VALUES (%s, %s, %s);", ( + student_id, assessment_name, report_yaml + )) + submission_id = cursor.lastrowid + + for file_name, file_contents in files: + cursor.execute(""" + INSERT INTO submitted_files + (submission_id, file_id, file_text) + VALUES (%s, (SELECT file_id FROM assessment_file WHERE file_name = %s), %s); + """, ( + submission_id, file_name, file_contents + )) + |