From 604fed77fb7a75bb39cda96b2f06438ce71ee86a Mon Sep 17 00:00:00 2001 From: user Date: Fri, 22 Mar 2024 15:00:01 +0100 Subject: password of the month --- src/db_handler.py | 18 +++++++++++++++++- 1 file changed, 17 insertions(+), 1 deletion(-) (limited to 'src/db_handler.py') diff --git a/src/db_handler.py b/src/db_handler.py index 576612a..7766da1 100644 --- a/src/db_handler.py +++ b/src/db_handler.py @@ -33,8 +33,24 @@ def get_top(column: str) -> (list[dict], list[str]): GROUP BY {column} ORDER BY COUNT({column}) DESC LIMIT 20; - """).format(column=psycopg.sql.Identifier(column),)) + """).format(column=psycopg.sql.Identifier(column), )) top_usernames = cur.fetchall() col_names = [desc[0] for desc in cur.description] return top_usernames, col_names + + +def get_password_of_the_month() -> str: + with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn: + with conn.cursor() as cur: + cur.execute(""" +SELECT password +FROM login_attempt +WHERE timestamp BETWEEN current_timestamp - interval '1 month' AND timestamp +GROUP BY password +ORDER BY COUNT(password) DESC +LIMIT 1; + """) + + password = cur.fetchone()['password'] + return password -- cgit v1.2.3