Revamp comments style like sql statements

2 files changed, 14 insertions, 9 deletions

diff --git a/src/blog_node5_net.py b/src/blog_node5_net.py
index 5761fc1..0c2a865 100644
--- a/src/blog_node5_net.py
+++ b/src/blog_node5_net.py
@@ -94,14 +94,18 @@ except FileNotFoundError as ex:
 @app.context_processor  # Always inject site title to all render_templates
 def inject_common():
     url = strip_trailing_slash(flask.request.path)
-    comments, comment_headers = db.get_comments(url)
+    comments, comment_headers, comments_sql = db.get_comments(url)
     if comments:
         comments_formatted = tabulate.tabulate(comments, comment_headers, tablefmt="psql")
     else:
-        comments_formatted = f"""sqlite> SELECT COUNT(comment) FROM comment WHERE page_url = '{url}';
-0"""
+        comments_formatted = ""
+    insert_sql_pretty = markdown.markdown(f"```sql\nINSERT INTO comment(nickname, visitor_url, contact, show_contact, comment, public) VALUES (\n```", extensions=['fenced_code','codehilite'])
 
+    sql_pretty = markdown.markdown(f"```sql{ comments_sql }```", extensions=['fenced_code','codehilite'])
+    print(sql_pretty)
     args = {
+        'insert_sql_pretty': insert_sql_pretty,
+        'comments_sql': markupsafe.Markup(markdown.markdown(sql_pretty)),
         'comments': comments_formatted,
         'title': config['site_root_folder_path'],
         'motd': random.choice(motd_list)
diff --git a/src/db_handler.py b/src/db_handler.py
index 6789105..471154a 100644
--- a/src/db_handler.py
+++ b/src/db_handler.py
@@ -15,14 +15,15 @@ class DBHandler:
     def get_comments(self, url: str) -> list[dict]:
         with self.con_factory() as con:
             cur = con.cursor()
-            cur.execute(
-                '''
-                SELECT id, nickname, comment, page_url, visitor_url, (CASE WHEN show_contact THEN contact ELSE NULL END) as contact_info, created_at
-                FROM comment WHERE approved AND public AND page_url = ? ORDER BY created_at DESC;
-                ''', (url,))
+            comments_sql = '''
+SELECT id, nickname, comment, page_url, visitor_url, (CASE WHEN show_contact THEN contact ELSE NULL END) as contact_info, created_at
+FROM comment WHERE approved AND public AND page_url = ? ORDER BY created_at DESC;
+'''
+            cur.execute(comments_sql, (url,))
             comments = cur.fetchall()
             headers = list(map(lambda attr : attr[0], cur.description))
-            return comments, headers
+            comments_sql = comments_sql.replace('?', f"'{url}'")
+            return comments, headers, comments_sql
 
 
     def post_comment(self, comment: str, page_url: str, visitor_url: str=None, nickname: str=None, contact: str=None,