def findUser(uname=None, passwd=None):
with connect_db() as conn:
c = conn.cursor()
query = "SELECT * from tb_users WHERE username=? AND password=?"
c.execute(query, (uname, hashPassword(passwd)))
match_lists = c.fetchone()
return match_lists
def change_profile_img(kp_id=None, profile_image=None):
try:
with connect_db() as conn:
c = conn.cursor()
sql = "UPDATE tb_know_people SET profile_image=? WHERE kp_id=?"
c.execute(sql, (profile_image, kp_id))
conn.commit()
return True
except DatabaseError as e:
print(e)
return False
def delete_know_people(kp_id=None):
try:
with connect_db() as conn:
c = conn.cursor()
sql = "DELETE FROM tb_know_people WHERE kp_id=?"
c.execute(sql, (kp_id, ))
conn.commit()
return True
except DatabaseError as e:
print(e)
return False
def update_know_people(kp_id=None, name=None, lname=None, age=None, sex=None):
try:
with connect_db() as conn:
c = conn.cursor()
sql = "UPDATE tb_know_people SET name=?, lname=?, age=?, sex=? WHERE kp_id=?"
c.execute(sql, (name, lname, age, sex, kp_id))
conn.commit()
return True
except DatabaseError as e:
print(e)
return False
def delete_camera(camera_id):
try:
with connect_db() as conn:
c = conn.cursor()
sql = "DELETE FROM tb_camera WHERE camera_id=?"
c.execute(sql, (camera_id, ))
conn.commit()
return True
except Error as e:
print(e)
return False
def delete_user(user_id):
try:
with connect_db() as conn:
c = conn.cursor()
query = "DELETE FROM tb_users WHERE user_id=?"
c.execute(query, (user_id, ))
conn.commit()
return True
except DatabaseError as e:
print(e)
return False
def update_user(user_id=None, uname=None, passwd=None, level=1):
try:
with connect_db() as conn:
c = conn.cursor()
query = "UPDATE tb_users SET username=?, password=?, level=? WHERE user_id=?"
c.execute(query, (uname, hashPassword(passwd), level, user_id))
conn.commit()
return True
except DatabaseError as e:
print(e)
return False
def add_know_people(name=None, lname=None, age=None, sex=None):
try:
with connect_db() as conn:
c = conn.cursor()
sql = "INSERT INTO tb_know_people VALUES(?, ?, ?, ?, ?, NULL)"
kp_id = random_some_string(10)
c.execute(sql, (kp_id, name, lname, age, sex))
conn.commit()
return True
except DatabaseError as e:
print(e)
return False
def insertUser(uname=None, password=None, level=1):
try:
with connect_db() as conn:
c = conn.cursor()
query = "INSERT INTO tb_users VALUES (?, ?, ?, ?)"
user_id = random_some_string()
c.execute(query, (user_id, uname, hashPassword(password), level))
conn.commit()
return True
except DatabaseError as e:
print(e)
return False
def insert_people_access(kp_id, camera_id, full_img_path, face_img_path):
try:
with connect_db() as conn:
c = conn.cursor()
sql = 'INSERT INTO tb_access (kp_id, camera_id, cap_full_image_path, face_image_path, detected_time)' \
' VALUES(?, ?, ?, ?, ?)'
dt = datetime.now(timezone.utc).isoformat()
c.execute(sql, (kp_id, camera_id, full_img_path, face_img_path, dt))
conn.commit()
return True
except Error as e:
print(e)
return False
def add_camera(camera_code=None,
camera_name=None,
room_name=None,
username=None,
password=None,
ip=None,
port=554):
try:
with connect_db() as conn:
c = conn.cursor()
sql = "INSERT INTO tb_camera VALUES(?, ?, ?, ?, ?, ?, ?, ?)"
camera_id = random_some_string(10)
c.execute(sql, (camera_id, camera_code, camera_name, room_name,
username, password, ip, port))
conn.commit()
return True
except Error as e:
print(e)
return False
def update_camera(camera_id=None,
camera_code=None,
camera_name=None,
room_name=None,
username=None,
password=None,
ip=None,
port=554):
try:
with connect_db() as conn:
c = conn.cursor()
sql = "UPDATE tb_camera set cameraCode=?, cameraName=?, roomName=?, userName=?, password=?, ip=?, port=?" \
"WHERE " \
"camera_id=? "
c.execute(sql, (camera_code, camera_name, room_name, username,
password, ip, port, camera_id))
conn.commit()
return True
except Error as e:
print(e)
return False
def list_know_people_by_id(kp_id=None):
with connect_db() as conn:
c = conn.cursor()
sql = "SELECT * FROM tb_know_people WHERE kp_id=?"
c.execute(sql, (kp_id, ))
return c.fetchall()
def list_all_know_people():
with connect_db() as conn:
c = conn.cursor()
sql = "SELECT * FROM tb_know_people"
c.execute(sql)
return c.fetchall()
def query_unknown_people_access(start_date, end_date):
with connect_db() as conn:
c = conn.cursor()
sql = 'SELECT * FROM v_unknown_people_access WHERE detected_time >= ? AND detected_time <= ?'
c.execute(sql, (start_date, end_date))
return c.fetchall()
def find_all_users():
with connect_db() as conn:
c = conn.cursor()
query = "SELECT * from tb_users"
c.execute(query)
return c.fetchall()