notebook.community

Edit and run 





In [71]:



    


# TROUBLESHOOTING
conn = pg8000.connect(database = 'mondial')
cursor = conn.cursor()
# elevation and caldera works
# elevation and None #not working. I think it's because there was a problem passing in sort with %s . When I used the plus signs, it worked.
# elevation and earth
# area and caldera
# area and None
# area and earth
# name and caldera
# name and None
# name and earth
# earth and caldera
# earth and None
# earth and earth
possible_sorts = ['elevation', 'area', 'name', 'earth']
sort = 'elevation'
possible_type_params = ['caldera', None, 'earth']
type_param = 'earth'

# getting rid of nonsense type_param
cursor.execute('SELECT name, elevation, area, type FROM lake WHERE type = %s LIMIT 1', [type_param])
if not cursor.fetchone():
    lakes_list = []
    
if type_param:
    print('1a. There is a type_param:', type_param)
    if sort == 'elevation' or sort == 'area':
        print('2a. Sort is either \'elevation\' or \'area\':', sort)
        print('3a. So now we\'re finding all of the lakes with the parameters \'type_param\' =', type_param, ' and \'sort\' =', sort + ':')
        cursor.execute('SELECT name, elevation, area, type FROM lake WHERE type = %s ORDER BY ' + sort + ' desc LIMIT 10', [type_param])
    else:
        print('Sort is NOT \'elevation\' or \'area\'.')
        print('Sort is:', sort)
        sort = 'name'
        print('So we changed sort to \'name\':', sort)
        print('So now we\'re finding all of the lakes with the parameters \'type_param\' =', type_param, ' and \'sort\' =', sort + ':')
        cursor.execute('SELECT name, elevation, area, type FROM lake WHERE type = %s ORDER BY ' + sort + ' LIMIT 10', [type_param])
else:
    print('There is NOT a type_param:', type_param)
    if sort == 'elevation' or sort == 'area':
        print('Sort is either \'elevation\' or \'area\':', sort)
        print('So now we\'re finding all of the lakes with the parameters \'type_param\' =', type_param, ' and \'sort\' =', sort + ':')
        cursor.execute('SELECT name, elevation, area, type FROM lake ORDER BY ' + sort + ' desc LIMIT 10')
    else:
        print('Sort is NOT \'elevation\' or \'area\'.')
        print('Sort is:', sort)
        sort = 'name'
        print('So we changed sort to \'name\':', sort)
        print('So now we\'re finding all of the lakes with the parameters \'type_param\' =', type_param, ' and \'sort\' =', sort + ':')
        print('SELECT name, elevation, area, type FROM lake ORDER BY %s LIMIT 10', [sort])
        cursor.execute('SELECT name, elevation, area, type FROM lake ORDER BY ' + sort + ' LIMIT 10')
lakes_list = []
for item in cursor.fetchall():
    elevation = item[1]
    if elevation:
        elevation = int(elevation)
    area = item[2]
    if area:
        area = int(area)
    lakes_dict = {'name': item[0],
                 'elevation': elevation,
                 'area': area,
                 'type': item[3]}

    lakes_list.append(lakes_dict)
for dictionary in lakes_list:
    print(dictionary)



    


















    






1a. There is a type_param: earth
2a. Sort is either 'elevation' or 'area': elevation
3a. So now we're finding all of the lakes with the parameters 'type_param' = earth  and 'sort' = elevation:

Homework 6: Web Applications

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:

  • A request to /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.
  • The API should recognize the query string parameter 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.
  • The API should recognize the query string parameter type. When specified, the results should only include rows that have the specified value in the type field.
  • You should be able to use both the 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:

  • You'll need to construct the SQL query as a string, piece by piece. This will likely involve a somewhat messy tangle of if statements. Lean into the messy tangle.
  • Make sure to use parameter placeholders (%s) in the query.
  • If you're getting SQL errors, print out your SQL statement in the request handler function so you can debug it. (When you use print() in Flask, the results will display in your terminal window.)
  • When in doubt, return to the test code. Examine it carefully and make sure you know exactly what it's trying to do.

Problem set #1: A list of lakes

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
  • The API should recognize the query string parameter 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.
  • The API should recognize the query string parameter type. When specified, the results should only include rows that have the specified value in the type field.
  • You should be able to use both the sort and type parameters in any request.




In [47]:



    


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'])



    


















    






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

Problem set #2: Lakes of a certain type

The following code fetches all lakes of type salt and finds their average area and elevation.

Expected output:

average area: 18880
average elevation: 970




In [67]:



    


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))



    


















    






average area: 18880
average elevation: 970

Problem set #3: Lakes in order

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 [68]:



    


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)



    


















    






* 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

Problem set #4: Order and type

The following code prints the names of the largest caldera lakes, ordered in reverse order by area.

Expected output:

* Lake Nyos
* Lake Toba
* Lago Trasimeno
* Lago di Bolsena
* Lago di Bracciano
* Crater Lake
* Segara Anak
* Laacher Maar




In [69]:



    


import requests
data = requests.get('http://localhost:5000/lakes?sort=area&type=caldera').json()
for item in data:
    print("*", item['name'])



    


















    






* Lake Nyos
* Lake Toba
* Lago Trasimeno
* Lago di Bolsena
* Lago di Bracciano
* Crater Lake
* Segara Anak
* Laacher Maar

Problem set #5: Error handling

Your API should work fine even when faced with potential error-causing inputs. For example, the expected output for this statement is an empty list ([]), not every row in the table.





In [72]:



    


import requests
data = requests.get('http://localhost:5000/lakes', params={'type': "' OR true; --"}).json()
data



    


















    
Out[72]:








[]

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 [73]:



    


import requests
data = requests.get('http://localhost:5000/lakes', params={'sort': "florb"}).json()
[x['name'] for x in data[:5]]



    


















    
Out[73]:








['Ammersee', 'Arresoe', 'Atlin Lake', 'Balaton', 'Barrage de Mbakaou']

Paste your code

Please paste the code for your entire Flask application in the cell below, in case we want to take a look when grading or debugging your assignment.





In [ ]:



    


import pg8000
import decimal
from flask import Flask, request, jsonify
app = Flask(__name__)

@app.route('/lakes')
def give_lakes():
    conn = pg8000.connect(database = 'mondial', user = 'rebeccaschuetz')
    cursor = conn.cursor()
    sort = request.args.get('sort', 'name')
    type_param = request.args.get('type', None)

    # to get rid of not valid type_params:
    cursor.execute('SELECT name, elevation, area, type FROM lake WHERE type = %s LIMIT 1', [type_param])
    if not cursor.fetchone():
        lakes_list = []

    if type_param:
        if sort == 'elevation' or sort == 'area':
            cursor.execute('SELECT name, elevation, area, type FROM lake WHERE type = %s ORDER BY ' + sort + ' desc', [type_param])
        else:
            sort = 'name'
            cursor.execute('SELECT name, elevation, area, type FROM lake WHERE type = %s ORDER BY ' + sort, [type_param])
    else:
        if sort == 'elevation' or sort == 'area':
            cursor.execute('SELECT name, elevation, area, type FROM lake ORDER BY ' + sort + ' desc')
        else:
            sort = 'name'
            cursor.execute('SELECT name, elevation, area, type FROM lake ORDER BY ' + sort)
    lakes_list = []
    for item in cursor.fetchall():

        def decimal_to_int(x):
            if isinstance(x, decimal.Decimal):
                return int(x)
            else:
                return None

        # elevation = item[1]
        # if elevation:
        #     elevation = int(elevation)
        # area = item[2]
        # if area:
        #     area = int(area)
        # lakes_dict = {'name': item[0],
        #              'elevation': elevation,
        #              'area': area,
        #              'type': item[3]}

        lakes_dict = {'name': item[0],
                     'elevation': decimal_to_int(item[1]),
                     'area': decimal_to_int(item[2]),
                     'type': item[3]}

        lakes_list.append(lakes_dict)
    for dictionary in lakes_list:
        print(dictionary)
    return jsonify(lakes_list)

app.run()

Content source: raschuetz/foundations-homework

Similar notebooks:

notebook.community | gallery | about