From e2d1d421e422790b956e57e40522630221eae02c Mon Sep 17 00:00:00 2001 From: yuzu-eva Date: Tue, 11 Mar 2025 03:04:37 +0100 Subject: got website working; final touches are needed --- database.py | 34 +++++++++++++++++++++++++++------- 1 file changed, 27 insertions(+), 7 deletions(-) (limited to 'database.py') diff --git a/database.py b/database.py index aebd72e..a910a07 100755 --- a/database.py +++ b/database.py @@ -34,21 +34,41 @@ class Database: cursor.execute("SELECT alt, url FROM images WHERE sidebar_img = 1;") return cursor.fetchall() + def get_all_categories(self): + cursor = self.__connection.cursor() + cursor.execute("SELECT name FROM categories;") + return [i[0] for i in cursor.fetchall()] + + def add_category(self, category): + if not category in self.get_all_categories(): + cursor = self.__connection.cursor() + cursor.execute("INSERT INTO categories (name) VALUES (?);", (category, )) + + return True + return False + def get_all_thoughts(self): cursor = self.__connection.cursor() - cursor.execute("SELECT id, title, datetime FROM thoughts") + cursor.execute(""" + SELECT thoughts.id, title, datetime, categories.name + FROM thoughts INNER JOIN categories + ON thoughts.category_id = categories.id""") return cursor.fetchall() def get_thought(self, id_): cursor = self.__connection.cursor() cursor.execute(""" - SELECT title, datetime, markdown_text FROM thoughts - WHERE id = ?;""", (id_, )) - return cursor.fetchall() + SELECT title, datetime, markdown_text, redirect, name + FROM thoughts INNER JOIN categories + ON thoughts.category_id = categories.id + WHERE thoughts.id = ?;""", (id_, )) + return cursor.fetchone() - def add_thought(self, title, markdown): + def add_thought(self, title, markdown, category): cursor = self.__connection.cursor() cursor.execute(""" - INSERT INTO thoughts (title, datetime, markdown_text) - VALUES (?, datetime('now'), ?);""", (title, markdown)) + INSERT INTO thoughts (category_id, title, markdown_text, datetime) + VALUES (( + SELECT id FROM categories WHERE name = ? + ), ?, ?, datetime('now'));""", (category, title, markdown)) -- cgit v1.2.3