一位朋友说上一篇博客上返回的数据不好取，直接返回json格式的数据会更好，于是就有了下面这篇，原理和上一篇相同
test_db.py创建虚拟数据的文件
# coding=utf-8
import sqlite3
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
# 连接数据库
conn = sqlite3.connect('test.db')
conn.text_factory = str
c = conn.cursor()
# 创建表地图表
c.execute('CREATE TABLE geoCoordMap (name text, map decimal(12,8), conver int)')
convervalue = [("海门",'121.15,31.89', 9 ),
("招远",'120.38,37.35', 10 ),
("舟山",'122.207216,29',10 ),
("盐城",'120.13,33.38', 10 ),
("赤峰",'118.87,42.28', 10 ),
("青岛",'120.33,36.07', 10 ),
("乳山",'121.52,36.89', 10 ),
("金昌",'102.188043,38',10 ),
("泉州",'118.58,24.93', 10 ),
("莱西",'120.53,36.86',109 ),
("日照",'119.46,35.42',109 ),
("胶南",'119.97,35.88', 39 ),
("南通",'121.05,32.08', 39 ),
("拉萨",'91.11,29.97', 39 ),
("云浮",'112.02,22.93', 39 ),
("梅州",'116.1,24.55', 39 ),
("文登",'122.05,37.2', 39 ),
("上海",'121.48,31.22', 12),
("威海",'122.1,37.5', 12),
("承德",'117.93,40.97', 12),
("厦门",'118.1,24.46', 12),
("汕尾",'115.375279,22',12),
("潮州",'116.63,23.68', 12),
("丹东",'124.37,40.13', 12),
("太仓",'121.1,31.45', 12),
("曲靖",'103.79,25.51', 12),
("烟台",'121.39,37.52', 12),
("福州",'119.3,26.08', 12),
("即墨",'120.45,36.38', 12),
("抚顺",'123.97,41.97', 12),
("玉溪",'102.52,24.35', 12),
("阳泉",'113.57,37.85', 12),
("莱州",'119.942327,37',12),
("湖州",'120.1,30.86', 12),
("汕头",'116.69,23.39', 12),
("昆山",'120.95,31.39', 12),
("宁波",'121.56,29.86', 12),
("湛江",'110.359377,21',12),
("揭阳",'116.35,23.55', 12),
("荣成",'122.41,37.16', 12),
("常熟",'120.74,31.64', 12),
("东莞",'113.75,23.04', 12),
("河源",'114.68,23.73', 12),
("淮安",'119.15,33.5', 12),
("泰州",'119.9,32.49', 12),
("南宁",'108.33,22.84', 12),
("营口",'122.18,40.65', 12),
("惠州",'114.4,23.09', 12),
("江阴",'120.26,31.91', 12),
("蓬莱",'120.75,37.8', 12),
("韶关",'113.62,24.84', 12),
("广州",'113.23,23.16', 12),
("延安",'109.47,36.6', 12),
("太原",'112.53,37.87', 12),
("清远",'113.01,23.7', 12),
("中山",'113.38,22.52', 12),
("昆明",'102.73,25.04', 12),
("寿光",'118.73,36.86', 12),
("盘锦",'122.070714,41',12),
("长治",'113.08,36.18', 12),
("深圳",'114.07,22.62', 12),
("珠海",'113.52,22.3', 12),
("宿迁",'118.3,33.96', 12),
("咸阳",'108.72,34.36', 12),
("铜川",'109.11,35.09', 12),
("平度",'119.97,36.77', 12),
("佛山",'113.11,23.05', 12),
("海口",'110.35,20.02', 12),
("江门",'113.06,22.61', 12),
("章丘",'117.53,36.72', 12),
("肇庆",'112.44,23.05', 12),
("大连",'121.62,38.92', 12),
("临汾",'111.5,36.08', 12),
("吴江",'120.63,31.16', 12),
("沈阳",'123.38,41.8', 12),
("苏州",'120.62,31.32', 12),
("茂名",'110.88,21.68', 12),
("嘉兴",'120.76,30.77', 12),
("长春",'125.35,43.88', 12),
("胶州",'120.03336,36', 12),
("银川",'106.27,38.47', 12),
("锦州",'121.15,41.13', 12),
("南昌",'115.89,28.68', 12),
("柳州",'109.4,24.33', 12),
("三亚",'109.511909,18',12),
("自贡",'104.778442,29',12),
("吉林",'126.57,43.87', 12),
("阳江",'111.95,21.85', 12),
("泸州",'105.39,28.91', 12),
("西宁",'101.74,36.56', 12),
("宜宾",'104.56,29.77', 12),
("成都",'104.06,30.67', 12),
("大同",'113.3,40.12', 12),
("镇江",'119.44,32.2', 12),
("桂林",'110.28,25.29', 12),
("宜兴",'119.82,31.36', 12),
("北海",'109.12,21.49', 12),
("西安",'108.95,34.27', 12),
("金坛",'119.56,31.74', 12),
("东营",'118.49,37.46', 12),
("遵义",'106.9,27.7', 12),
("绍兴",'120.58,30.01', 12),
("扬州",'119.42,32.39', 12),
("常州",'119.95,31.79', 12),
("潍坊",'119.1,36.62', 12),
("重庆",'106.54,29.59', 12),
("台州",'121.420757,28',12),
("南京",'118.78,32.04', 12),
("滨州",'118.03,37.36', 12),
("贵阳",'106.71,26.57', 12),
("无锡",'120.29,31.59', 12),
("本溪",'123.73,41.3', 12),
("渭南",'109.5,34.52', 12),
("宝鸡",'107.15,34.38', 12),
("焦作",'113.21,35.24', 12),
("句容",'119.16,31.95', 12),
("北京",'116.46,39.92', 12),
("徐州",'117.2,34.26', 12),
("衡水",'115.72,37.72', 12),
("包头",'110,40.58', 12),
("绵阳",'104.73,31.48', 12),
("枣庄",'117.57,34.86', 12),
("杭州",'120.19,30.26', 12),
("淄博",'118.05,36.78', 12),
("鞍山",'122.85,41.12', 12),
("溧阳",'119.48,31.43', 12),
("安阳",'114.35,36.1', 12),
("开封",'114.35,34.79', 12),
("济南",'117,36.65', 12),
("德阳",'104.37,31.13', 12),
("温州",'120.65,28.01', 12),
("九江",'115.97,29.71', 12),
("邯郸",'114.47,36.6', 12),
("临安",'119.72,30.23', 12),
("兰州",'103.73,36.03', 12),
("沧州",'116.83,38.33', 12),
("临沂",'118.35,35.05', 12),
("南充",'106.110698,30',12),
("天津",'117.2,39.13', 12),
("富阳",'119.95,30.07', 12),
("泰安",'117.13,36.18', 12),
("诸暨",'120.23,29.71', 12),
("郑州",'113.65,34.76', 12),
("聊城",'115.97,36.45', 12),
("芜湖",'118.38,31.33', 12),
("唐山",'118.02,39.63', 12),
("邢台",'114.48,37.05', 12),
("德州",'116.29,37.45', 12),
("济宁",'116.59,35.38', 12),
("荆州",'112.239741,30',12),
("宜昌",'111.3,30.7', 12),
("义乌",'120.06,29.32', 12),
("丽水",'119.92,28.45', 12),
("洛阳",'112.44,34.7', 12),
("株洲",'113.16,27.83', 12),
("莱芜",'117.67,36.19', 12),
("常德",'111.69,29.05', 12),
("保定",'115.48,38.85', 12),
("湘潭",'112.91,27.87', 12),
("金华",'119.64,29.12', 12),
("岳阳",'113.09,29.37', 12),
("长沙",'113,28.21', 12),
("衢州",'118.88,28.97', 12),
("廊坊",'116.7,39.53', 2),
("菏泽",'115.480656,35',21),
("合肥",'117.27,31.86',21 ),
("武汉",'114.31,30.52',21 ),
("大庆",'125.03,46.58',21 )]
# 插入数据
c.executemany('INSERT INTO geoCoordMap VALUES (?,?,?)', convervalue)
# 提交执行
conn.commit()
# 关闭数据库连接
conn.close()
运行程序app.py
# coding=utf-8
import sqlite3
from flask import Flask,render_template,json,jsonify,request
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
app = Flask(__name__)
def get_db():
db = sqlite3.connect('test.db')
return db
def query_db(query, args=(), one=False):
db = get_db()
cur = db.execute(query, args)
db.commit()
rv = cur.fetchall()
db.close()
return (rv[0] if rv else None) if one else rv
@app.route("/", methods=["GET"])
def index():
return render_template("index.html")
@app.route("/map", methods=["GET"])
def air_map():
if request.method == "GET":
res = query_db("select * from geoCoordMap")
citys = []
# 这里需要先设置一个空数组，将sql返回的数据进行逐个存到这个数组里面，在将结果转化为json格式
for r in res:
city_map = {}
city_map['name'] = r[0]
city_map['map'] = r[1]
city_map['value'] = r[2]
citys.append(city_map)
return jsonify(citys)
这里启动服务，访问127.0.0.1:5000/map,返回的数据是数组中的json串
这样更加方便前端获取有效数据
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>ECharts3 Ajax</title>
<script src="{{ url_for('static', filename='jquery-3.2.1.js') }}"></script>
<script src="{{ url_for('static', filename='echarts.js') }}"></script>
<script src="{{ url_for('static', filename='china.js') }}"></script>
</head>
<body>
<div id="main" style=" 1000px;height:600px;"></div>
<script type="text/javascript">
var myChart = echarts.init(document.getElementById('main'));
var mapData = [];
var geoCoordMap={};
$.get('/map').done(function (data){
// console.log(data);
if(data){
data.forEach(function (obj) {
var dataObj = {};
dataObj.name = obj.name;
dataObj.value = obj.value;
geoCoordMap[obj.name] = obj.map.split(',');
mapData.push(dataObj)
});
}
// console.log(mapData);
// console.log(geoCoordMap);
var convertData = function (data) {
var res = [];
for (var i = 0; i < data.length; i++) {
var geoCoord = geoCoordMap[data[i].name];
if (geoCoord) {
res.push({
name: data[i].name,
value: geoCoord.concat(data[i].value)
});
}
}
return res;
};
option = {
backgroundColor: '#404a59',
title: {
text: '全国主要城市空气质量',
subtext: 'data from PM25.in',
sublink: 'http://www.pm25.in',
left: 'center',
textStyle: {
color: '#fff'
}
},
tooltip : {
trigger: 'item'
},
legend: {
orient: 'vertical',
y: 'bottom',
x:'right',
data:['pm2.5'],
textStyle: {
color: '#fff'
}
},
geo: {
map: 'china',
label: {
emphasis: {
show: false
}
},
roam: true,
itemStyle: {
normal: {
areaColor: '#323c48',
borderColor: '#111'
},
emphasis: {
areaColor: '#2a333d'
}
}
},
series : [
{
name: 'pm2.5',
type: 'scatter',
coordinateSystem: 'geo',
data: convertData(mapData),
symbolSize: function (val) {
return val[2] / 10;
},
label: {
normal: {
formatter: '{b}',
position: 'right',
show: false
},
emphasis: {
show: true
}
},
itemStyle: {
normal: {
color: '#ddb926'
}
}
},
{
name: 'Top 5',
type: 'effectScatter',
coordinateSystem: 'geo',
data: convertData(mapData.sort(function (a, b) {
return b.value - a.value;
}).slice(0, 6)),
symbolSize: function (val) {
return val[2] / 10;
},
showEffectOn: 'render',
rippleEffect: {
brushType: 'stroke'
},
hoverAnimation: true,
label: {
normal: {
formatter: '{b}',
position: 'right',
show: true
}
},
itemStyle: {
normal: {
color: '#f4e925',
shadowBlur: 10,
shadowColor: '#333'
}
},
zlevel: 1
}
]
};
myChart.setOption(option);
});
</script>
</body>
</html>