Windows+Pycharm+Flask+Vue+Element-Plus 前后端分离实现分写查询功能

准备工作

安装nodejs

https://nodejs.cn/download/

验证是否安装成功

安装Python

不赘述，3.7+

Pycharm 创建Flask项目

D:\pythonProject\myvue

Windows Power Shell 进入到CMD指令界面，cd D:\pythonProject\myvue 进入到myvue目录，创建vue项目文件client，然后cd到client目录，安装vue-cli

npm install -g @vue/cli

然后cd到client目录，安装element-plus

npm install element-plus --save

然后cd到client目录，安装element-plus/icons-vue

npm install @element-plus/icons-vue

然后cd到client目录，安装axios（前后端分离的应用，要安装请求的库axios）

npm install --save axios

前端代码（myvue\client\scr\components\DataQuery.vue）

<template>
  <el-form :inline="true" :model="queryForm" class="form-inline" @submit.native.prevent="onSubmit">
    <el-form-item label="债券名称">
      <el-input class="input-width" v-model="queryForm.bondShortName" placeholder="请输入债券名称"></el-input>
    </el-form-item>
    <el-form-item label="LEI">
      <el-input class="input-width" v-model="queryForm.lei" placeholder="请输入LEI"></el-input>
    </el-form-item>
    <el-form-item label="估值日期">
      <el-date-picker v-model="queryForm.priceAsOf" type="date" placeholder="请选择日期"></el-date-picker>
    </el-form-item>
    <el-form-item>
      <el-button type="primary" native-type="submit">查询</el-button>
      <el-button native-type="reset">重置</el-button>
    </el-form-item>
  </el-form>
  <div class="table-container">
    <div ref="tableWrapper" class="table-wrapper">
      <el-table :data="queryResult" style="width: 100%">
        <el-table-column fixed prop="bond_short_name" label="债券名称"></el-table-column>
        <el-table-column width="250" prop="lei" label="LEI"></el-table-column>
        <el-table-column prop="price_as_of" label="估值日期"></el-table-column>
        <el-table-column prop="isin" label="isin"></el-table-column>
        <el-table-column prop="current_coupon" label="当前票息"></el-table-column>
        <el-table-column prop="issue_price" label="发行价格"></el-table-column>
        <el-table-column prop="issue_size" label="发行规模"></el-table-column>
        <el-table-column prop="maturity" label="发行期限"></el-table-column>
      </el-table>
    </div>
  </div>
  <el-pagination
    @current-change="handlePageChange"
    :current-page="currentPage"
    :page-size="pageSize"
    :total="total"
    layout="prev, pager, next, jumper"
    background>
  </el-pagination>
</template>

<script>
import { ref, onMounted, onBeforeUnmount } from 'vue';
import axios from 'axios';
import { ElMessage, ElTable, ElTableColumn } from 'element-plus';

export default {
  components: {
    ElTable,
    ElTableColumn,
  },
  setup() {
    const queryForm = ref({ bondShortName: '', lei: '', priceAsOf: '' });
    const queryResult = ref([]);
    const currentPage = ref(1); // 当前页码
    const pageSize = ref(50); // 每页显示条数
    const total = ref(0); // 数据总数

    const loadQueryResult = () => {
      axios.get('http://localhost:5000/query', {
        params: {
          bond_short_name: queryForm.value.bondShortName,
          lei: queryForm.value.lei,
          price_as_of: queryForm.value.priceAsOf,
          page: currentPage.value, // 将当前页码传递给后端
          page_size: pageSize.value // 将每页显示条数传递给后端
        }
      })
      .then(response => {
        queryResult.value = response.data.data;
        total.value = response.data.total; // 更新总记录数
      })
      .catch(error => {
        ElMessage.error('Error fetching data');
        console.error(error);
      });
    };

    const handlePageChange = (newPage) => {
      currentPage.value = newPage; // 更新当前页码
      loadQueryResult(); // 重新加载数据
    };

    // 组件挂载时加载数据
    onMounted(loadQueryResult);

    const onSubmit = () => {
      currentPage.value = 1; // 每次查询时，重置当前页码为1
      loadQueryResult();
    };

    return {
      queryForm,
      queryResult,
      currentPage,
      pageSize,
      total,
      handlePageChange,
      onSubmit,
    };
  }
};
</script>

<style scoped>
.form-inline.input-width {
  width: 25%;
}

.table-wrapper {
  overflow-y: auto;
}
/* 表格容器样式 */
.table-container {
  height: 830px; /* 设置合适的高度 */
  overflow-y: auto; /* 添加滚动条 */
}
</style>

main.js(myvue/client/src/main.js)

import { createApp } from 'vue'
import App from './App.vue'
import router from './router'
import 'element-plus/theme-chalk/index.css'
import ElementPlus from 'element-plus'
import "@element-plus/icons-vue"
import 'element-plus/dist/index.css'

createApp(App).use(router).use(ElementPlus).mount('#app')

router/index.js文件

import { createRouter, createWebHistory } from 'vue-router';
import DataQuery from '../components/DataQuery.vue'; // 导入你的组件

// 创建路由器实例
const router = createRouter({
  history: createWebHistory(),
  routes: [
    {
      path: '/', // 首页路由
      name: 'Home',
      component: DataQuery, // 将DataQuery组件作为首页的组件
    },
    // 可以在这里添加更多的路由配置
  ],
});

export default router;

App.vue(myvue/client/src/App.vue)

<template>
  <div id="app">
    <data-query />
  </div>
</template>

<script>
import DataQuery from './components/DataQuery.vue';

export default {
  components: {
    DataQuery,
  },
};
</script>

后台代码（myvue\app.py）

from flask import Flask, request, jsonify
import pymysql
from flask_cors import CORS
from datetime import datetime

app = Flask(__name__)
CORS(app)

app.config['MYSQL_HOST'] = '127.0.0.1'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = '******'
app.config['MYSQL_DB'] = 'db_name'

def init_db():
    return pymysql.connect(
        host=app.config['MYSQL_HOST'],
        user=app.config['MYSQL_USER'],
        password=app.config['MYSQL_PASSWORD'],
        db=app.config['MYSQL_DB'],
        port=33061,
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )

def format_date(date):
    try:
        # 将整数类型的日期转换为字符串，然后解析为日期类型
        return datetime.strptime(str(date), '%Y%m%d').strftime('%Y-%m-%d')
    except ValueError:
        return date

def to_str(data):
    if isinstance(data, dict):
        return {k: to_str(v) for k, v in data.items()}
    elif isinstance(data, list):
        return [to_str(v) for v in data]
    elif isinstance(data, bytes):
        return data.decode('utf-8', errors='replace')  # 将 bytes 类型转换为 str
    else:
        return data

@app.route('/query', methods=['GET'])
def query_data():
    bond_short_name = request.args.get('bond_short_name', '').strip()
    lei = request.args.get('lei', '').strip()
    price_as_of = request.args.get('price_as_of', '').strip()
    page = int(request.args.get('page', 1))
    page_size = min(int(request.args.get('page_size', 10)), 100)  # 限制每页显示的数量最大为 100 条

    try:
        connection = init_db()
        with connection.cursor() as cursor:
            conditions = []
            params = []

            if bond_short_name:
                conditions.append("bond_short_name LIKE %s")
                params.append('%' + bond_short_name + '%')
            if lei:
                conditions.append("lei LIKE %s")
                params.append('%' + lei + '%')
            if price_as_of:
                formatted_date = format_date(price_as_of)
                conditions.append("price_as_of = %s")
                params.append(formatted_date)

            sql = "SELECT * FROM cbusd_instrument"
            if conditions:
                sql += " WHERE " + " AND ".join(conditions)

            count_sql = f"SELECT COUNT(*) FROM cbusd_instrument"
            if conditions:
                count_sql += " WHERE " + " AND ".join(conditions)

            cursor.execute(count_sql, params)
            total_records = cursor.fetchone()['COUNT(*)']

            offset = (page - 1) * page_size
            sql += " LIMIT %s OFFSET %s"
            params.extend([page_size, offset])

            cursor.execute(sql, params)
            result = cursor.fetchall()

            # 将结果中的字节转换为字符串
            result = [to_str(item) for item in result]

        return jsonify(data=result, total=total_records)

    except pymysql.err.MySQLError as e:
        return jsonify({"status": "error", "message": str(e)})
    finally:
        connection.close()

if __name__ == '__main__':
    app.run(host='127.0.0.1', port=5000)  # 根据需要更改主机和端口

启动前端

cd D:\pythonProject\myvue>client

npm run serve

启动后台

验证后台程序是否正常