server.py

#!/usr/bin/env python2.7

"""
Columbia's COMS W4111.001 Introduction to Databases
Example Webserver

To run locally:

    python server.py

Go to http://localhost:8111 in your browser.

A debugger such as "pdb" may be helpful for debugging.
Read about it online.
"""

import os
from sqlalchemy import *
from sqlalchemy.pool import NullPool
from flask import Flask, request, render_template, g, redirect, Response

tmpl_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'templates')
app = Flask(__name__, template_folder=tmpl_dir)


#
# The following is a dummy URI that does not connect to a valid database. You will need to modify it to connect to your Part 2 database in order to use the data.
#
# XXX: The URI should be in the format of: 
#
#     postgresql://USER:PASSWORD@104.196.135.151/proj1part2
#
# For example, if you had username gravano and password foobar, then the following line would be:
#
#     DATABASEURI = "postgresql://gravano:foobar@104.196.135.151/proj1part2"
#
DATABASEURI = "postgresql://brt2109:7475@104.196.135.151/proj1part2"


#
# This line creates a database engine that knows how to connect to the URI above.
#
engine = create_engine(DATABASEURI)

#
# Example of running queries in your database
# Note that this will probably not work if you already have a table named 'test' in your database, containing meaningful data. This is only an example showing you how to run queries in your database using SQLAlchemy.
#
engine.execute("""CREATE TABLE IF NOT EXISTS test (
  id serial,
  name text
);""")
engine.execute("""INSERT INTO test(name) VALUES ('grace hopper'), ('alan turing'), ('ada lovelace');""")


@app.before_request
def before_request():
  """
  This function is run at the beginning of every web request 
  (every time you enter an address in the web browser).
  We use it to setup a database connection that can be used throughout the request.

  The variable g is globally accessible.
  """
  try:
    g.conn = engine.connect()
  except:
    print "uh oh, problem connecting to database"
    import traceback; traceback.print_exc()
    g.conn = None

@app.teardown_request
def teardown_request(exception):
  """
  At the end of the web request, this makes sure to close the database connection.
  If you don't, the database could run out of memory!
  """
  try:
    g.conn.close()
  except Exception as e:
    pass


#
# @app.route is a decorator around index() that means:
#   run index() whenever the user tries to access the "/" path using a GET request
#
# If you wanted the user to go to, for example, localhost:8111/foobar/ with POST or GET then you could use:
#
#       @app.route("/foobar/", methods=["POST", "GET"])
#
# PROTIP: (the trailing / in the path is important)
# 
# see for routing: http://flask.pocoo.org/docs/0.10/quickstart/#routing
# see for decorators: http://simeonfranklin.com/blog/2012/jul/1/python-decorators-in-12-steps/
#
@app.route('/')
def index():
  """
  request is a special object that Flask provides to access web request information:

  request.method:   "GET" or "POST"
  request.form:     if the browser submitted a form, this contains the data in the form
  request.args:     dictionary of URL arguments, e.g., {a:1, b:2} for http://localhost?a=1&b=2

  See its API: http://flask.pocoo.org/docs/0.10/api/#incoming-request-data
  """

  # DEBUG: this is debugging code to see what request looks like
  print request.args


  #
  # example of a database query
  #

  #locations
  cursor = g.conn.execute("SELECT city FROM Locations")
  cities= []
  for result in cursor:
    cities.append(result[0])  # can also be accessed using result[0]
  cursor.close()
  

  #universes
  cursor = g.conn.execute("SELECT uid FROM Universes")
  universes= []
  for result in cursor:
    universes.append(result[0])  # can also be accessed using result[0]
  cursor.close()


  #
  # Flask uses Jinja templates, which is an extension to HTML where you can
  # pass data to a template and dynamically generate HTML based on the data
  # (you can think of it as simple PHP)
  # documentation: https://realpython.com/blog/python/primer-on-jinja-templating/
  #
  # You can see an example template in templates/index.html
  #
  # context are the variables that are passed to the template.
  # for example, "data" key in the context variable defined below will be 
  # accessible as a variable in index.html:
  #
  #     # will print: [u'grace hopper', u'alan turing', u'ada lovelace']
  #     <div>{{data}}</div>
  #     
  #     # creates a <div> tag for each element in data
  #     # will print: 
  #     #
  #     #   <div>grace hopper</div>
  #     #   <div>alan turing</div>
  #     #   <div>ada lovelace</div>
  #     #
  #     {% for n in data %}
  #     <div>{{n}}</div>
  #     {% endfor %}
  #
  context = dict(data = cities, data1 = universes)
 

  #
  # render_template looks in the templates/ folder for files.
  # for example, the below file reads template/index.html
  #
  return render_template("index.html", **context)


#
# This is an example of a different path.  You can see it at:
# 
#     localhost:8111/another
#
# Notice that the function name is another() rather than index()
# The functions for each app.route need to have different names
#
@app.route('/universe1', methods=['GET'])
def universe1():
  uid = request.args['unis']
  results = []
  cursor = g.conn.execute('SELECT i.alias, i.name, i.appdate, i.uid FROM Individuals AS i WHERE i.uid = (%s)', uid)
  for person in cursor:
    row = []
    row.append(person[0])
    row.append(person[1])
    row.append(person[2])
    row.append(person[3])

    results.append(row)

  cursor.close
  context = dict(data = results)
  return render_template("universe1.html", **context)

@app.route('/universe/<uid>')
def universe(uid):
  cursor = g.conn.execute('SELECT i.alias FROM Individuals AS i WHERE i.uid = (%s)', uid)
  results = []
  for person in cursor:
    results.append(person[0])
  cursor.close
  context = dict(data=results)
  return render_template("universe.html", **context)



#profile page
@app.route('/profile/<alias>')
def profile(alias):

  cursor = g.conn.execute('SELECT i.alias, i.name, i.appdate, i.species, i.uid, i.equipment FROM Individuals AS i WHERE i.alias = (%s)', alias)
  results = []
  civilian = false
  hero = false
  villain = false
  for person in cursor:
    row = []
    row.append(person[0]) 
    row.append(person[1])
    row.append(person[2])
    row.append(person[3])
    row.append(person[4])
    row.append(person[5])
  
    cursor = g.conn.execute('SELECT h.powname FROM Has_A AS h WHERE h.alias = (%s)', alias)
    for power in cursor:
      row.append(power[0]) #index 6
    cursor.close()
    if len(row)< 7:
      row.append("None") #index 6
    cursor = g.conn.execute('SELECT h.maincolor, h.sidekick FROM Heroes AS h WHERE h.alias = (%s)', alias)
    for ind in cursor:
      row.append(ind[0]) #index 7
      row.append(ind[1]) #index 8
      hero = true
      
    cursor.close()
    if len(row)< 9:
      row.append("NA")
      row.append("NA")
    
    cursor = g.conn.execute('SELECT c.employer FROM Civilians AS c WHERE c.alias = (%s)', alias)
    for ind in cursor:
      row.append(ind[0]) #index 9
      civilian = true
      
    cursor.close()
    if len(row)< 10:
      row.append("NA")
   
    cursor = g.conn.execute('SELECT v.nemesis FROM Villains AS v WHERE v.alias = (%s)', alias)
    for ind in cursor:
      row.append(ind[0]) #index 10
      villain = true
    cursor.close()
    if len(row) < 11:
      row.append("NA")

    if hero == true:
      row.append("Hero")
    elif civilian == true:
      row.append("Civilian")
    elif villain == true:
      row.append("Villain")
    results.append(row)
  cursor.close()

  affiliation = []
  cursor = g.conn.execute('SELECT b.aname FROM Belongs_To AS b WHERE b.alias = (%s)', alias)
  for aff in cursor:
    affiliation.append(aff[0])
  cursor.close()
  if affiliation:
    aff = affiliation[0]
    suggests = []
    cursor = g.conn.execute('SELECT b.alias FROM Belongs_To AS b WHERE b.aname = (%s)', aff)
    for person in cursor:
        suggests.append(person)
    cursor.close()



  #media
  media =[]
  movies = []
  books = []
  shows = []
  book = false
  show = false
  movie = false
  row = []

  cursor = g.conn.execute('SELECT m.title, m.releasedate FROM Appears_In AS m WHERE m.alias = (%s)', alias)
  for result in cursor:
    row = []
    row.append(result[0])
    row.append(result[1])
    media.append(row)

  cursor.close()
  for thing in media:
    title = thing[0]

    cursor = g.conn.execute('SELECT b.title, b.releasedate, b.author FROM Books AS b WHERE b.title = (%s)', title)
    for author in cursor:
      row = []
      row.append(author[0])
      row.append(author[1])
      row.append(author[2])
      books.append(row)
      
    cursor.close()

    cursor = g.conn.execute('SELECT t.title, t.releasedate, t.channel, t.director FROM TVShows AS t WHERE t.title = (%s)', title)
    for show in cursor:
        row = []
        row.append(show[0])
        row.append(show[1])
        row.append(show[2])
        row.append(show[3])

        shows.append(row)
        
    cursor.close()

    cursor = g.conn.execute('SELECT m.title, m.releasedate, m.phase, m.director FROM Movies AS m WHERE m.title = (%s)', title)
    for movie in cursor:
        row = []
        row.append(movie[0])
        row.append(movie[1])
        row.append(movie[2])
        row.append(movie[3])
    
        movies.append(row)
    cursor.close()

  if affiliation and books and movies:
    context = dict(data = results, data1 = affiliation, data2 = suggests, book = books, movie = movies)
  elif affiliation and books:
    context = dict(data = results, data1 = affiliation, data2 = suggests, book = books)
  elif affiliation and shows:
    context = dict(data = results, data1 = affiliation, data2 = suggests, show = shows)
  elif affiliation and movies:
    context = dict(data = results, data1 = affiliation, data2 = suggests, movie = movies)
  elif affiliation and shows and movies:
    context = dict(data = results, data1 = affiliation, data2 = suggests, show = shows, movie = movies)
  elif affiliation and movies and shows:
    context = dict(data = results, data1 = affiliation, data2 = suggests, movie = movies, show = shows)

  elif books:
    
    context = dict(data = results, book = books)

  elif books and movies:
    
    context = dict(data = results, book = books, movie = movies)

  elif shows:
    
    context = dict(data = results, show = shows)

  elif shows and movies:
    
    context = dict(data = results, show = shows, movie = movies)

  elif movies:
    context = dict(data = results, movie = movies)

  else:
    context = dict(data = results, data3 = media)
  

    
  return render_template("profile.html", **context)




# Example of adding new data to the database
@app.route('/add', methods=['POST'])
def add():
  name = request.form['name']
  g.conn.execute('INSERT INTO test(name) VALUES (%s)', name)
  return redirect('/')

@app.route('/another', methods=['GET'])
def results():
  results = []
  city = request.args['cities']
  cursor = g.conn.execute('SELECT Lives_In.alias FROM Lives_In INNER JOIN Locations ON Lives_In.city= Locations.city AND Lives_In.city = (%s)', city)
  in_city = []
  for result in cursor:
    in_city.append(result[0])  # can also be accessed using result[0]
  cursor.close()
  #get name and appearance date
  for person in in_city:
    cursor = g.conn.execute('SELECT i.alias, i.name, i.appdate, i.uid FROM Individuals AS i WHERE i.alias = (%s)', person)
    for person in cursor:
      row = []
      row.append(person[0])
      row.append(person[1])
      row.append(person[2])
      row.append(person[3])
      results.append(row)
    cursor.close()
  
  context = dict(data = results)
  return render_template("another.html", **context)

@app.route('/login')
def login():
    abort(401)
    this_is_never_executed()


if __name__ == "__main__":
  import click

  @click.command()
  @click.option('--debug', is_flag=True)
  @click.option('--threaded', is_flag=True)
  @click.argument('HOST', default='0.0.0.0')
  @click.argument('PORT', default=8111, type=int)
  def run(debug, threaded, host, port):
    """
    This function handles command line parameters.
    Run the server using:

        python server.py

    Show the help text using:

        python server.py --help

    """

    HOST, PORT = host, port
    print "running on %s:%d" % (HOST, PORT)
    app.run(host=HOST, port=PORT, debug=debug, threaded=threaded)


  run()