数据库原理大作业全国房价图-vue3前端框架flask后端mysql数据库宝塔部署

于 2025-04-18 11:42:34 发布
阅读量760 收藏 16
点赞数 25
文章标签： 数据库 课程设计 flask vue mysql echarts
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xuexueyouzi/article/details/147322666
版权

前言

我们老师有个数据库原理的大作业，我比较感兴趣，然后我自己完成了这项作业，并且实现了部署，网址是数据可视化平台，其中前端框架是vue3后端是用flask写的，数据库是mysql，用宝塔方便部署。其中有登录页面，账号和密码请关注我然后私信我获取，源码的话如果点赞的人多我回进行上传。其中ER图的创建是通过这个网页SQL转ER图工具 - 在线数据库设计工具 | 噌噌噌代码生成平台

前端

vue3框架

通过下面命令创建

npm create vite@latest my-vue-app --template vue

涉及到的核心依赖 

  • Vue 3 (Composition API + script setup语法)
  • Pinia (状态管理)
  • Vue Router (路由管理)
  • Vite (构建工具)
  • Axios (HTTP客户端)

 其中登录页面是

登录页面

这个主要借用了ant-design-vue，方便快速搭建，然后后端我并没有建立相应的数据库，只是单纯的后端校验 

首页

然后首页的话是单纯的个人信息展示 这两个页面都没有涉及到很困难的地方

数据展示

数据展示页面的话主要是通过echarts插件来实现的，对此我们要先安装echarts插件，然后页面是由六部分组成的，除了中国地图需要自己去找数据资源，其余五幅图只要有数据自己再写好看的样式就行了，对于中国地图，点击对应的省份之后也回进入对应的省份页面

数据管理

其中实现了最基本的增删改查，然后有插件可以实现对于excel的导出，写的比较简陋

后端

flask框架

下面是代码

from flask import Flask, request, jsonify
from flask_cors import CORS
import mysql.connector
import logging

# 配置日志
logging.basicConfig(level=logging.INFO)

# 创建Flask应用实例
app = Flask(__name__)
CORS(app)

# 配置 MySQL 连接参数
DB_CONFIG = {
    "host": "localhost",
    "user": "你自己的用户",
    "password": "你自己的密码",
    "database": "你自己的数据库"
}


# 定义路由和视图函数
@app.route('/')
def hello_world():
    return 'Hello, World!'


@app.route('/auth/login', methods=['POST'])
def login():
    data = request.get_json()
    username = data.get('username')
    password = data.get('password')
    logging.info(f"Received login request with username: {username}, password: {password}")
    if username == '拜托关注我一下' and password == '私信我获取密码':
        # 返回成功响应，状态码 200，并包含 JSON 响应体
        return jsonify({"message": "登录成功", "token": "your_token_here"}), 200
    else:
        # 返回失败响应，状态码 401，并包含 JSON 响应体
        return jsonify({"message": "用户名或密码错误"}), 401


@app.route('/city/data', methods=['GET'])
def get_data():
    city = request.args.get('city')
    logging.info(f"Received request for city data: {city}")
    try:
        # 每次操作创建新连接
        mydb = mysql.connector.connect(**DB_CONFIG)
        mycursor = mydb.cursor()
        query = "SELECT * FROM rooms WHERE district = %s"
        mycursor.execute(query, (city,))
        result = mycursor.fetchall()
        logging.info(f"Query result: {result}")
        return jsonify({'data': result}), 200
    except mysql.connector.Error as err:
        logging.error(f"Database error: {err}")
        return jsonify({"message": "数据库查询出错"}), 500
    finally:
        if 'mydb' in locals() and mydb.is_connected():
            mycursor.close()
            mydb.close()


@app.route('/city/data/update', methods=['POST'])
def update_city():
    params = request.get_json()
    if not params:
        return {'message': '请求体中缺少 JSON 数据'}, 400
    index = params.get('index')
    data = params.get('data')
    city = params.get('city')
    logging.info(f"Received update request for city {city}, index {index}, data {data}")
    # 验证参数
    if not all([isinstance(index, int) and index > 0, data, city]):
        return {'message': '参数缺失或格式不正确'}, 400

    try:
        mydb = mysql.connector.connect(**DB_CONFIG)
        mycursor = mydb.cursor()
        # 获取指定城市第 index 条记录的 id
        query = "SELECT id FROM rooms WHERE district = %s LIMIT %s, 1"
        mycursor.execute(query, (city, index - 1))
        result = mycursor.fetchone()
        if not result:
            return {'message': f'未找到城市 {city} 的第 {index} 条记录'}, 404
        row_id = result[0]
        logging.info(f"Row ID to update: {row_id}")
        # 更新记录
        update_query = "UPDATE rooms SET value = %s WHERE id = %s"
        mycursor.execute(update_query, (data, row_id))
        mydb.commit()
        return {'message': '数据更新成功'}
    except mysql.connector.Error as err:
        logging.error(f"Database error: {err}")
        mydb.rollback()
        return {'message': '数据库更新出错'}, 500
    finally:
        if 'mydb' in locals() and mydb.is_connected():
            mycursor.close()
            mydb.close()


@app.route('/city/data/delete', methods=['POST'])
def delete_city():
    params = request.get_json()
    index = params.get('index')
    city = params.get('city')
    logging.info(f"Received delete request for city {city}, index {index}")
    try:
        mydb = mysql.connector.connect(**DB_CONFIG)
        mycursor = mydb.cursor()
        # 假设表中有一个自增的 id 列
        query = "SELECT id FROM rooms WHERE district = %s LIMIT %s, 1"
        mycursor.execute(query, (city, index - 1))
        row_id = mycursor.fetchone()[0]
        delete_query = "DELETE FROM rooms WHERE id = %s"
        mycursor.execute(delete_query, (row_id,))
        mydb.commit()
        return {'data': '数据删除成功'}  # 返回一个字典
    except mysql.connector.Error as err:
        logging.error(f"Database error: {err}")
        mydb.rollback()
        return {'message': '数据库删除出错'}, 500
    finally:
        if 'mydb' in locals() and mydb.is_connected():
            mycursor.close()
            mydb.close()


@app.route('/city/data/add', methods=['POST'])
def add_city():
    params = request.get_json()
    if not params:
        return jsonify({'message': '请求体中缺少 JSON 数据'}), 400
    province = params.get('province')
    data = params.get('data')
    city = params.get('city')
    logging.info(f"Received add request for province {province}, city {city}, data {data}")

    try:
        mydb = mysql.connector.connect(**DB_CONFIG)
        mycursor = mydb.cursor()
        # 查询最大 id
        mycursor.execute('SELECT MAX(id) FROM rooms')
        max_id_result = mycursor.fetchone()
        logging.info(f"Max ID result: {max_id_result}")
        if max_id_result and len(max_id_result) > 0:
            max_id = max_id_result[0]
        else:
            max_id = 0
        new_id = max_id + 1
        logging.info(f"New ID: {new_id}")

        # 插入数据
        query = "INSERT INTO rooms (id, city, district, value) VALUES (%s, %s, %s, %s)"
        mycursor.execute(query, (new_id, province, city, data))
        mydb.commit()

        return jsonify({'message': '数据插入成功', 'new_id': new_id}), 200
    except mysql.connector.Error as err:
        logging.error(f"Database error: {err}")
        mydb.rollback()
        return jsonify({'message': f'数据库操作错误: {str(err)}'}), 500
    finally:
        if 'mydb' in locals() and mydb.is_connected():
            mycursor.close()
            mydb.close()


@app.route('/bardata', methods=['GET'])
def get_bar_data():
    try:
        mydb = mysql.connector.connect(**DB_CONFIG)
        mycursor = mydb.cursor()
        query = "SELECT province, value FROM provinces"
        mycursor.execute(query)
        result = {}
        for row in mycursor.fetchall():
            result[row[0]] = round(row[1] / 10000, 2)
        logging.info(f"Bar data result: {result}")
        sort_res = dict(sorted(result.items(), key=lambda x: x[1]))
        logging.info(f"Sorted bar data result: {sort_res}")
        return jsonify(sort_res), 200
    except mysql.connector.Error as err:
        logging.error(f"Database error: {err}")
        return jsonify({"message": "数据库查询出错"}), 500
    finally:
        if 'mydb' in locals() and mydb.is_connected():
            mycursor.close()
            mydb.close()


@app.route('/piedata', methods=['GET'])
def get_pie_data():
    pieData = [
        {"value": 115, "name": '贝壳'},
        {"value": 180, "name": '安居客'},
        {"value": 150, "name": '链家'},
        {"value": 78, "name": '地方网站'},
        {"value": 25, "name": '现实调查'},
        {"value": 60, "name": '网上问价'},
    ]
    return jsonify(pieData), 200


@app.route('/mapdata', methods=['GET'])
def get_map_data():
    try:
        mydb = mysql.connector.connect(**DB_CONFIG)
        mycursor = mydb.cursor()
        query = "SELECT province, value FROM provinces"
        mycursor.execute(query)
        result = {}
        for row in mycursor.fetchall():
            result[row[0]] = round(row[1] / 10000, 2)
        logging.info(f"Map data result: {result}")
        return jsonify(result), 200
    except mysql.connector.Error as err:
        logging.error(f"Database error: {err}")
        return jsonify({"message": "数据库查询出错"}), 500
    finally:
        if 'mydb' in locals() and mydb.is_connected():
            mycursor.close()
            mydb.close()


@app.route('/mapdata/city', methods=['GET'])
def get_mapdata_city_data():
    city = request.args.get('city')
    logging.info(f"Received request for map data of city: {city}")
    try:
        mydb = mysql.connector.connect(**DB_CONFIG)
        mycursor = mydb.cursor()
        query = "SELECT district, value FROM rooms WHERE city = %s"
        mycursor.execute(query, (city,))
        result = {}
        for row in mycursor.fetchall():
            key = row[0]
            num = float(row[1])
            value = round(num / 10000, 2)
            if key not in result:
                result[key] = value
            else:
                result[key] = max(result[key], value)
        logging.info(f"Map data result for city {city}: {result}")
        return jsonify(result), 200
    except mysql.connector.Error as err:
        logging.error(f"Database error: {err}")
        return jsonify({"message": "数据库查询出错"}), 500
    finally:
        if 'mydb' in locals() and mydb.is_connected():
            mycursor.close()
            mydb.close()


@app.route('/linedata', methods=['GET'])
def get_line_data():
    result = [['2018', 3.7], ['2019', 4.7], ['2020', 4.3], ['2021', 3.6], ['2022', 3.2], ['2023', 3.0], ['2024', 2.9]]
    return jsonify(result), 200


@app.route('/raddata', methods=['GET'])
def get_rad_data():
    city = request.args.get('city')
    logging.info(f"Received request for rad data of city: {city}")
    try:
        mydb = mysql.connector.connect(**DB_CONFIG)
        mycursor = mydb.cursor()
        query = "SELECT district, value FROM rooms WHERE city = %s"
        mycursor.execute(query, (city,))
        result = {}
        for row in mycursor.fetchall():
            key = row[0]
            num = float(row[1])
            value = round(num / 10000, 2)
            if key not in result:
                result[key] = value
            else:
                result[key] = max(result[key], value)
        logging.info(f"Rad data result for city {city}: {result}")
        return jsonify(result), 200
    except mysql.connector.Error as err:
        logging.error(f"Database error: {err}")
        return jsonify({"message": "数据库查询出错"}), 500
    finally:
        if 'mydb' in locals() and mydb.is_connected():
            mycursor.close()
            mydb.close()


@app.route('/otherdata', methods=['GET'])
def get_other_data():
    city = request.args.get('city')
    logging.info(f"Received request for other data of city: {city}")
    try:
        mydb = mysql.connector.connect(**DB_CONFIG)
        mycursor = mydb.cursor()
        query = "SELECT district, value FROM rooms WHERE city = %s"
        mycursor.execute(query, (city,))
        result = {}
        for row in mycursor.fetchall():
            key = row[0]
            num = float(row[1])
            value = round(num / 10000, 2)
            if key not in result:
                result[key] = value
            else:
                result[key] = max(result[key], value)
        logging.info(f"Other data result for city {city}: {result}")
        return jsonify(result), 200
    except mysql.connector.Error as err:
        logging.error(f"Database error: {err}")
        return jsonify({"message": "数据库查询出错"}), 500
    finally:
        if 'mydb' in locals() and mydb.is_connected():
            mycursor.close()
            mydb.close()


if __name__ == '__main__':
    app.run(debug=True)

其中要解决前后端的跨域问题，引入cors就很好的解决了，还有我有偷懒的成分，有些图表数据我就是自己编写的，为了前端页面的好看。然后数据库的数据来源主要是我通过爬虫爬取的安居客数据，一共2w多条，因为安居客名称还有前端省份城市名称什么的不一致，可能会导致不少数据显示不出来，城市名越标准规范的基本都能显示出来。

通过flask快速搭建后端就好了，但是注意线程问题，在本地能够正常运行但是部署之后不一定能够，我出现了数据库查询错误，上面的是修改好后的

部署

部署的话我用的是宝塔面板，服务器就用哪些厂商的喽，新人价格一年就几十，自己快速安装lnmp环境可以，但是我为了我后续的使用我是编译安装的，然后就是正常的部署python项目，然后把前端打包部署一下就好了

后续

待定

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值