Categories
Mastering Development

Charting JSON data from Flask and MySQL

I’ve stored JSON data into a MySQL database and now I need to pull specific elements out as needed for use with Chartjs (https://www.chartjs.org/docs/latest/getting-started/)

<link rel="stylesheet" href="/static/assets/css/Chart.min.css">
<script type="text/javascript" src="/static/assets/js/plugins/Chart.min.js"></script>

<canvas id="canvas" height="300"></canvas>

MySQL’s cursor class is set to “DictCursor” in Flask so that it returns python lists instead of tuples –

from flask import Flask, request, json, jsonify
import pymysql

app = Flask(__name__)    

@app.route('/data', methods=['GET', 'POST'])
    def data():
        ind_id = 1
        ctype = 1
        qry = """SELECT TEPs FROM db.charts WHERE chart_id = '%i' AND chart_type = '%i' ;"""
        port = 3306
        xychart = 0  
        conn = pymysql.connect(
            user = "user", 
            password = "password", 
            host = "localhost",
            database = "database",
            cursorclass=pymysql.cursors.DictCursor
            )
        cur = conn.cursor()
        first_query = (qry % (ind_id, ctype))
        cur.execute(first_query)

        results = cur.fetchone()
        cur.close()
        result = (results["TEPs"])
        # jresult = json.dumps(result,indent=4)
        # jresult = json.loads(result)
        # print(jresult)
        return jsonify(result)

if __name__ == '__main__':
    app.run(debug=True)

This returns the following data JSON string to jquery, but I cannot use jquery to parse the json info in Javascript:

"{'Info': {'Indicator': 'Fin.Dev.Index.IMF', 'Desc': 'Financial Industry Index IMF'}, 'Chart_Detail': {'Chart_Type': 'TEP All Nations', 'Chart_Id': '0', 'Page': 'Fin.Dev.Index.IMF.htm', 'Graph': 'Fin.Dev.Index.IMF..jpg'}, 'XY_Data': {'XY_AdvanceX': [0.09, 0.13, 0.15, 0.21, 0.26, 0.32, 0.4, 0.52, 0.69, 0.93], 'XY_AdvanceY': [0.1, 0.05, 0.37, 0.21, 0.11, 0.37, 0.68, 0.5, 0.94, 0.75], 'SurveysX': [0.09, 0.13, 0.15, 0.21, 0.26, 0.32, 0.4, 0.52, 0.69, 0.93], 'SurveysY': [10, 20, 19, 19, 19, 19, 19, 18, 18, 16], 'RegressionX': [0.09, 0.13, 0.15, 0.21, 0.26, 0.32, 0.4, 0.52, 0.69, 0.93], 'RegressionY': [0.1, 0.05, 0.37, 0.21, 0.11, 0.37, 0.68, 0.5, 0.94, 0.75]}, 'TEP_Data': {'TEP_AdvancingX': [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0], 'TEP_AdvancingY': [0.1, 0.05, 0.37, 0.21, 0.11, 0.37, 0.68, 0.5, 0.94, 0.75], 'SurveysX': [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0], 'SurveysY': [10, 20, 19, 19, 19, 19, 19, 18, 18, 16], 'RegressionX': [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0], 'RegressionY': [0.1, 0.05, 0.37, 0.21, 0.11, 0.37, 0.68, 0.5, 0.94, 0.75]}, 'Surveys': {'Countries': '177', 'Total_Surveys': '11', 'Avg_Advance': '0.4124293785310734'}, 'About': {'Website': 'http://sustainsocieties.com/WAOH', 'REST_Server': 'See WAOH Announcements', 'Copywrite': 'http://TransitionEconomics.info'}}"

I’ve tried …

var getData = $.get('/data', {'ind_id':'1'});

        getData.done(function(result){
           alert(result.result.X)
           alert(result.X)
           alert(result.result)

My question is, how best to plot the data: values and labels: to a jquery for charting.

In the example above, jresult[TEP_Data][TEP_AdvancingY] = [0.1, 0.05, 0.37, 0.21, 0.11, 0.37, 0.68, 0.5, 0.94, 0.75] = should work but does not.

If I try returning JSON data in python, this doesn’t work (JSON validates and I can extract the data in a Jupyter Notebook or other – but not here in Flask …

Y = jresult["TEP_Data"]["TEP_AdvancingY"] # should return [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0]
X = jresult["TEP_Data"]["TEP_AdvancingX"] # should return [0.1, 0.05, 0.37, 0.21, 0.11, 0.37, 0.68, 0.5, 0.94, 0.75]
return jsonify(Y=Y, X=X)

When the json values are passed correctly, I will return it via jsonify(results) to a jquery charting label: or values: field as follows:

  var lineChartData = {   
        labels: [ result.X ]
             #['January', 'February', 'March', 'April', 'May', 'June', 'July'],
             #[1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0]
        datasets: [{
          data: [   
            result.Y
            # [0.1, 0.05, 0.37, 0.21, 0.11, 0.37, 0.68, 0.5, 0.94, 0.75]
          ]
        }
window.onload = function() {

  // document.getElementById('ind_data').addEventListener('click', function() {

    var ctx = document.getElementById('canvas').getContext('2d');
    window.myLine = Chart.Line(ctx, {
      data: lineChartData
      }
    });
  };
    });

I’m new to python, jquery, and json so this mix of python objects and json objects is brutal and all internet tutorials for MySQL work seem to be php-based.

Leave a Reply

Your email address will not be published. Required fields are marked *