API

[ericnost]

This issue lays out some ideas for how to serve data from the database in different ways.

Streamlit

Streamlit is a Python web framework that provides a number of convenient tools for relatively simple dashboard building. My sense is that Streamlit could function well to display data, but doesn't provide tools for serving it. Additionally, there are some downsides in terms of really only supporting Python (no HTML/CSS involved) as well as a bit of wonkiness in the dynamic elements (though these in themselves are huge positives).

Flask

Flask is a more general Python web framework that does routing. This makes it useful for APIs and for apps that want to make use of HTML/CSS.

A comparison of the two is here: https://somabhadra.medium.com/the-conclusive-face-off-flask-vs-streamlit-40bdef6859a4

Flask API

Here's what an API + renderer with Flask could look like:

import os
from flask import Flask, render_template
from sqlalchemy import create_engine
import pandas

app = Flask(__name__)

# Get Data Function
def get_data(view, filter = None, value = None):
  """Get database environment"""
  #user = os.environ.get('USER', 'Default')
  #db = os.environ.get('DBNAME', 'Default')
  #pass = os.environ.get('PASS', 'Default')
  # etc.

  # construct SQLAlchemy create_engine connection string here
  # a lot of this is already done in npri; it would be moved here

  try:
    # see npri for how to construct sql request
    sql = 'select * from '+view+' where `+filter+` = `+value+`
    data = pandas.read_sql(sql)
  except:
    data = "Error" # return redirect(url_for('home'))

  return data

# Homepage
@app.route('/')
def home():
  return render_template('home.html')

# API - for returning data as Pandas DataFrame or as JSON input to webapp
@app.route('/<application>/<view>/<filter>/<value>')
def api(application, view, filter = None, value = None):
  data = get_data(view, filter, value)
  if application == "api":
    return data
  elif application == "report":
    return render_template('report.html', data = json(data))

if __name__ == '__main__':
    server_port = os.environ.get('PORT', '8080')
    app.run(debug=False, port=server_port, host='0.0.0.0')

[ericnost]

Will have to serializenpri objects, see: https://stackoverflow.com/questions/58343453/typeerror-object-of-type-is-not-json-serializable

This will mean changing how things are handled on the client side.