G1H93ZpQ

1from flask import Flask
2from flask import render_template
3from flask import request
4
5import os
6from urllib.parse import urlparse
7import json
8
9import psycopg2
10from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
11
12app = Flask(__name__)
13
14# connection string and db connection initialization
15POSTGRESQL_URL = os.environ['POSTGRESQL_URL']
16parsed = urlparse(POSTGRESQL_URL)
17
18# Open a connection to the default compose database
19conn = psycopg2.connect(
20    host=parsed.hostname,
21    port=parsed.port,
22    user=parsed.username,
23    password=parsed.password,
24    database='compose')
25
26# Now query it to see if the "grand_tour" database exists
27cursor=conn.cursor()
28cursor.execute("SELECT COUNT(*) = 0 FROM pg_catalog.pg_database WHERE datname = 'grand_tour'")
29not_exists_row = cursor.fetchone()
30not_exists = not_exists_row[0]
31if not_exists:
32    # It doesn't. We get to create it here.
33    # We have to tweak the isolation level so we aren't in a transaction
34    conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
35    # Now we get our cursor again
36    cursor=conn.cursor()
37    # And we create the database
38    cursor.execute('CREATE DATABASE grand_tour')
39
40# Whatever happened previously, now we can open a connection to the
41# "grand_tour" database and carry on...
42
43conn = psycopg2.connect(
44    host=parsed.hostname,
45    port=parsed.port,
46    user=parsed.username,
47    password=parsed.password,
48    database='grand_tour')
49
50@app.route('/')
51# top-level page display, creates table if it doesn't exist 
52def serve_page(name=None):
53    cur = conn.cursor()
54    cur.execute("""CREATE TABLE IF NOT EXISTS words (
55		id serial primary key,
56		word varchar(256) NOT NULL,
57		definition varchar(256) NOT NULL) """)
58    return render_template('index.html', name=name)
59
60@app.route('/words', methods=['PUT'])
61# triggers on hitting the 'Add' button; inserts word/definition into table
62def handle_words(name=None):
63    cur = conn.cursor()
64    cur.execute("""INSERT INTO words (word, definition)
65        VALUES (%s, %s)""",(request.form['word'],request.form['definition']))
66    conn.commit()
67    return "ECHO: PUT\n"
68
69@app.route('/words', methods=['GET'])
70# query for all the rows in the table,\
71# makes a dictionary object from the column names and the results,\
72# makes json from the dict for display on the page.
73def display_find(name=None):
74    cur = conn.cursor()
75    cur.execute("""SELECT word, definition FROM words""")
76    cursor_obj = cur.fetchall()
77
78    labels = [column[0] for column in cur.description]
79    results_list = []
80
81    for row in cursor_obj:
82        results_list.append(dict(zip(labels, row)))
83
84    return json.dumps(results_list)
85
86if __name__ == "__main__":
87    app.run()