Flask 和 ECharts 展示去哪儿旅游数据可视化

from flask import Flask, jsonify, render_template, redirect, url_for, session, flash, request import pymysql from 天气可视化pyspark.forms import loginForm import pandas as pd app = Flask(__name__) app.config['SECRET_KEY'] = '123456' def connt(): conn = pymysql.connect(host='127.0.0.1', user='root', password='786120564', database='去哪儿') 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("/admin") def admin(): sql = "SELECT * FROM user " cursor, conn = connt() cursor.execute(sql) users = cursor.fetchall() close(cursor, conn) return render_template('admin.html', users=users) @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(): if form.username.data == 'admin': sql = "SELECT * FROM user " cursor, conn = connt() cursor.execute(sql) users = cursor.fetchall() close(cursor, conn) return render_template('admin.html', users=users) 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('/delete/<int:user_id>') def delete(user_id): # 建立数据库连接 cursor, conn = connt() # 执行 SQL 删除 sql = "DELETE FROM user WHERE id=%s" cursor.execute(sql, user_id) conn.commit() close(cursor, conn) return redirect(url_for('admin')) @app.route('/edit/<int:user_id>', methods=['GET', 'POST']) def edit(user_id): # 建立数据库连接 if request.method == 'POST': # 处理表单提交 username = request.form['username'] password = request.form['password'] # 执行 SQL 更新 sql = "UPDATE user SET username=%s, password=%s WHERE id=%s" cursor, conn = connt() cursor.execute(sql, (username, password, user_id)) conn.commit() close(cursor, conn) # 重定向到用户列表页 return redirect('/admin') else: # 显示编辑页面 sql = "SELECT * FROM user WHERE id=%s" cursor, conn = connt() cursor.execute(sql, user_id) user = cursor.fetchall() close(cursor, conn) # 渲染模板并传递用户数据 return render_template('edit.html', user=user[0]) @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('/add', methods=['GET', 'POST']) def add(): # 建立数据库连接 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('/admin') else: return render_template('add.html') # 全国城市热度前十 @app.route("/echart1") def echart1(): query = "SELECT 目的地, count(*) as count FROM trip GROUP BY 目的地 order by count desc limit 10" conn = pymysql.connect(host='127.0.0.1', user='root', password='786120564', database='去哪儿') df_pandas = pd.read_sql(query, conn) # Close the MySQL connection conn.close() # Now, df_pandas is a Pandas DataFrame, and you can work with it as usual x_data = df_pandas['目的地'].tolist() y_data = df_pandas[['目的地', 'count']].values.tolist() return jsonify([x_data, y_data]) # 价格区间数量 @app.route("/echart2") def echart2(): query = "SELECT * FROM trip" conn = pymysql.connect(host='127.0.0.1', user='root', password='786120564', database='去哪儿') df_pandas = pd.read_sql(query, conn) # Close the MySQL connection conn.close() 价格_ranges = [(0, 500), (500, 1000), (1000, 1200), (1200, 1500), (1500, 1800), (1800, 2200), (2200, 3500)] # Convert the "价格" column to numeric type df_pandas['价格'] = pd.to_numeric(df_pandas['价格'], errors='coerce') # Calculate the count for each 价格 range count_list = [] for 价格_range in 价格_ranges: count = df_pandas[(df_pandas['价格'] >= 价格_range[0]) & (df_pandas['价格'] < 价格_range[1])].shape[0] count_list.append(count) # Now, count_list contains the counts for each 价格 range return jsonify([价格_ranges, count_list]) # 房间类型占比 @app.route("/echart3") def echart3(): # SQL query query = "SELECT 房间 FROM trip" conn = pymysql.connect(host='127.0.0.1', user='root', password='786120564', database='去哪儿') df_pandas = pd.read_sql(query, conn) # Close the MySQL connection conn.close() # Define words words = ['标准间', '大床房', '双人间', '商务标间', '特惠房', '豪华标准间', '观海房', '主题标准间', '情侣大床房', '高级房'] # Initialize count_list count_list = [] # Count occurrences for each word in the '房间' column for word in words: count = int(df_pandas['房间'].str.contains(word).sum()) count_list.append({'name': word, 'value': count}) return jsonify(count_list) import cpca # 编写将城市名称转换为省份名称的函数 def get_province(city): location = cpca.transform([city]) return location.iloc[0]["省"] @app.route("/echart4") def echart4(): query = "SELECT 目的地, count(*) as count FROM trip GROUP BY 目的地" # Establish MySQL connection and execute query conn = pymysql.connect(host='127.0.0.1', user='root', password='786120564', database='去哪儿') df_pandas = pd.read_sql(query, conn) # Close the MySQL connection conn.close() # Apply the get_province function to the '目的地' column df_pandas['province'] = df_pandas['目的地'].apply(get_province) # Summarize the counts by province result = df_pandas.groupby('province')['count'].sum().reset_index() # Convert the result to the desired format data_1 = [{'name': row['province'], 'value': row['count']} for _, row in result.iterrows()] return jsonify(data_1) # 个旧酒店类型 平均评分 @app.route("/echart5") def echart5(): # SQL query query = "SELECT 酒店 FROM trip" # Establish MySQL connection and execute query conn = pymysql.connect(host='127.0.0.1', user='root', password='786120564', database='去哪�