POC: Flask-based API service

[NealHumphrey]

Since more people are familiar with Flask, we are going to try using this as the platform to connect our database to an API that can be queried by the front end. The scope of this ticket is to set up an initial version of a Flask web app that people can add new end points to.

Scope:

• [ ] Set up a Flask app. Select an appropriate candidate Flask extension to use with this for the API, such as Flask-RESTful or Flask-Restless. RESTful appears to be more flexible since we will want some API endpoints that do custom joins with more than one table, but assignee should use their judgement.

• [ ] Create a generic table end point that can return all rows of any table in our database using the endpoint api/table/<tablename>. Format output as json using a list of dictionaries similar to our Lambda demo

• [ ] Create a specific table end point for returning counts of records in a specific zone. Start with the building_permits table. Zones include:

  • zip code: api/building_permits/zip returns total count of records in each unique zip code
  • neighborhood cluster: api/building_permits/nc total count of records in each unique neighborhood cluster.
  • census_tract: After #177 is complete, this will be available in the database - skip for now
  • ward: api/building_permits/ward

Should merge this into dev after this much is done, so that multiple people can start adding additional endpoints.

[jasonrhaas]

I think connexion would be a good place for us to start. We can build out the API endpoints in YAML, and it also self-generates the documentation for the various endpoints. It's built on top of Flask and it's pretty easy to get started with. It also uses the Swagger API specification, so our API will be standardized to that.

https://connexion.readthedocs.io/en/latest/index.html

Here's an example of the UI you get using the swagger spec and connexion:

[jasonrhaas]

Another option is Eve, which is a super easy out-of-the-box solution. It does CRUD operations out of the box, supports an SQL back-end and SQLAlchemy.

http://python-eve.org/

So many API options...

[NealHumphrey]

Just adding a note to this ticket, in case anyone else is working on this note that @jasonrhaas started on a POC using Eve in the related ticket #179 . @jasonrhaas keep us updated on progress, if it looks like Eve is the way to go, and if you need any help turning your first pass into a working version with our real data.

[jasonrhaas]

Update: I made some progress on this. Since we aren't using SQLAlchemy yet we have to do manual queries of the database and jsonify the tuple responses.

If we move to the ORM SQLAlchemy approach, Flask RESTless pretty much has a built in API. But as of right now we need to manually add all the endpoints which isn't ideal.

Anyway, it looks kind of like this:

@app.route('/api/project_temp', methods=['GET'])
def project_temp():
    q = 'SELECT * from project_temp;'
    with conn.cursor(cursor_factory=RealDictCursor) as cur:
        cur.execute(q)
        results = cur.fetchall()

[jasonrhaas]

Prototype for this is complete. I did everything except the nc and ward endpoints. See #189

[NealHumphrey]

Woo woo! @jasonrhaas (and also @ptgott and @ostermanj and @prisalex ) I got this deployed to an AWS instance (just on my personal account for now). I also added an additional endpoint that returns all building_permit counts grouped by whatever grouping is desired (ward, zip etc.). These endpoints are available:

http://hiapidemo.us-east-1.elasticbeanstalk.com/ : tells us the API is awesome http://hiapidemo.us-east-1.elasticbeanstalk.com/api/building_permits/all/neighborhoodcluster http://hiapidemo.us-east-1.elasticbeanstalk.com/api/building_permits/all/anc http://hiapidemo.us-east-1.elasticbeanstalk.com/api/building_permits/all/zipcode http://hiapidemo.us-east-1.elasticbeanstalk.com/api/building_permits/all/ward http://hiapidemo.us-east-1.elasticbeanstalk.com/api/building_permits/zip/<actualzip>: returns count for just one zip http://hiapidemo.us-east-1.elasticbeanstalk.com/api/<table> returns first 100 rows

Right now I used zipcode instead of zip because that's the actual name of the

[ptgott]

Thanks for this!

Since the API returns zone-by-zone aggregates with the zone labels as keys, what does everyone think about integrating the data with geojson 'Polygon' FeatureCollections as in 'javascript/tool/data'? We could have one query return, say, 'ward.json', one query return the 'ward' aggregates, and a front-end script assign an 'aggregate_building_permits' property to each Feature in the FeatureCollection based on the data returned from the API. This way we could add Mapbox layers showing filled Polygons colored according to the aggregate. Or we could mix the geoJSON and the aggregates on the back-end? What do you think?

[prisalex]

Finished off the nc & ward endpoints. Seemed too easy but hopefully it is what was requested...

https://github.com/codefordc/housing-insights/pull/191

[NealHumphrey]

Thanks @jasonrhaas for getting this up! Closing this issue as we have a deployed version of this working. As noted in my review of the PR, did move this into the /python folder and ended up converting the queries to SQLAlchemy for consistency with how the ingestion code works so there's a lower barrier for people to get up to speed on the code.

I'll post a new issue and probably plan to do a planning discussion sometime in the next week or so on how to take this to the next phase, i.e. how to structure the app as it grows and how to build in the ORM.