Created
August 8, 2017 13:34
-
-
Save codepope/007b5469206ccf0d062f68ea583b631c to your computer and use it in GitHub Desktop.
A grandtour example of on the fly db creation.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from flask import Flask | |
from flask import render_template | |
from flask import request | |
import os | |
from urllib.parse import urlparse | |
import json | |
import psycopg2 | |
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT | |
app = Flask(__name__) | |
# connection string and db connection initialization | |
POSTGRESQL_URL = os.environ['POSTGRESQL_URL'] | |
parsed = urlparse(POSTGRESQL_URL) | |
# Open a connection to the default compose database | |
conn = psycopg2.connect( | |
host=parsed.hostname, | |
port=parsed.port, | |
user=parsed.username, | |
password=parsed.password, | |
database='compose') | |
# Now query it to see if the "grand_tour" database exists | |
cursor=conn.cursor() | |
cursor.execute("SELECT COUNT(*) = 0 FROM pg_catalog.pg_database WHERE datname = 'grand_tour'") | |
not_exists_row = cursor.fetchone() | |
not_exists = not_exists_row[0] | |
if not_exists: | |
# It doesn't. We get to create it here. | |
# We have to tweak the isolation level so we aren't in a transaction | |
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) | |
# Now we get our cursor again | |
cursor=conn.cursor() | |
# And we create the database | |
cursor.execute('CREATE DATABASE grand_tour') | |
# Whatever happened previously, now we can open a connection to the | |
# "grand_tour" database and carry on... | |
conn = psycopg2.connect( | |
host=parsed.hostname, | |
port=parsed.port, | |
user=parsed.username, | |
password=parsed.password, | |
database='grand_tour') | |
@app.route('/') | |
# top-level page display, creates table if it doesn't exist | |
def serve_page(name=None): | |
cur = conn.cursor() | |
cur.execute("""CREATE TABLE IF NOT EXISTS words ( | |
id serial primary key, | |
word varchar(256) NOT NULL, | |
definition varchar(256) NOT NULL) """) | |
return render_template('index.html', name=name) | |
@app.route('/words', methods=['PUT']) | |
# triggers on hitting the 'Add' button; inserts word/definition into table | |
def handle_words(name=None): | |
cur = conn.cursor() | |
cur.execute("""INSERT INTO words (word, definition) | |
VALUES (%s, %s)""",(request.form['word'],request.form['definition'])) | |
conn.commit() | |
return "ECHO: PUT\n" | |
@app.route('/words', methods=['GET']) | |
# query for all the rows in the table,\ | |
# makes a dictionary object from the column names and the results,\ | |
# makes json from the dict for display on the page. | |
def display_find(name=None): | |
cur = conn.cursor() | |
cur.execute("""SELECT word, definition FROM words""") | |
cursor_obj = cur.fetchall() | |
labels = [column[0] for column in cur.description] | |
results_list = [] | |
for row in cursor_obj: | |
results_list.append(dict(zip(labels, row))) | |
return json.dumps(results_list) | |
if __name__ == "__main__": | |
app.run() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment