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
|
from dataclasses import dataclass
import pymysql
@dataclass
class SmarkerDatabase:
host:str
user:str
passwd:str
db:str
port:int = 3306
def __enter__(self):
try:
self.__connection = self.__get_connection()
except pymysql.err.OperationalError as e:
if e.args[0] == 1049:
self.__build_db()
return self
def __exit__(self, type, value, traceback):
self.__connection.close()
def __get_connection(self):
return pymysql.connect(
host = self.host,
port = self.port,
user = self.user,
passwd = self.passwd,
charset = "utf8mb4",
database = self.db
)
def __build_db(self):
self.__connection = pymysql.connect(
host = self.host,
port = self.port,
user = self.user,
passwd = self.passwd,
charset = "utf8mb4",
)
with self.__connection.cursor() as cursor:
# unsafe:
cursor.execute("CREATE DATABASE %s" % self.db)
cursor.execute("USE %s" % self.db)
cursor.execute("""
CREATE TABLE students(
student_no VARCHAR(10) PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
email VARCHAR(50) NOT NULL
);
""")
cursor.execute("""
CREATE TABLE assessment(
assessment_name VARCHAR(30) PRIMARY KEY NOT NULL,
yaml_path TEXT NOT NULL,
num_enrolled INT UNSIGNED NULL
);
""")
cursor.execute("""
CREATE TABLE submissions(
submission_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
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,
FOREIGN KEY (student_no) REFERENCES students(student_no),
FOREIGN KEY (assessment_name) REFERENCES assessment(assessment_name)
);
""")
cursor.execute("""
CREATE TABLE assessment_file(
file_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
assessment_name VARCHAR(30) NOT NULL,
file_name VARCHAR(30) NOT NULL,
FOREIGN KEY (assessment_name) REFERENCES assessment(assessment_name)
);
""")
cursor.execute("""
CREATE TABLE submitted_files(
submission_id INT UNSIGNED NOT NULL,
file_id INT UNSIGNED NOT NULL,
file_text TEXT NOT 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
def get_tables(self):
with self.__connection.cursor() as cursor:
cursor.execute("SHOW TABLES;")
return cursor.fetchall()
|
