def get_student_by_id(self, student_identifier):
with SQLite(self.db_name) as cur:
cur.execute("SELECT * FROM students "
"INNER JOIN faculties ON (students.faculty_id = faculties.id) "
"INNER JOIN classes ON (students.class_id = classes.id) "
"WHERE student_identifier = ?", (student_identifier,))
return cur.fetchone()
def _create_classes_table(self):
with SQLite(self.db_name) as cur:
cur.execute("CREATE TABLE if not exists classes("
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
"class_name VARCHAR NOT NULL,"
"faculty_id INTEGER, "
"FOREIGN KEY(faculty_id) REFERENCES faculties(id))")
def add_mark(self, subject, mark, student_id):
self._create_marks_table()
with SQLite(self.db_name) as cur:
cur.execute("INSERT INTO marks("
"subject, mark, student_id)"
"VALUES(?,?,?)",
(subject, mark, student_id))
def create_faculty(*data):
with SQLite('students.db') as cur:
cur.execute("CREATE TABLE if not exists faculties("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR NOT NULL, "
"description VARCHAR)")
cur.execute("INSERT INTO faculties(name, description) VALUES(?,?)", (*data,))
def add_student(self, student_id, student_name, faculty_id, class_id):
self._create_students_table()
with SQLite(self.db_name) as cur:
cur.execute("INSERT INTO students("
"student_identifier, student_name, faculty_id, class_id)"
"VALUES(?,?,?,?)",
(student_id, student_name, faculty_id, class_id))
def _create_marks_table(self):
with SQLite(self.db_name) as cur:
cur.execute("CREATE TABLE if not exists marks("
"mark_id INTEGER PRIMARY KEY AUTOINCREMENT,"
"subject VARCHAR NOT NULL, "
"mark TINYINT NOT NULL,"
"student_id INTEGER,"
"FOREIGN KEY(student_id) REFERENCES students(id))")
def create_class(*data):
with SQLite('students.db') as cur:
cur.execute("CREATE TABLE if not exists classes("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR NOT NULL,"
"faculty_id INTEGER, "
"FOREIGN KEY(faculty_id) REFERENCES faculties(id))")
cur.execute("INSERT INTO classes(name, faculty_id) VALUES(?,?)", (*data,))
def create_curator(*data):
with SQLite('students.db') as cur:
cur.execute("CREATE TABLE if not exists curators("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR NOT NULL, "
"last_name VARCHAR NOT NULL)")
cur.execute("INSERT INTO curators(name, last_name) VALUES(?,?)",
(*data, ))
def create_curators_data(self, count):
self._create_curators_table()
for _ in range(count):
with SQLite('students.db') as cur:
cur.execute(
"INSERT INTO curators(name, last_name) VALUES(?,?)", (
random.choice(self.names),
random.choice(self.last_names),
))
def create_marks_data(self, subject_id):
self._create_table_marks()
for i in self._get_students():
mark = random.randrange(10, 101)
with SQLite('students.db') as cur:
cur.execute(
"INSERT INTO marks("
"mark, subject_id, student_id)"
"VALUES(?,?,?)", (mark, subject_id, i))
def get_excellent_students(self):
with SQLite(self.db_name) as cur:
cur.execute("SELECT students.id, students.student_identifier, "
"students.student_name "
"FROM students "
"INNER JOIN marks ON (students.id = marks.student_id)"
"GROUP BY(students.id)"
"HAVING(AVG(marks.mark) >= 90)")
return cur.fetchall()
def get_all_students_info(self):
with SQLite(self.db_name) as cur:
cur.execute("SELECT students.id, students.student_identifier, students.student_name, "
"faculties.faculty_name, classes.class_name, GROUP_CONCAT(marks.mark) "
"FROM students "
"INNER JOIN faculties ON (students.faculty_id = faculties.id)"
"INNER JOIN classes ON (students.class_id = classes.id)"
"INNER JOIN marks ON (students.id = marks.student_id)"
"GROUP BY(students.id) ")
return cur.fetchall()
def _get_faculties_classes(faculty_id):
with SQLite('students.db') as cur:
cur.execute(
"SELECT GROUP_CONCAT(classes.id) "
"FROM faculties "
"INNER JOIN classes ON(classes.faculty_id = faculties.id) "
"WHERE faculties.id=?", (faculty_id, ))
return [
int(item) for sub in cur.fetchone() for item in sub.split(',')
]
def create_mark(*data):
with SQLite('students.db') as cur:
cur.execute("CREATE TABLE if not exists marks("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"mark TINYINT NOT NULL, "
"subject_id VARCHAR NOT NULL,"
"student_id INTEGER,"
"FOREIGN KEY(subject_id) REFERENCES subjects(id), "
"FOREIGN KEY(student_id) REFERENCES students(id))")
cur.execute("INSERT INTO classes(name, faculty_id) VALUES(?,?)",
(*data, ))
def _create_students_table():
with SQLite('students.db') as cur:
cur.execute("CREATE TABLE if not exists students("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR NOT NULL, "
"last_name VARCHAR NOT NULL, "
"faculty_id INTEGER NOT NULL, "
"class_id INTEGER NOT NULL, "
"curator_id INTEGER NOT NULL, "
"FOREIGN KEY(faculty_id) REFERENCES faculties(id), "
"FOREIGN KEY(class_id) REFERENCES classes(id), "
"FOREIGN KEY(curator_id) REFERENCES curators(id))")
def _create_product_table(self):
with SQLite(self.db_name) as cur:
cur.execute(
"CREATE TABLE if not exists product("
"product_id INTEGER PRIMARY KEY AUTOINCREMENT, "
"product_name VARCHAR NOT NULL, "
"price DECIMAL NOT NULL, "
"in_sale TINYINT NOT NULL, "
"in_stock TINYINT NOT NULL, "
"description TEXT, "
"product_url VARCHAR, "
"category_id INTEGER,"
"FOREIGN KEY(category_id) REFERENCES category(category_id))")
def get_faculty(faculty_id):
with SQLite('students.db') as cur:
cur.execute("SELECT faculties.id, faculties.name, "
"faculties.description, GROUP_CONCAT(classes.id), GROUP_CONCAT(classes.name) "
"FROM faculties "
"INNER JOIN classes ON (classes.faculty_id = faculties.id) "
"WHERE faculties.id = ?", (faculty_id,))
result = dict(zip(faculty_structure, cur.fetchone()))
result['classes_id'] = [int(item) for item in result['classes_id'].split(',')]
result['classes_name'] = result['classes_name'].split(',')
result['classes'] = dict(zip(result['classes_id'], result['classes_name']))
result.pop('classes_id')
result.pop('classes_name')
return result
def get_student(student_id):
with SQLite('students.db') as cur:
cur.execute(
"SELECT students.id, students.name, students.last_name,"
"faculties.id, faculties.name, "
"classes.id, classes.name, "
"curators.id, GROUP_CONCAT(marks.mark) "
"FROM students "
"INNER JOIN faculties ON (students.faculty_id = faculties.id) "
"INNER JOIN classes ON (students.class_id = classes.id)"
"INNER JOIN curators ON (students.curator_id = curators.id)"
"INNER JOIN marks ON (students.id = marks.student_id) "
"WHERE students.id = ?", (student_id, ))
return dict(zip(student_structure, cur.fetchone()))
def create_students_data(self, count):
self._create_students_table()
for _ in range(count):
faculty_id = random.choice(self._get_faculties())
class_id = random.choice(self._get_faculties_classes(faculty_id))
curator_id = random.choice(self._get_curators())
with SQLite('students.db') as cur:
cur.execute(
"INSERT INTO students("
"name, last_name, "
"faculty_id, class_id, curator_id) "
"VALUES(?,?,?,?,?)",
(random.choice(self.names), random.choice(
self.last_names), faculty_id, class_id, curator_id))
def get_class(class_id):
with SQLite('students.db') as cur:
cur.execute("SELECT classes.id, classes.name, classes.faculty_id, "
"GROUP_CONCAT(students.id), GROUP_CONCAT(students.last_name) "
"FROM classes "
"INNER JOIN students ON (students.class_id = classes.id) "
"WHERE classes.id=?", (class_id,))
result = dict(zip(class_structure, cur.fetchone()))
result['students_id'] = [int(item) for item in result['students_id'].split(',')]
result['students_last_name'] = result['students_last_name'].split(',')
result['students'] = dict(zip(result['students_id'], result['students_last_name']))
result.pop('students_id')
result.pop('students_last_name')
return result
def create_student(*data):
with SQLite('students.db') as cur:
cur.execute("CREATE TABLE if not exists students("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"name VARCHAR NOT NULL, "
"last_name VARCHAR NOT NULL, "
"faculty_id INTEGER NOT NULL, "
"class_id INTEGER NOT NULL, "
"curator_id INTEGER NOT NULL, "
"FOREIGN KEY(faculty_id) REFERENCES faculties(id), "
"FOREIGN KEY(class_id) REFERENCES classes(id), "
"FOREIGN KEY(curator_id) REFERENCES curators(id))")
cur.execute(
"INSERT INTO students("
"name, last_name, "
"faculty_id, class_id, curator_id) "
"VALUES(?,?,?,?,?)", (*data, ))
def get_excellent_students():
with SQLite('students.db') as cur:
cur.execute(
"SELECT students.id, students.name, students.last_name,"
"faculties.id, faculties.name, "
"classes.id, classes.name, "
"curators.id, GROUP_CONCAT(marks.mark) "
"FROM students "
"INNER JOIN faculties ON (students.faculty_id = faculties.id) "
"INNER JOIN classes ON (students.class_id = classes.id)"
"INNER JOIN curators ON (students.curator_id = curators.id)"
"INNER JOIN marks ON (students.id = marks.student_id) "
"GROUP BY(students.id)"
"HAVING(AVG(marks.mark) >= 80)")
return [
dict(zip(student_structure, values)) for values in cur.fetchall()
]
def add_product(self,
product_name,
price,
in_sale,
in_stock,
category_id,
description='',
product_url=''):
self._create_product_table()
if not product_url:
product_url = product_name.replace(' ', '-').lower()
args = (product_name, price, in_sale, in_stock, description,
product_url, category_id)
with SQLite(self.db_name) as cur:
cur.execute(
"INSERT INTO product "
"(product_name, price, in_sale, in_stock, description, product_url, category_id)"
"VALUES (?,?,?,?,?,?,?)", args)
def delete_faculty(faculty_id):
with SQLite('students.db') as cur:
cur.execute("DELETE FROM faculties WHERE id=?", (faculty_id,))
def update_faculty(faculty_id, *data):
with SQLite('students.db') as cur:
cur.execute("UPDATE faculties SET name=?, description=? WHERE id=?", (*data, faculty_id))
def get_faculties():
with SQLite('students.db') as cur:
cur.execute("SELECT * FROM faculties")
return [dict(zip(faculties_structure, values)) for values in cur.fetchall()]
def _create_category_table(self):
with SQLite(self.db_name) as cur:
cur.execute("CREATE TABLE if not exists category("
"category_id INTEGER PRIMARY KEY AUTOINCREMENT,"
"category_name VARCHAR NOT NULL)")
def add_category(self, name):
self._create_category_table()
with SQLite(self.db_name) as cur:
cur.execute("INSERT INTO category (category_name) VALUES (?)",
(name, ))
def delete_student(student_id):
with SQLite('students.db') as cur:
cur.execute("DELETE FROM students WHERE id=?", (student_id, ))
return Response(status=200)
def update_student(student_id, *data):
with SQLite('students.db') as cur:
cur.execute(
"UPDATE students SET name=?, last_name=?, faculty_id=?, class_id=?, curator_id=? WHERE id=?",
(*data, student_id))