I am having difficulty getting my JSON data fitting into the C3.JS charts properly.
I am querying my database as such:
def mysqlconnect():
conn = pymysql.connect(
host='localhost',
user='dylan',
password='cookies',
db="nano_detections",
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = conn.cursor()
cursor.execute("Select SPECIES, COUNT(*) FROM detections GROUP BY species")
output = cursor.fetchall()
conn.close()
return output
then I am creating the json object as such
output = mysqlconnect()
print(f"json: {json.dumps(output)}")
with open('static/json_data.json', 'w') as outfile:
outfile.write(json.dumps(output, indent=4))
outfile.close()
json_file = json.dumps(output, indent=4)
this is then sent to the function call in Flask
@app.route("/", methods=['GET'])
def home():
return render_template("index.html", json_file=json_file)
Here is a sample of the json file:
[
{
"SPECIES": "WHITE TAILED TROPIC",
"COUNT(*)": 5920
},
{
"SPECIES": "TRUMPTER SWAN",
"COUNT(*)": 10512
},
{
"SPECIES": "CASPIAN TERN",
"COUNT(*)": 1556
},
…..
{
"SPECIES": "SUPERB STARLING",
"COUNT(*)": 1
},
{
"SPECIES": "WALL CREAPER",
"COUNT(*)": 4
},
{
"SPECIES": "WILD TURKEY",
"COUNT(*)": 1
},
{
"SPECIES": "BLACK TAIL CRAKE",
"COUNT(*)": 1
}]
Where I am trying to run it as
<html>
<head>
Hello
<script src="https://unpkg.com/[email protected]/dist/d3.min.js"></script>
<link href="https://unpkg.com/[email protected]/c3.min.css" rel="stylesheet">
<script src="https://unpkg.com/[email protected]/c3.min.js"></script>
</head>
<body>
<div id="chart"></div>
<script>
var json_data = '{{json_file}}';
var chart = c3.generate({
data: {
type: 'bar',
json: json_data,
keys: {
x: 'SPECIES',
value: ['COUNT(*)']
}
},
axis: {
x: {
type: 'category'
}
},
bar: {
width: {
ratio: 0.5
}
}
});
console.log(json_data);
</script>
</body>
</html>
My confusion is why does my attempt to load json_file not work but the following code does work.
var chart = c3.generate({
data: {
type: 'bar',
json: [
{ 'SPECIES': 'X', 'COUNT(*)': 5920 },
{ 'SPECIES': 'Y', 'COUNT(*)': 10512 },
{ 'SPECIES': 'Z', 'COUNT(*)': 1556 }
],
keys: {
x: 'SPECIES',
value: ['COUNT(*)']
}
},
axis: {
x: {
type: 'category'
}
},
bar: {
width: {
ratio: 0.5
}
}
});
