1：后台连接数据库创建session对象
2：创建表关系映射
3：查询数据
4：将数据封装成特定格式（json）
5：前台通过ajax请求指定路由异步加载数据并在地图上展示
先来看一下效果
地图参考：https://gallery.echartsjs.com/editor.html?c=map-china-dataRange
连接数据库：sqlalchemy
from flask import Flask, render_template, jsonify
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from config import *
config 中保存的是数据库的url
CONN = 'mysql://root:root@127.0.0.1:3306/csv_change_mysql?charset=utf8'
1：连接数据库并创建session对象
Base = declarative_base()
engine = create_engine(CONN)
Session = sessionmaker(bind=engine)
session = Session()
2：创建表关系映射
我的数据库结构为：
表名为test,id字段没什么用主要是因为在使用使用sqlalchemy创建映时必须指定一个主键否则会报错
详情请看我的另一篇博客：
创建映射代码：
class Test(Base):
__tablename__ = 'test'
id = Column(Integer,primary_key=True,autoincrement=True)
job = Column(String(16))
low_salary = Column(Integer)
high_salary = Column(Integer)
location = Column(String(5))
3：查询数据
我需要的是地点与薪资之间的关系所以只需要这两列即可
sql：select avg(high_salary),max(high_salary),min(high_salary),count(id),location from test group by location;
查询出最高薪资、最低薪资、平均薪资、地点、以及职位数
执行sql语句
recruits = session.execute(sql)
4：封装数据：
我需要的数据格式
因此需要将查询结果封装成指定格式数据
def query_data():
returnData = {}
sql = 'select avg(high_salary),max(high_salary),min(high_salary),count(id),location from test group by location;'
recruits = session.execute(sql)
x = []
for recruit in recruits:
x.append(recruit)
two_tuple = tuple(x)#二维数组
print(two_tuple)
avg_salary = []
max_salary = []
min_salary = []
count_job = []
for item in two_tuple:
location = item[4]
avg_salary.append({'name': location, 'value': round(item[0], 0)})
max_salary.append({'name': location, 'value': round(item[1], 0)})
min_salary.append({'name': location, 'value': round(item[2], 0)})
count_job.append({'name': location, 'value': round(item[3], 0)})
#加一个状态码让前台可以判断是否得到了数据
if two_tuple:
returnData['status'] = 1
else:
returnData['status'] = 0
returnData['avg_salary'] = avg_salary
returnData['max_salary'] = max_salary
returnData['min_salary'] = min_salary
returnData['count_job'] = count_job
return jsonify(returnData)
到了这后台代码基本完成了
使用json.dumps()可以使用ensure_ascii=False指定
使用jsonify可以通过设置app.config[‘JSON_AS_ASCII’] = False来显示中文
5：前台请求数据
①：导入所需的库
jquery
echarts
百度地图扩展
百度开发者中心申请的apikey
echarts地图都是基于百度地图显示的如果你没有一个百度地图的ak的话地图是无法显示的
如果你没有百度地图的ak请先申请一个步骤也很简单请参考：
引入代码：
<!--echarts-->
<script type="text/javascript" src="http://echarts.baidu.com/gallery/vendors/echarts/echarts.min.js"></script>
<!--jquery-->
<script src="https://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
<!--map-->
<script type="text/javascript" src="http://echarts.baidu.com/gallery/vendors/echarts/map/js/china.js"></script>
<!--ak-->
<scripttype="text/javascript"src="https://api.map.baidu.com/apiv=2.0&ak=你申请的ak"></script>
②：ajax异步加载数据
$(function () {
$.ajax({
type:'post',
url:'/query',
dataType:'json',
success:function (returnData) {
console.log(returnData);
if (returnData.status == 1){
//option中是地图的相关配置
option = {}
}
var dom = document.getElementById("container");
var myChart = echarts.init(dom);
myChart.setOption(option);
}
})
});
后台要允许post请求否则会报404
@app.route('/query',methods=['POST'])
def query_data():
还有一个细节就是地图的tooltip显示问题需要在option中配置代码为
tooltip: {
trigger: 'item',
formatter: function(params) {
var res = params.name+'<br/>';
var myseries = option.series;
for (var i = 0; i < myseries.length; i++) {
for(var j=0;j<myseries[i].data.length;j++){
if(myseries[i].data[j].name==params.name){
res+=myseries[i].name +' : '+myseries[i].data[j].value+'</br>';
}
}
}
return res;
}
}
最后放上完整代码请大家酌情参考
后台：
from flask import Flask, render_template, jsonify
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from config import *
import json
Base = declarative_base()
engine = create_engine(CONN)
Session = sessionmaker(bind=engine)
session = Session()
class Test(Base):
__tablename__ = 'test'
id = Column(Integer,primary_key=True,autoincrement=True)
job = Column(String(16))
low_salary = Column(Integer)
high_salary = Column(Integer)
location = Column(String(5))
app = Flask(__name__)
app.config['JSON_AS_ASCII'] = False
@app.route('/query',methods=['POST'])
def query_data():
returnData = {}
sql = 'select avg(high_salary),max(high_salary),min(high_salary),count(id),location from test group by location;'
recruits = session.execute(sql)
x = []
for recruit in recruits:
x.append(recruit)
two_tuple = tuple(x)#二维数组
avg_salary = []
max_salary = []
min_salary = []
count_job = []
for item in two_tuple:
location = item[4]
avg_salary.append({'name': location, 'value': round(item[0], 0)})
max_salary.append({'name': location, 'value': round(item[1], 0)})
min_salary.append({'name': location, 'value': round(item[2], 0)})
count_job.append({'name': location, 'value': round(item[3], 0)})
#加一个状态码让前台可以判断是否得到了数据
if two_tuple:
returnData['status'] = 1
else:
returnData['status'] = 0
returnData['avg_salary'] = avg_salary
returnData['max_salary'] = max_salary
returnData['min_salary'] = min_salary
returnData['count_job'] = count_job
return jsonify(returnData)
@app.route('/')
def hello_world():
return render_template('testView.html')
if __name__ == '__main__':
app.run()
前台页面：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<div id="container" style="width: 600px; height:400px; margin: 0 auto;top: 100px"></div>
<script src="{{ url_for('static',filename='js/jquery-1.2.6.pack.js') }}"></script>
<script src="{{ url_for('static',filename='js/echarts.js') }}"></script>
<script src="{{ url_for('static',filename='js/China.js') }}"></script>
<script type="text/javascript">
$(function () {
$.ajax({
type:'post',
url:'/query',
dataType:'json',
success:function (returnData) {
console.log(returnData);
if (returnData.status == 1){
option = {
title: {
text: '大数据行业地点与薪资关系',
subtext: 'Test数据',
left: 'center'
},
tooltip: {
trigger: 'item',
formatter: function(params) {
var res = params.name+'<br/>';
var myseries = option.series;
for (var i = 0; i < myseries.length; i++) {
for(var j=0;j<myseries[i].data.length;j++){
if(myseries[i].data[j].name==params.name){
res+=myseries[i].name +' : '+myseries[i].data[j].value+'</br>';
}
}
}
return res;
}
},
legend: {
orient: 'vertical',
left: 'left',
data:['最大薪资(k)','最低薪资(k)','平均薪资(k)','总职位数(个)']
},
visualMap: {
min: 0,
max: 200,
left: 'left',
top: 'bottom',
text: ['高','低'], // 文本，默认为数值文本
calculable: true
},
toolbox: {
show: true,
orient: 'vertical',
left: 'right',
top: 'center',
feature: {
dataView: {readOnly: false},
restore: {},
saveAsImage: {}
}
},
series: [
{
name: '最大薪资(k)',
type: 'map',
mapType: 'china',
roam: false,
tooltip:{
show:true
},
label: {
normal: {
show: true
},
emphasis: {
show: true
}
},
data:returnData.max_salary
},
{
name: '最低薪资(k)',
type: 'map',
mapType: 'china',
tooltip:{
show:true
},
label: {
normal: {
show: true
},
emphasis: {
show: true
}
},
data:returnData.min_salary
},
{
name: '平均薪资(k)',
type: 'map',
mapType: 'china',
tooltip:{
show:true
},
label: {
normal: {
show: true
},
emphasis: {
show: true
}
},
data:returnData.avg_salary
}, {
name: '总职位数(个)',
type: 'map',
mapType: 'china',
tooltip:{
show:true
},
label: {
normal: {
show: true
},
emphasis: {
show: true
}
},
data:returnData.count_job
}
]
};
}
var dom = document.getElementById("container");
var myChart = echarts.init(dom);
myChart.setOption(option);
}
})
});
</script>
</body>
</html>
注：地图加载不出来，可能是js文件顺序问题，china.js要放大echarts.js后面。