Google Charts API with SQL data in Python

Question

I have looked over the other questions addressing this, but they don't answer my questions specifically. I am trying to use data returned from SQL in Flask, and passing it to the Google Charts API. It's a basic weight/calorie tracker, and I need to return date and weight for the chart. My userdata for one user returns from SQL query

db.execute("SELECT * FROM history WHERE id = :id", id=session["user_id"]):

[{'id': 1, 'weight': 180, 'calories': 2100, 'rewards': 1, 'date': '2018-04-14'}, {'id': 1, 'weight': 185, 'calories': 1800, 'rewards': -1, 'date': '2018-04-15'}, {'id': 1, 'weight': 180, 'calories': 1600, 'rewards': 1, 'date': '2018-04-13'}, {'id': 1, 'weight': 180, 'calories': 1900, 'rewards': -2, 'date': '2018-04-12'}, {'id': 1, 'weight': 186, 'calories': 1111, 'rewards': 5, 'date': '2018-04-16'}, {'id': 1, 'weight': 184, 'calories': 1200, 'rewards': 5, 'date': '2018-04-17'}, {'id': 1, 'weight': 184, 'calories': 1400, 'rewards': 3, 'date': '2018-04-18'}, {'id': 1, 'weight': 180, 'calories': 2100, 'rewards': -4, 'date': '2018-04-11'}]

I am not familiar with PHP which I see pop up often, but I am OK with Python, and just learning JavaScript. It appears I need to jsonify the data to pass, but I'm not sure how to do that. I read through the Google Charts documentation, but I'm still not quite sure how to pass this info into the chart code.

Any help you can give is MUCH appreciated!

*************Edited with updated code************* OK, I think I have the data formatted correctly, and returning json.dumps(). I can get the Google Charts to display a chart with manually entered data in the script, but when I pass the variable to use I get undefined: Request could not be proxied! error in Chrome when I try to access the page. Here is a larger snippet of my code, I hope it helps. (I'm new to programming, so please keep that in mind :/)

Python route - this returns what I initially put: @app.route("/charting", methods=["GET", "POST"]) @login_required def charting():

rows = db.execute("SELECT * FROM history WHERE id = :id", id=session["user_id"])

jsonData = (json.dumps(rows))

return render_template("charting.html"), jsonData

JavaScript - works with manually entered data, but doesn't work with the passed variable .done(function (jsonData):

  // get data
  $.ajax({
    url: '/charting',
    dataType: 'json'
  }).done(function ({{ jsonData }}) {
  });

  // load json data
  function loadData(jsonData) {
    $.each(jsonData, function(index, row) {
      data.addRow([
        row.date,
        row.weight,
        row.calories
      ]);
    });
    drawChart();
  }

Am I passing the variable correctly? I should be passing the JSON version of the data, and it should parse everything according to the data.addcolumn descriptors.

Answer 1

use separate files for your python and html / javascript...

in python, for the data you posted above,
use json.dumps to json encode the data,
then return / print it to the page...

import json

... db code ...

return json.dumps(dataFromQuery)

see JSON encoder and decoder

---

in html, use jquery ajax to get data from python...

$.ajax({
  url: 'path to python',
  dataType: 'json'
}).done(function (jsonData) {
  // jsonData is what python returns
});

---

in order to create a google data table directly from json,
the json must be in a specific format, found here...
Format of the Constructor's JavaScript Literal data Parameter

if you can change the python to format the data accordingly,
you can create the chart's data table like so...

var data = new google.visualization.DataTable(jsonData);

otherwise, you will you need to load the data table manually,
something like this...

var data = new google.visualization.DataTable();
data.addColumn('string', 'Date');
data.addColumn('number', 'Weight');
data.addColumn('number', 'Calories');

$.each(jsonData, function(index, row) {
  data.addRow([
    row.date,
    row.weight,
    row.calories
  ]);
});

---

see following working snippet,
since the path to python doesn't exist,
the example will use hard-coded data,
which can be removed...

google.charts.load('current', {
  packages: ['corechart']
}).then(function () {
  // create chart
  var container = $('#chart_div').get(0);
  var chart = new google.visualization.LineChart(container);
  var options = {
    legend: {
      position: 'top'
    }
  };

  // create data table
  var data = new google.visualization.DataTable();
  data.addColumn('string', 'Date');
  data.addColumn('number', 'Weight');
  data.addColumn('number', 'Calories');

  // get data
  $.ajax({
    url: 'path to python',
    dataType: 'json'
  }).done(function (jsonData) {
    loadData(jsonData);
  }).fail(function (jqXHR, textStatus, errorThrown) {
    var jsonData = [{'id': 1, 'weight': 180, 'calories': 2100, 'rewards': 1, 'date': '2018-04-14'}, {'id': 1, 'weight': 185, 'calories': 1800, 'rewards': -1, 'date': '2018-04-15'}, {'id': 1, 'weight': 180, 'calories': 1600, 'rewards': 1, 'date': '2018-04-13'}, {'id': 1, 'weight': 180, 'calories': 1900, 'rewards': -2, 'date': '2018-04-12'}, {'id': 1, 'weight': 186, 'calories': 1111, 'rewards': 5, 'date': '2018-04-16'}, {'id': 1, 'weight': 184, 'calories': 1200, 'rewards': 5, 'date': '2018-04-17'}, {'id': 1, 'weight': 184, 'calories': 1400, 'rewards': 3, 'date': '2018-04-18'}, {'id': 1, 'weight': 180, 'calories': 2100, 'rewards': -4, 'date': '2018-04-11'}];
    loadData(jsonData);
  });

  // load json data
  function loadData(jsonData) {
    $.each(jsonData, function(index, row) {
      data.addRow([
        row.date,
        row.weight,
        row.calories
      ]);
    });
    drawChart();
  }

  // draw chart
  $(window).resize(drawChart);
  function drawChart() {
    chart.draw(data, options);
  }
});

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>