finised parser, finished rendering posts, finished sercving images

1 files changed, 73 insertions, 5 deletions

diff --git a/database.py b/database.py
index 93f7538..142d942 100644
--- a/database.py
+++ b/database.py
@@ -1,12 +1,28 @@
+from dataclasses import dataclass
 import pymysql
+import random
 import app
 
+@dataclass
 class Database:
+    safeLogin:bool = True   #automatically login with the user in the config file, who is read only
+    user:str = None         #otherwise, login with the given username and passwd
+    passwd:str = None
+
     def __enter__(self):
-        self.__connection = pymysql.connect(
-            **app.CONFIG["mysql"],
-            charset = "utf8mb4"
-        )
+        if self.safeLogin:
+            self.__connection = pymysql.connect(
+                **app.CONFIG["mysql"],
+                charset = "utf8mb4"
+            )
+        else:
+            self.__connection = pymysql.connect(
+                user = self.user,
+                passwd = self.passwd,
+                host = app.CONFIG["mysql"]["host"],
+                db = app.CONFIG["mysql"]["db"],
+                charset = "utf8mb4"
+            )
         return self
 
     def __exit__(self, type, value, traceback):
@@ -22,12 +38,64 @@ class Database:
             cursor.execute("SELECT alt, url FROM images WHERE imageName = %s;", (imageName, ))
             return cursor.fetchone()
 
+    def get_pfp_image(self):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT alt, url FROM images WHERE pfp_img = 1;")
+            return random.choice(cursor.fetchall())
+
     def get_header_articles(self):
         with self.__connection.cursor() as cursor:
             cursor.execute("SELECT articleName, link FROM headerArticles;")
             return cursor.fetchall()
 
+    def get_all_categories(self):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT category_name FROM categories;")
+            return [i[0] for i in cursor.fetchall()]
+
+    def add_category(self, category):
+        if not category in self.get_all_categories():
+            with self.__connection.cursor() as cursor:
+                cursor.execute("INSERT INTO categories (category_name) VALUES (%s);", (category, ))
+
+            self.__connection.commit()
+            return True
+            
+        return False
+
+    def add_thought(self, category, title, markdown):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("""
+            INSERT INTO thoughts (category_id, title, markdown_text) 
+            VALUES ((
+                SELECT category_id FROM categories WHERE category_name = %s
+            ), %s, %s);""", (category, title, markdown))
+        self.__connection.commit()
+
+    def get_thought(self, id_):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("""
+            SELECT categories.category_name, thoughts.title, thoughts.dt, thoughts.markdown_text 
+            FROM thoughts INNER JOIN categories 
+            ON thoughts.category_id = categories.category_id 
+            WHERE thought_id = %s;""", (id_, ))
+            return cursor.fetchone()
+
+    def get_categories_not(self, category_name):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SELECT category_name FROM categories WHERE category_name != %s;", (category_name, ))
+            return [i[0] for i in cursor.fetchall()]
+
+    def get_all_thoughts(self):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("""
+            SELECT thought_id, title, dt, category_name FROM thoughts 
+            INNER JOIN categories ON thoughts.category_id = categories.category_id;
+            """)
+            return cursor.fetchall()
+
+
 
 if __name__ == "__main__":
     with Database() as db:
-        print(db.get_image("headerImage"))
\ No newline at end of file
+        print(db.get_all_thoughts())
\ No newline at end of file