python flask 数据可视化_python数据可视化之flask+echarts（二）

一位朋友说上一篇博客上返回的数据不好取，直接返回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

ECharts3 Ajax

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);

});