api.py

import json
import psycopg2
from flask import Flask, jsonify, request, send_from_directory
from flask_cors import cross_origin
from psycopg2 import sql
import configparser
from GetAverageData import get_interpolation_data_for_point, get_year_columns, get_average_of_multiple_years, get_min_max
from write_raster import write_raster
import SQLPandasTools as s2pTool

cfg = configparser.ConfigParser()
cfg.read('../config.ini')
assert "POSTGRES" in cfg, "missing POSTGRES in config.ini"
param_postgres = cfg["POSTGRES"]
app = Flask(__name__)
app.config['ENV'] = "development"
app.config['DEBUG'] = True
# Testing: Exceptions are propagated rather than handled by the the app’s error handlers. Extensions may also change their behavior to facilitate easier testing. You should enable this in your own tests.
app.config['TESTING'] = False


@app.route('/', methods=['GET'])
@cross_origin()
def index():
    columns = sql.SQL(' * ')    # columns to be queried (e.g. years)
    wheres = sql.SQL('')        # where filters
    values = ''                 # Used in second parameter of cursor.execute() (Avoids SQL injection)

    # Filter for getting specific years
    if 'years' in request.args:
        years = request.args['years'].split(',')
        years_clean = []

        for year in years:
            years_clean.append('"' + year + '"')
        years_clean = str(years_clean).replace('[', '').replace(']', '').replace("'", "").replace('\n', '').replace(' ', '')

        columns = sql.SQL(years_clean)

    # Filter for getting only real measurent data without any interpolated points
    if 'no-interpol' in request.args:
        wheres = wheres + (sql.SQL("AND file IS NOT NULL "))

    # Filter for specific coordinates
    if 'lat' in request.args or 'lng' in request.args:
        lat = request.args['lat']
        lon = request.args['lon']
        with psycopg2.connect(database=param_postgres["dbName"], user=param_postgres["user"], password=param_postgres["password"], host=param_postgres["host"], port=param_postgres["port"]) as connection:
            with connection.cursor() as cursor:
                return get_interpolation_data_for_point(lat, lon, columns, cursor)

    else:
        if 'id' in request.args:
            station_id = request.args['id']
            wheres = wheres + (sql.SQL("AND station_id = {values} ").format(column=sql.Identifier('stations', 'station_id'), values=sql.Placeholder()))
            for n in [int(station_id)]:
                values = (*values, n)  # adding n to existing tuple

        # Just for development
        # if 'country' in request.args:
        #     country = request.args['country']
        #     wheres = wheres + (sql.SQL("AND LOWER({column}) LIKE {values} ").format(column=sql.Identifier('stations', 'country'), values=sql.Placeholder()))
        #     for n in [country]:
        #         values = (*values, n)  # adding n to existing tuple

        # Composing query
        query = sql.SQL("SELECT array_to_json(array_agg(row_to_json(t))) from ("
                        "SELECT station_id, {} FROM stations "
                        "WHERE lon IS NOT NULL "    # Unnecessary filter, just so the real filters can always be written with AND
                        "{} "
                        ") t;").format(columns, wheres)

        # Actual query execution
        with psycopg2.connect(database=param_postgres["dbName"], user=param_postgres["user"], password=param_postgres["password"], host=param_postgres["host"], port=param_postgres["port"]) as connection:
            with connection.cursor() as cursor:
                # print(query.as_string(cursor))
                # print(values)
                cursor.execute(query, values)
                results = cursor.fetchall()[0][0]
                
                return s2pTool.sqlToGeoPandas(results).to_json()
                #return jsonify(results)


def getStandardQuery():
    columns = sql.SQL(' * ')    # columns to be queried (e.g. years)
    wheres = sql.SQL('')        # where filters
    
    query = sql.SQL("SELECT array_to_json(array_agg(row_to_json(t))) from ("
            "SELECT station_id, {} FROM stations "
            "WHERE file IS NOT NULL " 
            "{} "
            ") t;").format(columns, wheres)
    
    return query


@app.route('/minmax', methods=['GET'])
def return_min_max():
    with psycopg2.connect(database=param_postgres["dbName"], user=param_postgres["user"], password=param_postgres["password"], host=param_postgres["host"], port=param_postgres["port"]) as connection:
        with connection.cursor() as cursor:
            min, max = get_min_max(cursor)
            return {'min': str(min), 'max': str(max)}


@app.route('/raster', methods=['GET'])
def get_raster():
    """
    Request like: http://127.0.0.1:42000/raster?years=2010-2018&ramp=[[255,255,255,1],[255,244,191,1],[255,233,128,1],[255,221,64,1],[255,210,0,1],[243,105,0,1],[230,0,0,1],[153,0,0,1],[77,0,0,1],[0,0,0,1]]
    """
    if 'ramp' in request.args:
        ramp = json.loads(request.args['ramp'])
        print(ramp)
        if 'years' in request.args:
            years = request.args['years'].split('-')
            if int(years[1]) < int(years[0]):
                years = [years[1], years[0]]
            with psycopg2.connect(database=param_postgres["dbName"], user=param_postgres["user"], password=param_postgres["password"], host=param_postgres["host"], port=param_postgres["port"]) as connection:
                with connection.cursor() as cursor:
                    average_data = get_average_of_multiple_years(cursor, years)
                    path, filename = write_raster(average_data, ramp)
            return send_from_directory(path, filename=filename, as_attachment=True)
        # return 'Läuft, Brudi'


@app.route('/annualMean', methods=['GET'])
@cross_origin()
def annualMean():

    query = getStandardQuery()

    with psycopg2.connect(database=param_postgres["dbName"], user=param_postgres["user"], password=param_postgres["password"], host=param_postgres["host"], port=param_postgres["port"]) as connection:
        with connection.cursor() as cursor:

            cursor.execute(query)

            results = cursor.fetchall()[0][0]

            return jsonify(s2pTool.determineAnnualMean(results))

    return "{}"


if __name__ == '__main__':
    app.run(host='127.0.0.1', port=42000)