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 [5]:
!pip3 install Flask
In [6]:
!curl http://localhost:5000/florb
In [7]:
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 [ ]:
!pip install requests
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))
In [ ]:
conn.rollback()
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 [ ]:
import requests
data = requests.get('http://localhost:5000/lakes', params={'type': "' OR true; --"}).json()
data
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 [ ]:
import requests
data = requests.get('http://localhost:5000/lakes', params={'sort': "florb"}).json()
[x['name'] for x in data[:5]]
In [ ]:
# paste code here
from flask import Flask, request, jsonify
import pg8000
import decimal
app= Flask(__name__)
@app.route("/lakes")
def get_lakes():
conn= pg8000.connect(database="mondial", user="skkandrach")
cursor= conn.cursor()
type_lakes = request.args.get('type', '')
sort_lakes= request.args.get('sort', '')
if (type_lakes != '') and (sort_lakes != '') and (sort_lakes != 'name'):
cursor.execute("SELECT name, elevation, area, type FROM lake WHERE type = %s ORDER BY " + sort_lakes + " DESC", [type_lakes])
elif type_lakes != '':
cursor.execute("SELECT name, elevation, area, type FROM lake WHERE type = %s ORDER BY name", [type_lakes])
elif sort_lakes not in ['name', 'elevation', 'area']:
cursor.execute("SELECT name, elevation, area, type FROM lake ORDER BY name" )
elif sort_lakes != '':
cursor.execute("SELECT name, elevation, area, type FROM lake ORDER BY " + sort_lakes + " DESC")
else:
cursor.execute("SELECT name, elevation, area, type FROM lake ORDER BY name")
def decimal_to_int(x):
if isinstance(x, decimal.Decimal):
return int(x)
else:
return None
whole_dict = []
for item in cursor.fetchall():
whole_dict.append({'name': item[0], 'elevation': decimal_to_int(item[1]), 'area': decimal_to_int(item[2]), 'type': item[3]})
return jsonify(everything_dict)
app.run(debug=True)
In [ ]: