快捷導(dǎo)航

vue-element-admin+flask實現(xiàn)數(shù)據(jù)查詢項目的實例代碼

更新時間：2022年11月07日 08:42:53 作者：東方不敗就是我

這篇文章主要介紹了vue-element-admin+flask實現(xiàn)數(shù)據(jù)查詢項目,填寫數(shù)據(jù)庫連接信息和查詢語句，即可展示查詢到的數(shù)據(jù)，需要的朋友可以參考下

當(dāng)今前端世界最流行的三個框架Vue,React,Angular。這三個框架可謂是各有千秋，可以滿足不同場景的需求。我們這套實踐分享內(nèi)容主要是使用Vue框架，這個框架上學(xué)習(xí)起來非常容易，很容易上手。相對React陡峭的學(xué)習(xí)曲線，Vue顯得格外簡單和友好。好了，選好了框架后，我們還需要選擇UI組件庫?；赩ue框架開發(fā)的UI組件庫也有很多，例如：View UI ，Element UI等等。我們這里使用Element UI做為我們的UI組件庫。它的生態(tài)很豐富，有很多開源的集成了很多日常開發(fā)中常用的功能。在這個項目中，我們采用vue-element-admin。vue-element-admin 是一個后臺前端解決方案，它基于 vue 和 element-ui實現(xiàn)。它使用了最新的前端技術(shù)棧，內(nèi)置了 i18n 國際化解決方案，動態(tài)路由，權(quán)限驗證，提煉了典型的業(yè)務(wù)模型，提供了豐富的功能組件，可以幫助我們快速搭建企業(yè)級中后臺產(chǎn)品。

本文分享一個使用vue-element-admin+flask實現(xiàn)的一個數(shù)據(jù)查詢項目，

填寫數(shù)據(jù)庫連接信息和查詢語句，即可展示查詢到的數(shù)據(jù)。

前提：已下載vue-element-admin并編譯成功

前端

1、添加路由

src-router-index.js添加路由

{
    path: '/idata',
    component: Layout,
    redirect: '/idata/index',
    hidden: false,
    children: [
      {
        path: 'index',
        component: () => import('@/views/idata/index'),
        name: 'idata',
        meta: { title: 'iData設(shè)置', icon: 'list', noCache: true }
      }
    ]
  },

2、添加頁面元素

在src-views下新建idata目錄，目錄下新建index.vue

<template>
  <div class="app-container">
    <el-card class="box-card">
      <el-form :inline="true" ref="formData" :rules="rules" :model="formData" label-width="55px">
        <el-form-item label="域名" prop="db_host">
          <el-input
          v-model="formData.db_host"></el-input>
        </el-form-item>
        <el-form-item label="端口" prop="db_port" >
          <el-input
          v-model="formData.db_port" style="width: 80px;"></el-input>
        </el-form-item>
        <el-form-item label="用戶名" prop="db_user">
          <el-input
          v-model="formData.db_user" style="width: 80px;"></el-input>
        </el-form-item>
        <el-form-item label="密碼" prop="db_passwd">
          <el-input
          v-model="formData.db_passwd" show-password></el-input>
        </el-form-item>
        <el-form-item label="數(shù)據(jù)庫" prop="db_name">
          <el-input
          v-model="formData.db_name"></el-input>
        </el-form-item>
       
  
          <el-row>
      <el-input
        type="textarea"
        :rows="5"
        placeholder="請輸入SQL語句"
        v-model="formData.sql">
      </el-input>
    </el-row>
  <el-row>
          <el-button type="primary" @click="onConnect('formData')">測試連接</el-button>
          <el-button type="primary" @click="onExec('formData')">執(zhí)行SQL</el-button>
          <p>{{message}}</p>
        </el-row>
    </el-form>
    </el-card>
    <el-card class="box-card">
      <el-table
      :data="tableData"
     
      style="width: 100%">
        <el-table-column v-for="h in tableHead" :key="h"
          :prop="h" :label="h">
        </el-table-column>
      </el-table>
    </el-card>
  </div>
</template>
 
<script>
import { getConnect, doConnect, exec } from '@/api/idata'
export default {
  name: 'idata',
  data() {
    return {
      rules: {
        db_host: [
          { required: true, message: '請輸入域名', trigger: 'blur' }
        ],
        db_port: [
          { required: true, message: '請輸入端口', trigger: 'blur' }
        ],
        db_user: [
          { required: true, message: '請輸入用戶名', trigger: 'blur' }
        ],
        db_passwd: [
          { required: true, message: '請輸入密碼', trigger: 'blur' }
        ],
        db_name: [
          { required: true, message: '請輸入數(shù)據(jù)庫', trigger: 'blur' }
        ]
      },
 
      title: 'iData設(shè)置',
      formData: {
        db_charset: 'utf8',
        db_host: null,
        db_port: null,
        db_user: null,
        db_passwd: null,
        db_name: null,
        sql: null,
        param: {},
        key: 'all'
      },
      tableData: [],
      time: 0,
      uniqKey: null,
      maxCount: 20,
      sql: null,
      message:null,
      tableHead: [],
      result:[]
    }
  },
  methods: {
    onConnect (formName) {
        this.$refs[formName].validate((valid) =>{
        if (valid) {
        
         doConnect({
        ...this.formData
      }).then(res => {
        
        this.uniqKey = res.uniq_key
        this.message='連接成功'
        this.$message({
          message: '連接測試成功！',
          type: 'success'
        })
      })
          }
         else {    
          this.$message({
            message: '請檢查必填字段！',
            type: 'error'
          })
           
            return false
          }
      }
      )//驗證函數(shù)結(jié)尾
    },
    onExec (formName) {
      this.$refs[formName].validate((valid) =>{
      if (valid) {
          if (!this.formData.sql) {
            this.$message({
              message: 'SQL 語句不能為空。',
              type: 'warning'
            })
            return
          }
      
      exec({                      
            ...this.formData,           
           
          }).then(res => {
           
              if (res.data.length > 0) {  
              this.tableHead = []
              for (let i in res.data[0]) {
                this.tableHead.push(i)
              }
              this.tableData = res.data  
              this.time = res.time_cost
            } else {                      
              this.tableHead = []
              this.tableData = []
            }
            this.message='查詢成功'
            this.loading = false
            this.$message({
              message: '查詢成功',
              type: 'success'
            }
 
            )
         
       
  }
           ).catch(err => { 
            this.loading = false
            console.log(err)
            this.$message({
            
              type: 'error'
            })
          })
        }
       else {    
        this.$message({
          message: '請檢查必填字段！',
          type: 'error'
        })
          console.log('error submit!!')
          return false
        }
    }
    )//驗證函數(shù)結(jié)尾
  }//onconnect函數(shù)結(jié)尾
}
}
</script>

3、添加請求

在src-api新建idata.js

import request from '@/utils/request'
export function doConnect (data) {
  return request({
    url: '/api/test',
    method: 'post',
    data
  })
}
export function exec (data) {
  return request({
    url: '/api/idata',
    method: 'post',
    data
  })
}

----拿到ret結(jié)果，根據(jù)結(jié)果來顯示默認按鈕/成功按鈕。
----如何展示查詢到的數(shù)據(jù)呢？前端實際有一個table，動態(tài)讀取結(jié)果字段顯示。
----查詢數(shù)據(jù)時，前端一直報錯，顯示error，控制臺顯示uncanght promise報錯。
查了一會，才發(fā)現(xiàn)是后端返回的ret沒有code這個字段，加上就可以了。
----如何將textraea輸入框?qū)挾仍黾?br />----彈框消息
this.$message({
message: '連接測試成功！',
type: 'success'
})
----前端方法里的response.data實際是取的返回結(jié)果data這個key的值
---- this.$refs 用來給標(biāo)簽或者組件添加屬性。需要這個標(biāo)簽或者組件中有ref=
＜el-form :inline="true" ref="formData" :rules="rules" :model="formData"
label-width="70px"＞
----控制臺可以調(diào)試前端代碼
-----form加:rules="rules" 并且在return里寫上規(guī)則，并且每個el-form-item要有prop，可以實現(xiàn)
校驗必填其實在el-form這個組件有寫對于表單的校驗
-----加了校驗后報錯 Cannot read properties of undefined (reading 'validate')
傳參需要加引號submit('formName')。
this.$refs[formName].validate 這一行是對的

后端

這里只貼主要代碼，有時間再繼續(xù)貼。

1、添加路由

app.py添加

app.route('/api/idata', methods=['POST'])(idata)
    app.route('/api/test',methods=['POST'])(onconnect)

2、添加函數(shù)

controller下data.py

import json
from flask import request, current_app as app
from constant import SQLTYPE, DBKEYTYPE
from lib.dbpool import DBPool
 
 
def idata():
    return query(request.json)
 
def onconnect():
    data=request.json
    conn_info = warp_db_conn_info(data)
    print(conn_info)
    db_conn = DBPool.get_conn(**conn_info)
    ret = {
        "success": False,
        "msg": None,
        'code':0
    }
    if not db_conn:
        ret['msg'] = '連接目標(biāo)DB失敗，請確認連接信息是否正確'
        ret['code']=-1
    else:
        ret['msg'] = '連接成功'
        ret['success']=True
        ret['code']=0
    return ret
 
 
def warp_db_conn_info(data):
    d = {}
    keys = ['db_host', 'db_port', 'db_name', 'db_user', 'db_passwd', 'db_charset']
    for key in keys:
        if key in data and data[key]:
            d[key] = data[key].strip() if isinstance(data[key], (str, bytes)) else data[key]
 
    return d
 
 
def query(data):
    """
    根據(jù)DB和SQL信息執(zhí)行遠程查詢并返回結(jié)果
    :param data:     {
        "db_host": "x.x.x.x",
        "db_port": 3306,
        "db_name": "test",
        "db_user": "root",
        "db_passwd": "root",
        "db_charset": "utf8",
        "sql": "select * from tbl_key_info where id=:id limit 1",
        "param": {
            "id": 100110
        },
        "key": "all"
    }
    :return: {
        "success": True,
        "data": [{"id": 100110, "xxx", "xxx"}],
        "type": "SELECT",
        "msg": ""
    }
    """
    app.logger.info(f'query data {json.dumps(data)}')
    ret = {
        "success": False,
        "data": [],
        "type": None,
        "msg": None,
        "code":0
    }
 
    if not data:
        return ret
 
    conn_info = warp_db_conn_info(data)
    # print(conn_info)
    db_conn = DBPool.get_conn(**conn_info)
    if not db_conn:
        ret['msg'] = '連接目標(biāo)DB失敗，請確認連接信息是否正確'
        return ret
 
    sql = data.get('sql')
    if not sql:
        ret['msg'] = '查詢語句不能為空'
        return ret
 
    # app.logger.info(f'查詢語句: {sql}, 查詢參數(shù): {data.get("param")}')
    app.logger.info(f'查詢語句: {sql}, 查詢參數(shù): {data.get("param")}')
    # rows = db_conn.query(sql, **data.get('param'))
    rows = db_conn.query(sql)
    results = []
 
    upper_sql = sql.upper().strip()
    if upper_sql.startswith(SQLTYPE.INSERT):
        sql_type = SQLTYPE.INSERT
    elif upper_sql.startswith(SQLTYPE.UPDATE):
        sql_type = SQLTYPE.UPDATE
    elif upper_sql.startswith(SQLTYPE.DELETE):
        sql_type = SQLTYPE.DELETE
    elif upper_sql.startswith(SQLTYPE.SELECT):
        sql_type = SQLTYPE.SELECT
        key = data.get('key', DBKEYTYPE.FIRST)
        if key == 'all':
            results = rows.as_dict()
        else:  # 為了減少網(wǎng)絡(luò)傳輸，默認只查詢一行
            first = rows.first()
            results = [first.as_dict()] if first else []
    else:
        sql_type = SQLTYPE.UNKNOWN
 
    ret.update(
        {
            "success": True,
            "data": results,
            "type": sql_type,
            "msg": '',
            'code':0
 
        }
    )
 
    app.logger.info(f'查詢結(jié)果: {results}')
    return ret

dbpool.py用來進行數(shù)據(jù)庫連接

import records
from urllib import parse
class DBPool:
    db_pool = {}
 
    @staticmethod
    def get_conn(db_host=None, db_name=None, db_port="3306", db_user='root',
                 db_passwd='root', db_charset='utf8'):
        if db_host is None or db_name is None:
            raise ValueError("host and db_name can't be None.")
 
        key = DBPool.make_uniq_key(db_host, db_port, db_name)
        if key not in DBPool.db_pool:
            sql_str = DBPool.make_conn_str(db_host, db_name, db_port, db_user, db_passwd, db_charset)
            print(sql_str)
            DBPool.db_pool[key] = records.Database(sql_str, pool_pre_ping=True)
 
        try:
            db = DBPool.db_pool[key]
            db.query('''select 1''')
        except:
            db = None
            del DBPool.db_pool[key]
 
        return db
 
    @staticmethod
    def make_uniq_key(db_host, db_name, db_port):
        return f'{db_host}:{db_port}:{db_name}'
 
    @staticmethod
    def make_conn_str(db_host, db_name, db_port, db_user, db_passwd, db_charset):
        return f'mysql+pymysql://{db_user}:{parse.quote_plus(db_passwd)}@{db_host}:{db_port}/{db_name}?charset={db_charset}'

今天晚了，改天有時間再詳細講解。

到此這篇關(guān)于vue-element-admin+flask實現(xiàn)數(shù)據(jù)查詢項目的文章就介紹到這了,更多相關(guān)vue-element-admin數(shù)據(jù)查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: