Flask+ECharts历届学生信息数据可视化

import re import pandas as pd from flask import Flask, render_template, jsonify from sqlalchemy import create_engine,text app = Flask(__name__) @app.route("/") def index(): return render_template("index.html") @app.route("/2") def index2(): return render_template("index2.html") @app.route("/3") def bejing(): return render_template("index3.html") @app.route("/4") def bejing2(): return render_template("index4.html") @app.route("/echart1") def echart1(): engine = create_engine('mysql+pymysql://root:[email protected]/qmks2023') connection = engine.connect() # 从数据库中读取数据 df = pd.read_sql(sql=text('SELECT 入学年份 FROM zn12451 '), con=connection) # 4. 选择年份在 2000 至 2023 年之间的数据 df = df[(df['入学年份'] >= 2000) & (df['入学年份'] <= 2023)] # 4. 计算各年份人数 yearly_counts = df.groupby('入学年份').size().reset_index(name='人数') x_data = yearly_counts['入学年份'].tolist() y_data = yearly_counts['人数'].tolist() total = sum(y_data) return jsonify({'x_data':x_data,'y_data':y_data,'total':total}) import cpca @app.route("/echart2") def echart2(): engine = create_engine('mysql+pymysql://root:[email protected]/qmks2023') connection = engine.connect() # 从数据库中读取数据 df = pd.read_sql(sql=text('SELECT 工作单位地址 FROM zn12451'), con=connection) # 2. 提取省份信息 df['省份'] = df['工作单位地址'].apply(lambda x: cpca.transform([str(x)])['省'][0]) # 3. 统计每个省份的数量 province_counts = df.groupby('省份').size().reset_index(name='个数') # 4. 格式化结果 result_list = [] for index, row in province_counts.iterrows(): result_list.append({'name': row['省份'], 'value': row['个数']}) # 打印结果 print(result_list) return jsonify({'data':result_list}) @app.route("/echart3") def echart3(): engine = create_engine('mysql+pymysql://root:[email protected]/qmks2023') connection = engine.connect() # 从数据库中读取数据 df = pd.read_sql(sql=text('SELECT 最高学历 FROM zn12451'), con=connection) # 2. 计算各学历的人数 education_counts = df['最高学历'].value_counts() # 3. 获取学历列表和对应的人数列表 education_list = education_counts.index.tolist() count_list = education_counts.values.tolist() max_value = max(count_list) x_data = [{'name':i,'max':max_value} for i in education_list] # 打印结果 print("学历列表:", education_list) print("人数列表:", count_list) return jsonify({'x_data':x_data,'y_data':[count_list]}) @app.route("/echart4") def echart4(): engine = create_engine('mysql+pymysql://root:[email protected]/qmks2023') connection = engine.connect() # 从数据库中读取数据 df = pd.read_sql(sql=text('SELECT 职称 FROM zn12451'), con=connection) df = df[df['职称'].isin(['未知', '无']) == False] # 统计每个职称的数量 title_counts = df['职称'].value_counts() # 获取前十个职称及其数量 top_ten_titles = title_counts.head(10) # 格式化结果 result_list = [{'name': title, 'value': count} for title, count in top_ten_titles.items()] # 打印结果 print(result_list) return jsonify({'data':result_list}) if __name__ == '__main__': app.run()