From bc178741cf0d95e7fb8c25c0f04e40ac7a65cdf5 Mon Sep 17 00:00:00 2001
From: Nicholas Van Doorn <vandoorn.nick@gmail.com>
Date: Fri, 9 Jul 2021 13:32:42 -0700
Subject: We SQL now

its messy but whatever
---
 app/main.py        | 19 ++++++++++++++-----
 db/create_table.py |  8 ++++++++
 db/drop_table.py   |  8 ++++++++
 db/up.py           | 11 +++++++++++
 requirements.txt   |  1 +
 5 files changed, 42 insertions(+), 5 deletions(-)
 create mode 100644 db/create_table.py
 create mode 100644 db/drop_table.py
 create mode 100644 db/up.py

diff --git a/app/main.py b/app/main.py
index 9dc9b5b..b01c212 100644
--- a/app/main.py
+++ b/app/main.py
@@ -1,18 +1,27 @@
 from flask import *
 from tinydb import TinyDB
+import psycopg2
+import os
 
 app = Flask(__name__)
+def conn():
+    return psycopg2.connect(os.environ.get("DATABASE_URL") or "dbname=questbook user=postgres")
+
+def table_to_dict(table):
+    return { "email": table[0], "name": table[1], "message": table[2] }
 
 @app.route("/")
 def home():
-    posts = get_db().all()
+    cur = conn().cursor()
+    cur.execute("SELECT email, name, message FROM messages;")
+    posts = map(table_to_dict, cur.fetchall())
     return render_template("home.html", posts = posts)
 
 @app.route("/message", methods=["POST"])
 def create_message():
     form = request.form
-    get_db().insert({ "name": form["name"], "email": form["email"], "message": form["message"]})
+    local_conn = conn()
+    cur = local_conn.cursor()
+    cur.execute("insert into messages (email, name, message)\n values (%s, %s, %s)", (form["email"], form["name"], form["message"]))
+    local_conn.commit()
     return redirect("/")
-
-def get_db():
-    return TinyDB("db.json").table("messages")
diff --git a/db/create_table.py b/db/create_table.py
new file mode 100644
index 0000000..168fb16
--- /dev/null
+++ b/db/create_table.py
@@ -0,0 +1,8 @@
+import psycopg2
+import os
+
+conn = psycopg2.connect(os.environ.get("DATABASE_URL") or "dbname=questbook user=postgres")
+conn.cursor().execute("create table messages (id int, email varchar(255), name varchar(255), message varchar(2048))")
+conn.commit()
+
+
diff --git a/db/drop_table.py b/db/drop_table.py
new file mode 100644
index 0000000..3b8d1ff
--- /dev/null
+++ b/db/drop_table.py
@@ -0,0 +1,8 @@
+import psycopg2
+import os
+
+conn = psycopg2.connect(os.environ.get("DATABASE_URL") or "dbname=questbook user=postgres")
+conn.cursor().execute("drop table messages")
+conn.commit()
+
+
diff --git a/db/up.py b/db/up.py
new file mode 100644
index 0000000..60700c8
--- /dev/null
+++ b/db/up.py
@@ -0,0 +1,11 @@
+import psycopg2
+from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
+import os
+
+conn = psycopg2.connect(os.environ.get("DATABASE_URL") or "user=postgres")
+conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
+conn.cursor().execute("create database \"questbook\";")
+conn.commit()
+conn.cursor().execute("create table messages (id int, email varchar(255), name varchar(255), message varchar(2048))")
+conn.commit()
+
diff --git a/requirements.txt b/requirements.txt
index 42549db..2459026 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -4,5 +4,6 @@ gunicorn==20.1.0
 itsdangerous==2.0.1
 Jinja2==3.0.1
 MarkupSafe==2.0.1
+psycopg2-binary==2.9.1
 tinydb==4.5.0
 Werkzeug==2.0.1
-- 
cgit v1.2.3