Flask和Pyecharts可视化空气质量指数（AQI）

import json import pandas as pd from flask import Flask, jsonify, render_template, redirect, url_for, session, flash,request from pyecharts.charts import Map, HeatMap, Pie, Line, Bar import pymysql from pyecharts import options as opts from 空气质量指数数据可视化.forms import loginForm app = Flask(__name__) app.config['SECRET_KEY'] = '123456' def connt(): conn = pymysql.connect(host='127.0.0.1', port=3306,user='root', password='786120564', database='kongqi') cursor = conn.cursor() return cursor,conn def close(cursor,conn): cursor.close() conn.close() @app.route("/index") def index(): return render_template("index.html") @app.route("/") def main(): return redirect(url_for('login')) @app.route('/login', methods=['POST','GET']) def login(): if request.method == 'GET': form = loginForm() return render_template('login.html', form=form) form = loginForm(data=request.form) print(form.username.data, form.password.data) if form.validate_on_submit(): cursor, conn = connt() cursor.execute('SELECT * FROM user where username=%s and password=%s',(form.username.data, form.password.data)) # 获取查询结果 user = cursor.fetchall() close(cursor, conn) if user: flash('验证通过，登录成功') return redirect(url_for('index')) flash('没有校验成功') return render_template('login.html', form=form) @app.route('/register', methods=['GET', 'POST']) def register(): if request.method == 'POST': # 处理表单提交 username = request.form['username'] password = request.form['password'] # 执行 SQL 更新 sql = "INSERT INTO user (username,password) values(%s,%s)" cursor, conn = connt() cursor.execute(sql, (username, password)) conn.commit() close(cursor, conn) # 重定向到用户列表页 return redirect('/login') else: return render_template('register.html') # @app.route("/") # def index(): # map_data = echart1() # hot_data = echart2() # pie_data = echart3() # line_data = echart4() # pie1_data = echart5() # bar_data = echart6() # return render_template("index.html",map_data=map_data,hot_data =hot_data, pie_data=pie_data,line_data=line_data,pie1_data=pie1_data,bar_data=bar_data) # return render_template("index.html") import cpca # 编写将城市名称转换为省份名称的函数 def get_province(city): location = cpca.transform([city]) return location.iloc[0]["省"] def search(sql): db_params = { "host": "127.0.0.1", "user": "root", "password": "786120564", "database": "kongqi", "port": 3306, "charset": "utf8mb4", # Adjust the charset based on your database configuration } # Establish a connection to the MySQL database conn = pymysql.connect(**db_params) try: # Use pandas to read data from MySQL df = pd.read_sql(sql, con=conn) finally: # Close the database connection conn.close() return df @app.route("/echart1") def echart1(): name_dic = {'北京': '北京市', '天津': '天津市', '河北': '河北省', '山西': '山西省', '内蒙古': '内蒙古自治区', '辽宁': '辽宁省', '吉林': '吉林省' , '黑龙江': '黑龙江省', '上海': '上海市', '江苏': '江苏省', '浙江': '浙江省', '安徽': '安徽省', '福建': '福建省', '江西': '江西省', '山东': '山东省', '河南': '河南省', '湖北': '湖北省', '湖南': '湖南省', '广东': '广东省', '广西': '广西壮族自治区', '海南': '海南省', '重庆': '重庆市', '四川': '四川省', '贵州': '贵州省', '云南': '云南省', '西藏': '西藏自治区', '陕西': '陕西省', '甘肃': '甘肃省', '青海': '青海省', '宁夏': '宁夏回族自治区', '新疆': '新疆维吾尔自治区'} query = "SELECT cityname, AVG(aqi) AS avg_aqi FROM data GROUP BY cityname" df = search(query) # Apply the get_province function to the "cityname" column df['province'] = df['cityname'].apply(get_province) # Group by "province" and calculate the average of "avg_aqi" df_grouped = df.groupby("province")['avg_aqi'].mean().reset_index() # Convert DataFrame to lists for provinces and values provinces = df_grouped['province'].tolist() values = df_grouped['avg_aqi'].astype(int).tolist() print('provinces', provinces) print('values', values) c = ( Map(init_opts=opts.InitOpts(width='350px', height='300px', page_title='page')) .add("省份", [list(z) for z in zip(provinces, values)], "china",name_map=name_dic,is_map_symbol_show=True) .set_global_opts( title_opts=opts.TitleOpts(title="2022年全国各省份平均aqi",title_textstyle_opts=(opts.TextStyleOpts(color='white')),pos_left='center'), visualmap_opts=opts.VisualMapOpts(max_=100, split_number=5, is_piecewise=True,textstyle_opts=(opts.TextStyleOpts(color='white'))), ) ).set_series_opts(label_opts=opts.LabelOpts(is_show=True,color="white")) # map_data = c.render_embed() chart_data = c.dump_options_with_quotes() return jsonify(chart_data) @app.route("/echart2") def echart2(): conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='786120564', db='kongqi') df = pd.read_sql("SELECT aqi,pm2_5,pm10,so2,no2,co,o3 FROM data", con=conn) corr_df = df.corr() values = corr_df.values x_data = df.columns.tolist() triplets = [] for i, row in enumerate(values): for j, value in enumerate(row): triplets.append((i, j, round(value,2))) c = ( HeatMap() .add_xaxis(x_data) .add_yaxis( "污染物相关性", x_data, triplets, label_opts=opts.LabelOpts(is_show=True, position="inside"), ) .set_global_opts( title_opts=opts.TitleOpts(title="污染物相关性",title_textstyle_opts=(opts.TextStyleOpts(color='white'))), legend_opts=opts.LegendOpts( pos_top="20px", pos_left="center", textstyle_opts=opts.TextStyleOpts(color="#FFFFFF", font_size=14), selected_mode="multiple", ), xaxis_opts=opts.AxisOpts( name_gap=30, axislabel_opts=opts.LabelOpts(color="#FFFFFF", font_size=12), axisline_opts=opts.AxisLineOpts(is_on_zero=False), axistick_opts=opts.AxisTickOpts(is_show=True), splitline_opts=opts.SplitLineOpts(is_show=True, linestyle_opts=opts.LineStyleOpts(width=1)) ), yaxis_opts=opts.AxisOpts( name_gap=30, axislabel_opts=opts.LabelOpts(color="#FFFFFF", font_size=12), axisline_opts=opts.AxisLineOpts(is_on_zero=False), axistick_opts=opts.AxisTickOpts(is_show=True), splitline_opts=opts.SplitLineOpts(is_show=True, linestyle_opts=opts.LineStyleOpts(width=1)) ), visualmap_opts=opts.VisualMapOpts(max_=1, min_=-1, pieces=[ {"min": 0.5, "max": 1,"label": '0.5-1'}, {"min": 0.2, "max": 0.5,"label": '0.2-0.5'}, {"min": 0, "max": 0.2,"label": '0-0.2'}, {"min": -1, "max": 0, "label": '-1-0'}, ],is_piecewise = True,textstyle_opts=(opts.TextStyleOpts(color='whit