stm32-fmt-code/access_control_python/database.py

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