1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
from dataclasses import dataclass
import sqlite3
import datetime
@dataclass
class Database:
def __enter__(self):
self.__connection = sqlite3.connect("website.db")
return self
def __exit__(self, type, value, traceback):
self.__connection.commit()
self.__connection.close()
def get_header_links(self):
cursor = self.__connection.cursor()
cursor.execute("SELECT name, link FROM headerLinks;")
return cursor.fetchall()
def get_image(self, imageName):
cursor = self.__connection.cursor()
cursor.execute("SELECT alt, url FROM images WHERE imageName = %s;",
(imageName, ))
return cursor.fetchone()
def get_pfp_images(self):
cursor = self.__connection.cursor()
cursor.execute("SELECT alt, url FROM images WHERE pfp_img = 1;")
return cursor.fetchall()
def get_sidebar_images(self):
cursor = self.__connection.cursor()
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 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, 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, category):
cursor = self.__connection.cursor()
cursor.execute("""
INSERT INTO thoughts (category_id, title, markdown_text, datetime)
VALUES ((
SELECT id FROM categories WHERE name = ?
), ?, ?, datetime('now'));""", (category, title, markdown))
|
