api-mer-data/models.py

from flask import jsonify, render_template, make_response
from flask_restful import Resource
from database import e
from json import dumps

class Demandas_Meta(Resource):
    def get(self):
        #Connect to database
        conn = e.connect()
        #Perform query and return JSON data
        query = conn.execute("select distinct PAIS from GENERACION_EOR_TR")
        result = query.cursor.fetchall()
        data = {'paises' : [row[0] for row in result]}
        return jsonify(data)

class Demandas(Resource):
    def get(self):
        #Connect to database
        conn = e.connect()
        #Perform query and return JSON data
        query = conn.execute("select * from DEMANDA_MER")
        columns = query.keys()
        data = []
        result = query.cursor.fetchall()
        for row in result:
            data.append(dict(zip(columns, row)))
        #List of Dicts to Dict of List (LD to DL)
        v = dict(zip(data[0], zip(*[d.values() for d in data])))
        response = jsonify({'data' : v})
        #Response is List of Dicts (LD)
        #response = jsonify({'data' : data})
        return response

class Demanda_Max(Resource):
    def get(self, pais):
        #Connect to database
        conn = e.connect()
        #Perform query and return JSON data
        query = conn.execute("""SELECT
                                FECHA_HORA,
                                LTRIM(RTRIM(PAIS)) PAIS,
                                DEMANDA 
                                FROM
                                GENERACION_EOR_TR 
                                WHERE
                                DEMANDA = 
                                (
                                    SELECT
                                    MAX(DEMANDA) 
                                    FROM
                                    GENERACION_EOR_TR 
                                    WHERE
                                    PAIS = '{0}' 
                                    AND FECHA_HORA >= DATEADD(DAY, - 1, GETDATE())
                                )
                                AND PAIS = '{0}' 
                                AND FECHA_HORA >= DATEADD(DAY, - 1, GETDATE())""".format(pais.upper()))
        columns = query.keys()
        data = []
        result = query.cursor.fetchall()
        #print(result)
        for row in result:
            data.append(dict(zip(columns, row)))
        response = jsonify({'data' : data[0]})
        return response

class Home_Page(Resource):
    def get(self):
        headers = {'Content-Type' : 'text/html'}
        return make_response(render_template('home.html', title = 'API MER Data'),200,headers)