Windows+Pycharm+Flask+Vue+Element-Plus 前後端分離實現分寫查詢功能

知之不若行之發表於2024-05-14
WindowsPyCharmFlaskVue後端

準備工作

安裝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目錄，安裝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')

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

啟動後臺

驗證後臺程式是否正常

相關文章