initial commit, shows data

1 files changed, 40 insertions, 0 deletions

diff --git a/src/db_handler.py b/src/db_handler.py
new file mode 100644
index 0000000..576612a
--- /dev/null
+++ b/src/db_handler.py
@@ -0,0 +1,40 @@
+import os
+
+import psycopg
+import yaml
+
+with open(os.path.join('configs', 'database.yml'), 'r') as file:
+    db_con_params = yaml.safe_load(file.read())
+
+
+def get_latest_login_attempts() -> (list[dict], list[str]):
+    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
+        with conn.cursor() as cur:
+            cur.execute("""
+                SELECT login_attempt.id, username, password, ip, login_attempt.timestamp 
+                FROM login_attempt 
+                JOIN connection on connection.id = login_attempt.connection 
+                ORDER BY login_attempt.id desc limit 20;
+                """)
+
+            login_attempts = cur.fetchall()
+            col_names = [desc[0] for desc in cur.description]
+            return login_attempts, col_names
+
+
+def get_top(column: str) -> (list[dict], list[str]):
+    if column not in ['username', 'password']:
+        raise ValueError(f'{column} is not allowed')
+    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
+        with conn.cursor() as cur:
+            cur.execute(psycopg.sql.SQL("""
+                SELECT {column}, COUNT({column}) 
+                FROM login_attempt 
+                GROUP BY {column}
+                ORDER BY COUNT({column}) DESC 
+                LIMIT 20;
+                """).format(column=psycopg.sql.Identifier(column),))
+
+            top_usernames = cur.fetchall()
+            col_names = [desc[0] for desc in cur.description]
+            return top_usernames, col_names