api.py

import json

import psycopg2
from flask import Flask, jsonify, request
from psycopg2 import sql

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'])
def index():
    columns = sql.SQL(' * ')
    wheres = sql.SQL('')
    values = ''  # Gets used in cursor.execute()s' second parameter for safety reasons

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

    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('id, ') + sql.SQL(years_clean)

    # 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

    query = sql.SQL("SELECT array_to_json(array_agg(row_to_json(t))) from ("
                    "SELECT {} FROM stations WHERE lon IS NOT NULL "
                    "{} "
                    ") t;").format(columns, wheres)

    with psycopg2.connect(database='temperatures_berteld_morstein', user='postgres', password='postgres', host='localhost', port=5432) as connection:
        with connection.cursor() as cursor:
            print(query.as_string(cursor))
            print(values)
            cursor.execute(query, values)

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


app.run(host='127.0.0.1', port=42000)