78 lines
3.5 KiB
Python
78 lines
3.5 KiB
Python
import mysql.connector as sql
|
|
from dataclasses import dataclass
|
|
|
|
HOST = "192.168.0.239"
|
|
USER = "detector"
|
|
PASSWORD = "n9ZvEmZ7xSKE7cPx"
|
|
|
|
@dataclass
|
|
class Person():
|
|
id: int
|
|
name: str
|
|
surname: str
|
|
imagefile: str
|
|
|
|
@dataclass
|
|
class Student(Person):
|
|
pass
|
|
|
|
@dataclass
|
|
class Parent(Person):
|
|
pass
|
|
|
|
class Database:
|
|
db = None
|
|
cursor = None
|
|
def __init__(self) -> None:
|
|
self.db = sql.connect(host=HOST,user =USER,password=PASSWORD,database="face_detect")
|
|
self.cursor = self.db.cursor()
|
|
pass
|
|
def get_student_info(self, filename: str) -> Student:
|
|
statement = (f'SELECT * FROM students WHERE imagefile=\"{filename}\"')
|
|
self.cursor.execute(statement)
|
|
result = self.cursor.fetchone()
|
|
if result == None:
|
|
return None
|
|
student = Student(result[0],result[1],result[2],result[3])
|
|
return student
|
|
def get_parent_info(self, filename: str):
|
|
statement = (f'SELECT * FROM parents WHERE imagefile=\"{filename}\"')
|
|
self.cursor.execute(statement)
|
|
result = self.cursor.fetchone()
|
|
if result == None:
|
|
return None
|
|
parent = Parent(result[0],result[1],result[2],result[3])
|
|
return parent
|
|
def check_relationship_file(self, student_filename: str, parent_filename: str):
|
|
statement = f'SELECT id FROM `parent-student-relations` psr WHERE psr.student_id = (SELECT id FROM students WHERE students.imagefile = \"{student_filename}\") AND psr.parent_id = (SELECT id FROM parents WHERE parents.imagefile = "{parent_filename}")'
|
|
self.cursor.execute(statement)
|
|
return self.cursor.fetchone() != None
|
|
def check_relationship(self, student: Student, parent: Parent):
|
|
statement = f'SELECT id FROM `parent-student-relations` psr WHERE psr.student_id = {student.id} AND psr.parent_id = {parent.id}'
|
|
self.cursor.execute(statement)
|
|
return self.cursor.fetchone() != None
|
|
def log_access_student_file(self, student_filename: str, parent_filename: str):
|
|
statement = f'INSERT INTO face_detect.`access-log` (id, student_id, parent_id, `timestamp`) VALUES(NULL, (SELECT id FROM students WHERE students.imagefile = \"{student_filename}\"), (SELECT id FROM parents WHERE parents.imagefile = "{parent_filename}"), NULL)'
|
|
self.cursor.execute(statement)
|
|
self.db.commit()
|
|
def log_access_parent_file(self, parent_filename: str):
|
|
statement = f'INSERT INTO face_detect.`access-log` (id, student_id, parent_id, `timestamp`) VALUES(NULL, NULL, (SELECT id FROM parents WHERE parents.imagefile = "{parent_filename}"), NULL)'
|
|
self.cursor.execute(statement)
|
|
self.db.commit()
|
|
def log_access_student_with_parent(self, student: Student, parent: Parent):
|
|
statement = f'INSERT INTO face_detect.`access-log` (id, student_id, parent_id, `timestamp`) VALUES(NULL, {student.id}, {parent.id}, NULL)'
|
|
self.cursor.execute(statement)
|
|
self.db.commit()
|
|
def log_access_parent(self, parent: Parent):
|
|
statement = f'INSERT INTO face_detect.`access-log` (id, student_id, parent_id, `timestamp`) VALUES(NULL, NULL, {parent.id}, NULL)'
|
|
self.cursor.execute(statement)
|
|
self.db.commit()
|
|
# def log_access_student(self, parent: Parent):
|
|
# statement = f'INSERT INTO face_detect.`access-log` (id, student_id, parent_id, `timestamp`) VALUES(NULL, {student.id}, NULL, NULL)'
|
|
# self.cursor.execute(statement)
|
|
# self.db.commit()
|
|
|
|
|
|
def fetch_log() -> list:
|
|
pass
|