数据可视化方向的毕业设计详细思路

距离我本科答辩顺利通过已经过去十几天了，我决定把本科阶段最后的小成果做个总结分享给想做此方向项目的小伙伴们，希望能让你们想在动手实操时有项目可供参考，有实现思路可供学习，演示视频先呈现给大家。

一、研究目的及意义

（一）现状

应届毕业生关注重点难点：找工作+租房子
招聘网站繁杂
各个大学的就业信息网站成熟
租房网站众多

（二）缺点

仅提供信息，功能单一
信息分散，无法了解整体情况
文字、数字形式不直观
招聘与租房无关联

（三）改进

整合信息、统计数据
分区域数据可视化
丰富的图表呈现
集招聘租房于一体

因此，当下迫切需要一个能够把尽可能多的信息整合到一起的平台，且该平台需要具备强大的统计数据及数据可视化的功能，这样，用户就可以通过该平台来检索招聘信息及房源信息，并且可以通过图表可视化了解整体情况。对于每年日益增多的就业大军而言，可以从该系统中清楚了解到目前在一线城市、新一线城市、二线城市的互联网各行业及租房现状，有助于做出适合自身情况的选择。

二、实现思路与相关技术

在这里插入图片描述

前后端数据交互的实现——ajax技术

通过ajax传递参数，它是用户和服务器之间的一个中间层，使得用户操作和服务器响应异步化，前端将需要传递的参数转化为JSON字符串（json.stringify）再通过get/post方式向服务器发送一个请求并将参数直接传递给后台，后台对前端请求作出反应，接收数据，将数据作为条件进行查询，返回json字符串格式的查询结果集给前端，前端接收到后台返回的数据进行条件判断并做出相应的页面展示。

get/post请求方式的区别

都是向服务器提交数据，都能从服务器获取数据。Get方式的请求，浏览器会把响应头和数据体一并发送出去，服务器响应200表示请求已成功，返回数据。Post方式的请求，浏览器会先发送响应头，服务器响应100后，浏览器再发送数据体，服务器响应200，请求成功，返回数据。Post方式安全性更好。

什么时候用get，什么时候用post请求方式？

登录注册、修改信息部分都使用post方式，数据概况展示、可视化展示部分都使用get方式。也就是数据查询用get方式，数据增加、删除、修改用post方式更加安全。

数据可视化图表展示——ECharts图表库（包含所有想要的图表生成代码，可支持在线调试代码，图表大气美观，此网站真的绝绝子，分享给大家）

网站：ECharts开源可视化图表库

三、系统整体功能框架

在这里插入图片描述

四、详细实现

（一）数据获取
1、获取招聘信息
2、获取租房房源信息

很抱歉，由于爬虫部分涉及具体网站造成侵权行为，故删除此部分代码

（二）数据库

需要创建三张数据库表分别用来存储招聘信息、房源信息、和用户信息。
招聘信息表结构租房信息表结构用户信息表结构
将爬取到的数据存入csv文件以及本地mysql数据库中，部分数据展示如下图所示：
在这里插入图片描述

数据库与后台进行连接

使用pymysql连接本地mysql数据库，首先通过pip安装pymysql并创建好数据库以及数据库表，导入pymysql包，打开数据库连接，使用cursor()方法创建一个游标对象，使用execute()方法执行SQL查询。

（三）注册登录

注册登录流程：
在这里插入图片描述

实现代码（后端响应）：

#注册用户
@app.route('/addUser',methods=['POST'])
def addUser():#服务器端获取jsonget_json = request.get_json()name = get_json['name']password = get_json['password']conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()cursor.execute("select count(*) from `user` where `username` = '" + name + "'")count = cursor.fetchall()#该昵称已存在if (count[0][0]!= 0):table_result = {"code": 500, "msg": "该昵称已存在！"}cursor.close()else:add = conn.cursor()sql = "insert into `user`(username,password) values('"+name+"','"+password+"');"add.execute(sql)conn.commit()table_result = {"code": 200, "msg": "注册成功"}add.close()conn.close()return jsonify(table_result)
#用户登录
@app.route('/loginByPassword',methods=['POST'])
def loginByPassword():get_json = request.get_json()name = get_json['name']password = get_json['password']conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()cursor.execute("select count(*) from `user` where `username` = '" + name +"' and password = '" + password+"';")count = cursor.fetchall()if(count[0][0] != 0):table_result = {"code": 200, "msg": name}cursor.close()else:name_cursor = conn.cursor()name_cursor.execute("select count(*) from `user` where `username` = '" + name +"';")name_count = name_cursor.fetchall()#print(name_count)if(name_count[0][0] != 0):table_result = {"code":500, "msg": "密码错误！"}else:table_result = {"code":500, "msg":"该用户不存在，请先注册！"}name_cursor.close()conn.close()print(name)return jsonify(table_result)

（四）首页功能
在这里插入图片描述
（五）修改个人信息

#个人信息修改
@app.route('/updateUserInfo',methods=['POST'])
def updateUserInfo():get_json = request.get_json()name = get_json['name']print(name)email = get_json['email']content = get_json['content']address = get_json['address']phone = get_json['phone']conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()cursor.execute("update `user` set email = '"+email+"',content = '"+content+"',address = '"+address+"',phone = '"+phone+"' where username = '"+ name +"';")conn.commit()table_result = {"code": 200, "msg": "更新成功！","youxiang": email, "tel": phone}cursor.close()conn.close()print(table_result)return jsonify(table_result)

（六）修改密码
可以通过两种方式修改密码，均需要进行安全验证
在这里插入图片描述

#密码修改
@app.route('/updatePass',methods=['POST'])
def updatePass():get_json = request.get_json()name = get_json['name']oldPsw = get_json['oldPsw']newPsw = get_json['newPsw']rePsw = get_json['rePsw']conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()cursor.execute("select count(*) from `user` where `username` = '" + name + "' and password = '" + oldPsw+"';")count = cursor.fetchall()print(count[0][0])#确定昵称密码对应if (count[0][0] == 0):table_result = {"code": 500, "msg": "原始密码错误！"}cursor.close()else:updatepass = conn.cursor()sql = "update `user` set password = '"+newPsw+"' where username = '"+ name +"';"updatepass.execute(sql)conn.commit()table_result = {"code": 200, "msg": "密码修改成功！", "username": name, "new_password": newPsw}updatepass.close()conn.close()return jsonify(table_result)

（七）数据概况展示

注：仅研究互联网岗位招聘信息

以招聘数据概况为例：
在这里插入图片描述

@app.route('/data',methods=['GET'])
def data():limit = int(request.args['limit'])page = int(request.args['page'])page = (page-1)*limitconn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()if (len(request.args) == 2):cursor.execute("select count(*) from demo")count = cursor.fetchall()cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)cursor.execute("select * from demo limit "+str(page)+","+str(limit))data_dict = []result = cursor.fetchall()for field in result:data_dict.append(field)else:education = str(request.args['education'])positionName = str(request.args['positionName']).lower()if(education=='不限'):cursor.execute("select count(*) from demo where positionName like '%"+positionName+"%'")count = cursor.fetchall()cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)cursor.execute("select * from demo where positionName like '%"+positionName+"%' limit " + str(page) + "," + str(limit))data_dict = []result = cursor.fetchall()for field in result:data_dict.append(field)else:cursor.execute("select count(*) from demo where positionName like '%"+positionName+"%' and education = '"+education+"'")count = cursor.fetchall()cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)cursor.execute("select * from demo where positionName like '%"+positionName+"%' and education = '"+education+"' limit " + str(page) + "," + str(limit))data_dict = []result = cursor.fetchall()for field in result:data_dict.append(field)table_result = {"code": 0, "msg": None, "count": count[0], "data": data_dict}cursor.close()conn.close()return jsonify(table_result)

（八）招聘数据可视化
从全国、一线城市、新一线城市、二线城市四个角度分区域分析。
举个栗子，全国范围内的企业情况分析如下图所示：
在这里插入图片描述

@app.route('/qiye',methods=['GET'])
def qiye():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()cursor.execute("SELECT DISTINCT(city) from demo")result = cursor.fetchall()city = []city_result = []companySize = []companySizeResult = []selected = {}# 获取到的城市for field in result:city.append(field[0])if (len(request.args) == 0):# 没有查询条件# 获取到城市对应的个数for i in city:cursor.execute("SELECT count(*) from demo where city = '" + i + "'")count = cursor.fetchall()dict = {'value': count[0][0], 'name': i}city_result.append(dict)# 初始化最开始显示几个城市for i in city[10:]:selected[i] = False# 获取到几种公司规模cursor.execute("SELECT DISTINCT(companySize) from demo")company = cursor.fetchall()for field in company:companySize.append(field[0])# 每种公司规模对应的个数cursor.execute("SELECT count(*) from demo where companySize = '" + field[0] + "'")count = cursor.fetchall()companySizeResult.append(count[0][0])else:positionName = str(request.args['positionName']).lower()# 查询条件：某种职业# 每个城市某种职业的个数for i in city:cursor.execute("SELECT count(*) from demo where city = '" + i + "' and positionName like '%"+positionName+"%'")count = cursor.fetchall()dict = {'value': count[0][0], 'name': i}city_result.append(dict)for i in city[10:]:selected[i] = Falsecursor.execute("SELECT DISTINCT(companySize) from demo")company = cursor.fetchall()for field in company:companySize.append(field[0])cursor.execute("SELECT count(*) from demo where companySize = '" + field[0] + "' and positionName like '%"+positionName+"%'")count = cursor.fetchall()companySizeResult.append(count[0][0])result = {"city": city, "city_result": city_result, "selected": selected, "companySize": companySize, "companySizeResult": companySizeResult}cursor.close()return jsonify(result)

一线城市的企业情况分析如下图所示：
在这里插入图片描述

@app.route('/qiye_first',methods=['GET'])
def qiye_first():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()#cursor.execute("SELECT DISTINCT(city) from demo")#result = cursor.fetchall()city = ['北京', '上海', '广州', '深圳']city_result = []companySize = []companySizeResult = []selected = {}# 获取到的城市#for field in result:#city.append(field[0])if (len(request.args) == 0):# 没有查询条件# 获取到城市对应的个数for i in city:cursor.execute("SELECT count(*) from demo where city = '" + i + "'")count = cursor.fetchall()dict = {'value': count[0][0], 'name': i}city_result.append(dict)# 初始化最开始显示几个城市for i in city[4:]:selected[i] = False# 获取到几种公司规模cursor.execute("SELECT DISTINCT(companySize) from demo where city in ('北京', '上海', '广州', '深圳');")company = cursor.fetchall()for field in company:companySize.append(field[0])# 每种公司规模对应的个数cursor.execute("SELECT count(*) from demo where companySize = '" + field[0] + "' and city in ('北京', '上海', '广州', '深圳');")count = cursor.fetchall()companySizeResult.append(count[0][0])else:positionName = str(request.args['positionName']).lower()# 查询条件：某种职业# 每个城市某种职业的个数for i in city:cursor.execute("SELECT count(*) from demo where city = '" + i + "' and positionName like '%"+positionName+"%'")count = cursor.fetchall()dict = {'value': count[0][0], 'name': i}city_result.append(dict)for i in city[4:]:selected[i] = Falsecursor.execute("SELECT DISTINCT(companySize) from demo where city in ('北京', '上海', '广州', '深圳');")company = cursor.fetchall()for field in company:companySize.append(field[0])cursor.execute("SELECT count(*) from demo where companySize = '" + field[0] + "' and positionName like '%"+positionName+"%' and city in ('北京', '上海', '广州', '深圳');")count = cursor.fetchall()companySizeResult.append(count[0][0])result = {"city": city, "city_result": city_result, "selected": selected, "companySize": companySize, "companySizeResult": companySizeResult}cursor.close()return jsonify(result)

全国本科学历薪资情况分析展示如图所示：
在这里插入图片描述

@app.route('/xinzi',methods=['GET'])
def xinzi():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()positionName = ['java', 'python', 'php', 'web', 'bi', 'android', 'ios', '算法', '大数据', '测试', '运维', '数据库']#柱状图返回列表zzt_list = []zzt_list.append(['product', 'Java', 'Python', 'PHP', 'web', 'bi', 'android', 'ios', '算法', '大数据', '测试', '运维', '数据库'])if (len(request.args) == 0 or str(request.args['education'])=='不限'):temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) like '%K%' and positionName like '%"+i+"%';")count = cursor.fetchall()temp_list += count[0]zzt_list.append(['0—10K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5], temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 10 AND 20 and positionName like '%"+i+"%';")count = cursor.fetchall()temp_list += count[0]zzt_list.append(['10—20K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5], temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 20 AND 30 and positionName like '%"+i+"%';")count = cursor.fetchall()temp_list += count[0]zzt_list.append(['20—30K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5], temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 30 AND 40 and positionName like '%" + i + "%';")count = cursor.fetchall()temp_list += count[0]zzt_list.append(['30—40K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5], temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) > 40 and positionName like '%" + i + "%';")count = cursor.fetchall()temp_list += count[0]zzt_list.append(['40以上', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5], temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])else:education = str(request.args['education'])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) like '%K%' and positionName like '%" + i + "%' and education = '"+education+"'")count = cursor.fetchall()temp_list += count[0]zzt_list.append(['0—10K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5], temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 10 AND 20 and positionName like '%" + i + "%' and education = '"+education+"'")count = cursor.fetchall()temp_list += count[0]zzt_list.append(['10—20K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5], temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 20 AND 30 and positionName like '%" + i + "%' and education = '"+education+"'")count = cursor.fetchall()temp_list += count[0]zzt_list.append(['20—30K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5], temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 30 AND 40 and positionName like '%" + i + "%' and education = '"+education+"'")count = cursor.fetchall()temp_list += count[0]zzt_list.append(['30—40K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5], temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) > 40 and positionName like '%" + i + "%' and education = '"+education+"'")count = cursor.fetchall()temp_list += count[0]zzt_list.append(['40以上', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5], temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])result = {"zzt": zzt_list}cursor.close()return jsonify(result)

全国福利情况分析展示如图所示：
在这里插入图片描述

@app.route('/fuli',methods=['GET'])
def fuli():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)cursor.execute("select positionAdvantage from `demo`")data_dict = []result = cursor.fetchall()for field in result:data_dict.append(field['positionAdvantage'])content = ''.join(data_dict)positionAdvantage = []jieba.analyse.set_stop_words('./stopwords.txt')tags = jieba.analyse.extract_tags(content, topK=100, withWeight=True)for v, n in tags:mydict = {}mydict["name"] = vmydict["value"] = str(int(n * 10000))positionAdvantage.append(mydict)cursor.execute("select companyLabelList from `demo`")data_dict = []result = cursor.fetchall()for field in result:data_dict.append(field['companyLabelList'])content = ''.join(data_dict)companyLabelList = []jieba.analyse.set_stop_words('./stopwords.txt')tags = jieba.analyse.extract_tags(content, topK=100, withWeight=True)for v, n in tags:mydict = {}mydict["name"] = vmydict["value"] = str(int(n * 10000))companyLabelList.append(mydict)cursor.close()return jsonify({"zwfl": positionAdvantage, "gsfl": companyLabelList})

全国互联网岗位学历与工作经验要求情况分析展示如图所示：
在这里插入图片描述

@app.route('/xueli',methods=['GET'])
def xueli():#打开数据库连接conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')#创建一个游标对象cursorcursor = conn.cursor()#执行sql语句cursor.execute("SELECT DISTINCT(education) from demo")#获取所有记录列表result = cursor.fetchall()education = []education_data = []color_list = ['#459AF0', '#38C3B0', '#86CA5A', '#BFD44F', '	#90EE90']#获取到学历的五种情况：不限、大专、本科、硕士、博士for field in result:education.append(field[0])#获取到每种学历对应的个数for i in range(len(education)):cursor.execute("SELECT count(*) from demo where education = '" + education[i] + "'")count = cursor.fetchall()education_data.append({'value': count[0][0], 'itemStyle': {'color': color_list[i]}})cursor.execute("SELECT DISTINCT(workYear) from demo")result = cursor.fetchall()workYear = []workYear_data = []#获取到的几种工作经验for field in result:workYear.append(field[0])#获取到每种工作经验对应的个数for i in workYear:cursor.execute("SELECT count(*) from demo where workYear = '" + i + "'")count = cursor.fetchall()workYear_data.append({'value': count[0][0], 'name': i})cursor.close()return jsonify({"education":education, "education_data":education_data, "workYear_data":workYear_data})

全国互联网公司融资阶段分布情况分析如图所示：
在这里插入图片描述

@app.route('/rongzi',methods=['GET'])
def rongzi():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()cursor.execute("SELECT DISTINCT(financeStage) from demo")result = cursor.fetchall()finance = []finance_data = []# 获取到融资的几种情况for field in result:finance.append(field[0])# 获取到每种融资对应的个数for i in range(len(finance)):cursor.execute("SELECT count(*) from demo where financeStage = '" + finance[i] + "'")count = cursor.fetchall()finance_data.append({'value': count[0][0], 'name': finance[i]})cursor.close()return jsonify({"finance": finance, "finance_data": finance_data})

全国互联网公司职位类型分布情况分析展示如图所示：
在这里插入图片描述

@app.route('/poststyle',methods=['GET'])
def poststyle():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()cursor.execute("SELECT DISTINCT(firstType) from demo")result = cursor.fetchall()firstType = []firstType_data = []# 获取到职位类型的几种情况for field in result:firstType.append(field[0])# 获取到每种职位类型对应的个数for i in range(len(firstType)):cursor.execute("SELECT count(*) from demo where firstType = '" + firstType[i] + "'")count = cursor.fetchall()firstType_data.append({'value': count[0][0], 'name': firstType[i]})cursor.execute("SELECT DISTINCT(secondType) from demo")second = cursor.fetchall()secondType = []secondType_data = []# 获取到职位类型的几种情况for field in second:secondType.append(field[0])# 获取到每种职位类型对应的个数for i in range(len(secondType)):cursor.execute("SELECT count(*) from demo where secondType = '" + secondType[i] + "'")count = cursor.fetchall()secondType_data.append({'value': count[0][0], 'name': secondType[i]})cursor.close()return jsonify({"firstType": firstType, "firstType_data": firstType_data, "secondType": secondType, "secondType_data": secondType_data})

（九）九大热门城市招聘对比
在这里插入图片描述
可以通过饼图纹理展示某城市互联网公司的规模情况，漏斗图展示某城市某职位的学历要求情况等

以北京为例：

@app.route('/beijing',methods=['GET'])
def beijing():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()district = []district_result = []companySize = []companySizeResult = []education = []educationResult = []workYear = []workYear_data = []firstType = []firstType_data = []finance = []finance_data = []leida_max_dict = []# 获取到的行政区cursor.execute("SELECT DISTINCT(district) from demo where city='北京';")result = cursor.fetchall()for field in result:district.append(field[0])if (len(request.args) == 0):# 没有查询条件# 获取到行政区对应的个数for i in district:cursor.execute("SELECT count(*) from demo where district = '" + i + "';")count = cursor.fetchall()dict = {'value': count[0][0], 'name': i}district_result.append(dict)# 获取到几种公司规模cursor.execute("SELECT DISTINCT(companySize) from demo where city = '北京';")company = cursor.fetchall()for field in company:companySize.append(field[0])# 每种公司规模对应的个数cursor.execute("SELECT count(*) from demo where companySize = '" + field[0] + "' and city = '北京';")count = cursor.fetchall()dict = {'value': count[0][0], 'name': field[0]}companySizeResult.append(dict)# 获取到几种学历要求cursor.execute("SELECT DISTINCT(education) from demo where city = '北京';")eduresult = cursor.fetchall()for field in eduresult:education.append(field[0])# 每种学历要求对应的个数cursor.execute("SELECT count(*) from demo where education = '" + field[0] + "' and city = '北京';")count = cursor.fetchall()dict = {'value': count[0][0], 'name': field[0]}educationResult.append(dict)cursor.execute("SELECT DISTINCT(workYear) from demo where city = '北京';")workyear = cursor.fetchall()# 获取到的几种工作经验for field in workyear:workYear.append(field[0])# 获取到每种工作经验对应的个数for i in workYear:cursor.execute("SELECT count(*) from demo where workYear = '" + i + "' and city = '北京';")count = cursor.fetchall()workYear_data.append({'value': count[0][0], 'name': i})cursor.execute("SELECT DISTINCT(financeStage) from demo where city = '北京';")result = cursor.fetchall()# 获取到融资的几种情况for field in result:finance.append(field[0])leida_max_dict.append({'name': field[0], 'max': 300})# 获取到每种融资对应的个数for i in range(len(finance)):cursor.execute("SELECT count(*) from demo where financeStage = '" + finance[i] + "' and city = '北京';")count = cursor.fetchall()finance_data.append(count[0][0])# 职位福利cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)cursor.execute("select positionAdvantage from `demo` where city = '北京';")data_dict = []result = cursor.fetchall()for field in result:data_dict.append(field['positionAdvantage'])content = ''.join(data_dict)positionAdvantage = []jieba.analyse.set_stop_words('./stopwords.txt')tags = jieba.analyse.extract_tags(content, topK=100, withWeight=True)for v, n in tags:mydict = {}mydict["name"] = vmydict["value"] = str(int(n * 10000))positionAdvantage.append(mydict)# 职位类型cursor.execute("SELECT DISTINCT(firstType) from demo where city = '北京';")result = cursor.fetchall()# 获取到职位类型的几种情况for field in result:for i in field.keys():firstType.append(field[i])# 获取到每种职位类型对应的个数for i in range(len(firstType)):cursor.execute("SELECT count(*) from demo where firstType = '" + firstType[i] + "' and city = '北京';")count = cursor.fetchall()for field in count:for j in field.keys():value = field[j]firstType_data.append({'value': value, 'name': firstType[i]})#薪资待遇positionName = ['java', 'python', 'php', 'web', 'bi', 'android', 'ios', '算法', '大数据', '测试', '运维', '数据库']# 柱状图返回列表zzt_list = []zzt_list.append(['product', 'Java', 'Python', 'PHP', 'web', 'bi', 'android', 'ios', '算法', '大数据', '测试', '运维', '数据库'])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) like '%k%' and positionName like '%" + i + "%' and city = '北京';")count = cursor.fetchall()for i in count[0].keys():value = count[0][i]temp_list.append(value)zzt_list.append(['0—10K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5],temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 10 AND 20 and positionName like '%" + i + "%' and city = '北京';")count = cursor.fetchall()for i in count[0].keys():value = count[0][i]temp_list.append(value)zzt_list.append(['10—20K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5],temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 20 AND 30 and positionName like '%" + i + "%' and city = '北京';")count = cursor.fetchall()for i in count[0].keys():value = count[0][i]temp_list.append(value)zzt_list.append(['20—30K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5],temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 30 AND 40 and positionName like '%" + i + "%' and city = '北京';")count = cursor.fetchall()for i in count[0].keys():value = count[0][i]temp_list.append(value)zzt_list.append(['30—40K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5],temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])temp_list = []for i in positionName:cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) > 40 and positionName like '%" + i + "%' and city = '北京';")count = cursor.fetchall()for i in count[0].keys():value = count[0][i]temp_list.append(value)zzt_list.append(['40以上', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5],temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])else:positionName = str(request.args['positionName']).lower()print(positionName)# 查询条件：某种职业# 行政区for i in district:cursor.execute("SELECT count(*) from demo where district = '" + i + "' and positionName like '%"+positionName+"%';")count = cursor.fetchall()dict = {'value': count[0][0], 'name': i}district_result.append(dict)# 公司规模cursor.execute("SELECT DISTINCT(companySize) from demo where city = '北京';")company = cursor.fetchall()for field in company:companySize.append(field[0])cursor.execute("SELECT count(*) from demo where companySize = '" + field[0] + "' and positionName like '%"+positionName+"%' and city = '北京';")count = cursor.fetchall()dict = {'value': count[0][0], 'name': field[0]}companySizeResult.append(dict)# 学历要求cursor.execute("SELECT DISTINCT(education) from demo where city = '北京';")eduresult = cursor.fetchall()for field in eduresult:education.append(field[0])cursor.execute("SELECT count(*) from demo where education = '" + field[0] + "' and positionName like '%" + positionName + "%' and city = '北京';")count = cursor.fetchall()dict = {'value': count[0][0], 'name': field[0]}educationResult.append(dict)#工作经验cursor.execute("SELECT DISTINCT(workYear) from demo where city = '北京';")workyear = cursor.fetchall()for field in workyear:workYear.append(field[0])cursor.execute("SELECT count(*) from demo where workYear = '" + field[0] + "' and positionName like '%" + positionName + "%' and city = '北京';")count = cursor.fetchall()workYear_data.append({'value': count[0][0], 'name': field[0]})# 融资阶段cursor.execute("SELECT DISTINCT(financeStage) from demo where city = '北京';")result = cursor.fetchall()# 获取到融资的几种情况for field in result:finance.append(field[0])leida_max_dict.append({'name': field[0], 'max': 300})# 获取到每种融资对应的个数for i in range(len(finance)):cursor.execute("SELECT count(*) from demo where financeStage = '" + finance[i] + "' and positionName like '%" + positionName + "%' and city = '北京';")count = cursor.fetchall()finance_data.append(count[0][0])# 职位类型cursor.execute("SELECT DISTINCT(firstType) from demo where city = '北京';")result = cursor.fetchall()# 获取到职位类型的几种情况for field in result:firstType.append(field[0])# 获取到每种职位类型对应的个数for i in range(len(firstType)):cursor.execute("SELECT count(*) from demo where firstType = '" + firstType[i] + "' and positionName like '%" + positionName + "%' and city = '北京';")count = cursor.fetchall()firstType_data.append({'value': count[0][0], 'name': firstType[i]})# 职位福利cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)cursor.execute("select positionAdvantage from `demo` where city = '北京' and positionName like '%" + positionName + "%' ;")data_dict = []result = cursor.fetchall()for field in result:data_dict.append(field['positionAdvantage'])content = ''.join(data_dict)positionAdvantage = []jieba.analyse.set_stop_words('./stopwords.txt')tags = jieba.analyse.extract_tags(content, topK=100, withWeight=True)for v, n in tags:mydict = {}mydict["name"] = vmydict["value"] = str(int(n * 10000))positionAdvantage.append(mydict)# 薪资待遇positionName_sample = ['java', 'python', 'php', 'web', 'bi', 'android', 'ios', '算法', '大数据', '测试', '运维', '数据库']# 柱状图返回列表zzt_list = []zzt_list.append(['product', 'Java', 'Python', 'PHP', 'Web', 'BI', 'Android', 'ios', '算法', '大数据', '测试', '运维', '数据库'])# <10ktemp_list = []cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) like '%k%' and positionName like '%" + positionName + "%' and city = '北京';")count = cursor.fetchall()#print(count)for i in count[0].keys():value = count[0][i]print(value)for num in range(len(positionName_sample)):if positionName == positionName_sample[num]:temp_list.append(value)else:temp_list.append(0)# print(temp_list)# temp_list.append(value)zzt_list.append(['0—10K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5],temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])# 10-20ktemp_list = []cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 10 AND 20 and positionName like '%" + positionName + "%' and city = '北京';")count = cursor.fetchall()for i in count[0].keys():value = count[0][i]for num in range(len(positionName_sample)):if positionName == positionName_sample[num]:temp_list.append(value)else:temp_list.append(0)# temp_list.append(value)zzt_list.append(['10—20K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5],temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])# 20-30ktemp_list = []cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 20 AND 30 and positionName like '%" + positionName + "%' and city = '北京';")count = cursor.fetchall()for i in count[0].keys():value = count[0][i]for num in range(len(positionName_sample)):if positionName == positionName_sample[num]:temp_list.append(value)else:temp_list.append(0)#temp_list.append(value)zzt_list.append(['20—30K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5],temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])# 30-40ktemp_list = []cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) BETWEEN 30 AND 40 and positionName like '%" + positionName + "%' and city = '北京';")count = cursor.fetchall()for i in count[0].keys():value = count[0][i]for num in range(len(positionName_sample)):if positionName == positionName_sample[num]:temp_list.append(value)else:temp_list.append(0)#temp_list.append(value)zzt_list.append(['30—40K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5],temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])# >40ktemp_list = []cursor.execute("SELECT COUNT(*) FROM demo WHERE SUBSTR(salary,1,2) > 40 and positionName like '%" + positionName + "%' and city = '北京';")count = cursor.fetchall()for i in count[0].keys():value = count[0][i]for num in range(len(positionName_sample)):if positionName == positionName_sample[num]:temp_list.append(value)else:temp_list.append(0)#temp_list.append(value)zzt_list.append(['>40K', temp_list[0], temp_list[1], temp_list[2], temp_list[3], temp_list[4], temp_list[5],temp_list[6], temp_list[7], temp_list[8], temp_list[9], temp_list[10], temp_list[11]])print(zzt_list)result = {"district": district, "district_result": district_result, "companySize": companySize, "companySizeResult": companySizeResult, "education": education, "educationResult": educationResult, "workYear_data":workYear_data, "firstType": firstType, "firstType_data": firstType_data, "leida_max_dict":leida_max_dict, "cyt": positionAdvantage, "finance": finance, "finance_data": finance_data, "zzt": zzt_list}cursor.close()return jsonify(result)

（十）租房数据可视化
从全国、一线城市、新一线城市、二线城市四个角度分区域分析。
举个栗子，全国范围内的租房房屋面积情况分析如下图所示：
在这里插入图片描述

@app.route('/area',methods=['GET'])
def area():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()area_kind = ['<=20㎡', '21~40㎡', '41~60㎡', '61~80㎡', '81~100㎡', '101~120㎡', '121~140㎡', '141~160㎡', '161~180㎡', '181~200㎡']area_data = []# 获取到每种面积类别对应的个数#<=20㎡cursor.execute("SELECT count(*) from house where area between 0 and 20;")count = cursor.fetchall()area_data.append(count[0][0])#21~40㎡cursor.execute("SELECT count(*) from house where area between 21 and 40;")count = cursor.fetchall()area_data.append(count[0][0])# 41~60㎡cursor.execute("SELECT count(*) from house where area between 41 and 60;")count = cursor.fetchall()area_data.append(count[0][0])# 61~80㎡cursor.execute("SELECT count(*) from house where area between 61 and 80;")count = cursor.fetchall()area_data.append(count[0][0])# 81~100㎡cursor.execute("SELECT count(*) from house where area between 81 and 100;")count = cursor.fetchall()area_data.append(count[0][0])# 101~120㎡cursor.execute("SELECT count(*) from house where area between 101 and 120;")count = cursor.fetchall()area_data.append(count[0][0])# 121~140㎡cursor.execute("SELECT count(*) from house where area between 121 and 140;")count = cursor.fetchall()area_data.append(count[0][0])# 141~160㎡cursor.execute("SELECT count(*) from house where area between 141 and 160;")count = cursor.fetchall()area_data.append(count[0][0])# 161~180㎡cursor.execute("SELECT count(*) from house where area between 161 and 180;")count = cursor.fetchall()area_data.append(count[0][0])# 181~200㎡cursor.execute("SELECT count(*) from house where area between 181 and 200;")count = cursor.fetchall()area_data.append(count[0][0])cursor.close()print(area_data)return jsonify({"area_kind": area_kind, "area_data": area_data})

全国范围内租房房屋楼层情况分析展示如图所示：
在这里插入图片描述

@app.route('/floor',methods=['GET'])
def floor():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()cursor.execute("SELECT DISTINCT(floor) from house;")result = cursor.fetchall()floor_kind = []floor_data = []# 获取到楼层的几种情况for field in result:floor_kind.append(field[0])# 获取到每种楼层类型对应的个数for i in range(len(floor_kind)):cursor.execute("SELECT count(*) from house where floor = '" + floor_kind[i] + "'")count = cursor.fetchall()floor_data.append({'value': count[0][0], 'name': floor_kind[i]})cursor.close()return jsonify({"floor_kind": floor_kind, "floor_data": floor_data})

全国范围内租房房屋朝向情况分析展示如图所示：
在这里插入图片描述

@app.route('/orient',methods=['GET'])
def orient():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()cursor.execute("SELECT DISTINCT(orient) from house;")result = cursor.fetchall()orient_kind = []orient_data = []# 获取到朝向的几种情况for field in result:orient_kind.append(field[0])# 获取到每种朝向类型对应的个数for i in range(len(orient_kind)):cursor.execute("SELECT count(*) from house where orient = '" + orient_kind[i] + "'")count = cursor.fetchall()orient_data.append({'value': count[0][0], 'name': orient_kind[i]})cursor.close()print(orient_data)return jsonify({"orient_kind": orient_kind, "orient_data": orient_data})

全国范围内租房房屋价格情况分析展示如图所示：
在这里插入图片描述

@app.route('/price',methods=['GET'])
def price():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()price_kind = ['<=1000', '1001~2000', '2001~3000', '3001~4000', '4001~5000', '5001~6000', '6001~7000', '7001~8000', '8001~9000', '9001~10000', '>10000']price_data = []# 获取到每种价格类别对应的个数# <=1000cursor.execute("SELECT count(*) from house where price between 0 and 1000;")count = cursor.fetchall()price_data.append(count[0][0])# 1001~2000cursor.execute("SELECT count(*) from house where price between 1001 and 2000;")count = cursor.fetchall()price_data.append(count[0][0])# 2001~3000cursor.execute("SELECT count(*) from house where price between 2001 and 3000;")count = cursor.fetchall()price_data.append(count[0][0])# 3001~4000cursor.execute("SELECT count(*) from house where price between 3001 and 4000;")count = cursor.fetchall()price_data.append(count[0][0])# 4001~5000cursor.execute("SELECT count(*) from house where price between 4001 and 5000;")count = cursor.fetchall()price_data.append(count[0][0])# 5001~6000cursor.execute("SELECT count(*) from house where price between 5001 and 6000;")count = cursor.fetchall()price_data.append(count[0][0])# 6001~7000cursor.execute("SELECT count(*) from house where price between 6001 and 7000;")count = cursor.fetchall()price_data.append(count[0][0])# 7001~8000cursor.execute("SELECT count(*) from house where price between 7001 and 8000;")count = cursor.fetchall()price_data.append(count[0][0])# 8001~9000cursor.execute("SELECT count(*) from house where price between 8001 and 9000;")count = cursor.fetchall()price_data.append(count[0][0])# 9001~10000cursor.execute("SELECT count(*) from house where price between 9001 and 10000;")count = cursor.fetchall()price_data.append(count[0][0])# >10000cursor.execute("SELECT count(*) from house where price >10000;")count = cursor.fetchall()price_data.append(count[0][0])cursor.close()print(price_data)return jsonify({"price_kind": price_kind, "price_data": price_data})

全国范围内租房房屋价格与房屋面积关系情况分析展示如图所示：
在这里插入图片描述

@app.route('/relation',methods=['GET'])
def relation():conn = pymysql.connect(host='localhost', user='root', password='123456', port=3366, db='lagou',charset='utf8mb4')cursor = conn.cursor()relation_data = []cursor.execute("select count(*) from house;")count = cursor.fetchall()#print(count[0][0])cursor.execute("SELECT area,price from house;")result = cursor.fetchall()for i in range(count[0][0]):relation_data.append(list(result[i]))#print(relation_data)cursor.close()return jsonify({"relation_data": relation_data})

（十一）智能预测
在这里插入图片描述

@app.route('/predict',methods=['GET'])
def predict():y_data = ['0—10K', '10—20K', '20—30K', '30—40K', '40K以上']positionName = str(request.args['positionName']).lower()model = str(request.args['model'])with open(positionName+'_'+model+'.model', 'rb') as fr:selected_model = pickle.load(fr)companySize = int(request.args['companySize'])workYear = int(request.args['workYear'])education = int(request.args['education'])city = int(request.args['city'])x = [companySize, workYear, education, city]x = np.array(x)y = selected_model.predict(x.reshape(1, -1))return jsonify(y_data[y[0]])