title: Charting JSON data from Flask and MySQL
body: <p>I've stored JSON data into a MySQL database and now I need to pull specific elements out as needed for use with Chartjs (<a href="https://www.chartjs.org/docs/latest/getting-started/" rel="nofollow noreferrer">https://www.chartjs.org/docs/latest/getting-started/</a>)</p>
  
  <pre><code>&lt;link rel="stylesheet" href="/static/assets/css/Chart.min.css"&gt;
  &lt;script type="text/javascript" src="/static/assets/js/plugins/Chart.min.js"&gt;&lt;/script&gt;
  
  &lt;canvas id="canvas" height="300"&gt;&lt;/canvas&gt;
  </code></pre>
  
  <p>MySQL's cursor class is set to "DictCursor" in Flask so that it returns python lists instead of tuples -</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>This returns the following data JSON string to jquery, but I cannot use jquery to parse the json info in Javascript:</p>
  
  <pre><code>"{'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'}}"
  </code></pre>
  
  <p>I've tried ...</p>
  
  <pre><code>var getData = $.get('/data', {'ind_id':'1'});
  
          getData.done(function(result){
             alert(result.result.X)
             alert(result.X)
             alert(result.result)
  </code></pre>
  
  <p>My question is, how best to plot the data: values and labels: to a jquery  for charting. </p>
  
  <p>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.</p>
  
  <p>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 ...</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>When the json values are passed correctly, I will return it via jsonify(results) to a jquery charting label: or values: field as follows:</p>
  
  <pre><code>  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
        }
      });
    };
      });
  </code></pre>
  
  <p>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.</p>