For this homework, you're going to write a web API for the lake data in the MONDIAL database. (Make sure you've imported the data as originally outlined in our week 1 tutorial.)
The API should perform the following tasks:
/lakes should return a JSON list of dictionaries, with the information from the name, elevation, area and type fields from the lake table in MONDIAL.sort. When left blank or set to name, the results should be sorted by the name of the lake (in alphabetical order). When set to area or elevation, the results should be sorted by the requested field, in descending order.type. When specified, the results should only include rows that have the specified value in the type field.sort and type parameters in any request.This notebook contains only test requests to your API. Write the API as a standalone Python program, start the program and then run the code in the cells below to ensure that your API produces the expected output. When you're done, paste the source code in the final cell (so we can check your work, if needed).
Hints when writing your API code:
if statements. Lean into the messy tangle.print() in Flask, the results will display in your terminal window.)Your API should return a JSON list of dictionaries (objects). Use the code below to determine what the keys of the dictionaries should be. (For brevity, this example only prints out the first ten records, but of course your API should return all of them.)
Expected output:
143 lakes
Ammersee - elevation: 533 m / area: 46 km^2 / type: None
Arresoe - elevation: None m / area: 40 km^2 / type: None
Atlin Lake - elevation: 668 m / area: 798 km^2 / type: None
Balaton - elevation: 104 m / area: 594 km^2 / type: None
Barrage de Mbakaou - elevation: None m / area: None km^2 / type: dam
Bodensee - elevation: 395 m / area: 538 km^2 / type: None
Brienzersee - elevation: 564 m / area: 29 km^2 / type: None
Caspian Sea - elevation: -28 m / area: 386400 km^2 / type: salt
Chad Lake - elevation: 250 m / area: 23000 km^2 / type: salt
Chew Bahir - elevation: 520 m / area: 800 km^2 / type: salt
In [ ]:
import requests
data = requests.get('http://localhost:5000/lakes').json()
print(len(data), "lakes")
for item in data[:10]:
print(item['name'], "- elevation:", item['elevation'], "m / area:", item['area'], "km^2 / type:", item['type'])
In [ ]:
import requests
data = requests.get('http://localhost:5000/lakes?type=salt').json()
avg_area = sum([x['area'] for x in data if x['area'] is not None]) / len(data)
avg_elev = sum([x['elevation'] for x in data if x['elevation'] is not None]) / len(data)
print("average area:", int(avg_area))
print("average elevation:", int(avg_elev))
The following code fetches lakes in reverse order by their elevation and prints out the name of the first fifteen, excluding lakes with an empty elevation field.
Expected output:
* Licancabur Crater Lake
* Nam Co
* Lago Junin
* Lake Titicaca
* Poopo
* Salar de Uyuni
* Koli Sarez
* Lake Irazu
* Qinghai Lake
* Segara Anak
* Lake Tahoe
* Crater Lake
* Lake Tana
* Lake Van
* Issyk-Kul
In [ ]:
import requests
data = requests.get('http://localhost:5000/lakes?sort=elevation').json()
for item in [x['name'] for x in data if x['elevation'] is not None][:15]:
print("*", item)
In [ ]:
import requests
data = requests.get('http://localhost:5000/lakes?sort=area&type=caldera').json()
for item in data:
print("*", item['name'])
In [43]:
import requests
data = requests.get('http://localhost:5000/lakes', params={'type': "' OR true; --"}).json()
data
Out[43]:
Specifying a field other than name, area or elevation for the sort parameter should fail silently, defaulting to sorting alphabetically. Expected output: ['Ammersee', 'Arresoe', 'Atlin Lake', 'Balaton', 'Barrage de Mbakaou']
In [46]:
import requests
data = requests.get('http://localhost:5000/lakes', params={'sort': "florb"}).json()
[x['name'] for x in data[:5]]
Out[46]:
In [58]:
conn.rollback()
In [ ]:
from flask import Flask, request, jsonify
from decimal import Decimal
import pg8000
app = Flask(__name__)
conn = pg8000.connect(user="postgres",password="password", database="mondial")
@app.route("/lakes")
def get_lakes():
sorting = request.args.get('sort', 'name')
get_type = request.args.get('type', 0)
cursor = conn.cursor()
sort_by = "ORDER BY name"
if sorting=='area':
sort_by = "ORDER BY area DESC"
elif sorting=='elevation':
sort_by = "ORDER BY elevation DESC"
elif sorting=='name':
sort_by = "ORDER BY name DESC"
if get_type:
cursor.execute("SELECT name, area, elevation, type FROM lake WHERE type=%s " + sort_by, [get_type])
else:
cursor.execute("SELECT name, area, elevation, type FROM lake " + sort_by)
def decimal_to_int(x):
if isinstance(x, decimal.Decimal):
return int(x)
else:
return None
output = []
for item in cursor.fetchall():
try:
dictionary = {'name':item[0],'area': int(item[1]),'elevation':float(item[2]), 'type':item[3]}
print(dictionary)
output.append(
dictionary
)
except:
pass
return jsonify(output)
app.run(port=5004)
In [36]:
conn.rollback()
In [ ]: