elementUI 表格分页后台排序记录

  data () {
      return {
        currentPage: 1,
        currentSize: 10,
        total: 0,
        table_align: ‘left‘,
        logs: [],
        logType: ‘‘,
        logTypes: [],
        timeRange: [],
        keyStr: ‘‘,
        order: ‘desc‘,
        orderProp: ‘operatetime‘,
        times: [
          {value: 1, unit: ‘小时‘},
          {value: 3, unit: ‘小时‘},
          {value: 6, unit: ‘小时‘},
          {value: 12, unit: ‘小时‘},
          {value: 1, unit: ‘天‘},
          {value: 3, unit: ‘天‘},
          {value: 7, unit: ‘天‘},
          {value: 1, unit: ‘月‘},
          {value: 3, unit: ‘月‘},
        ],
        pickerOptions: {
          shortcuts: []
        }
      }
  },
  methods:{
      getFilter(){
          let filters = []
          if(this.logType !== ‘‘)
              filters.push({
                prop: [‘url‘],
                type: ‘and‘,
                operator: ‘like‘,
                value: this.logType
              })
          if(this.keyStr !== ‘‘)
              filters.push({
                prop: [‘nickname‘, ‘post‘],
                type: ‘or‘,
                operator: ‘like‘,
                value: this.keyStr
              })
          if(this.timeRange.length===2)
            filters.push({
              prop: ‘operatetime‘,
              operator: ‘between‘,
              value: this.timeRange
            })
          return JSON.stringify(filters)
      },
      getData(page){
        if(page) this.currentPage = page
        let obj = {
          page: page || this.currentPage,
          size: this.currentSize,
          filters: this.getFilter(),
          order: this.order,
          orderProp: this.orderProp
        }
        this.$http.post(BASE_URL+‘log/list‘, obj, {emulateJSON: true}).then((response) => {
          this.logs = response.data.data
          this.total = response.data.total
          this.logs.forEach(log=>{
            log.type = ‘‘
            for(let i in LOG.type){
              if(log.url.indexOf(i)>=0)
                log.type += LOG.type[i]
            }
            log.operatetime = TIME_FORMATE(log.operatetime)
            if(log.nickname===null) log.nickname=‘--‘
            if(log.account===null) log.account=‘--‘
          })
        }, (response) => {
          // error callback
        });
      },
      handleSizeChange(size){
        this.currentSize = size
        this.getData(1)
      },
      handleCurrentChange(page){
        this.currentPage = page
        this.getData()
      },
      sortChange(column){
          if(column.prop === ‘type‘) column.prop = ‘url‘
          switch (column.order){
            case ‘ascending‘: this.order = ‘asc‘; break
            case ‘descending‘: this.order = ‘desc‘; break
            default: this.order = ‘‘
          }
          this.orderProp = column.prop
          this.getData(1)
      },
      initTypeSelector(){
        this.logTypes = []
        for(let i in LOG.type){
          this.logTypes.push({
            label: LOG.type[i],
            value: i
          })
        }
      },
      selectInit(){
          this.logType = ‘‘
          this.timeRange = []
          this.keyStr = ‘‘
          this.getData(1)
      },
      createTimes(){
          this.pickerOptions.shortcuts = [{
            text: ‘清除时间‘,
            onClick(picker) {
              picker.$emit(‘pick‘, []);
            }
          }]
          this.times.forEach(t=>{
              this.pickerOptions.shortcuts.push({
                  text: ‘最近‘+TOCN(t.value)+t.unit,
                  onClick(picker) {
                    const end = new Date();
                    const start = new Date();
                    start.setTime(start.getTime() - t.value*TIMETYPE[t.unit]);
                    picker.$emit(‘pick‘, [start, end]);
                  }
              })
          })
      }
  },
  created(){
      this.createTimes()
      this.initTypeSelector()
      this.getData()
  }

@app.route(‘/log/list‘, methods=[‘POST‘, ‘OPTIONS‘])
@loginCheck
def list_log():

    if request.method == ‘POST‘:
        page = int(request.form[‘page‘])
        size = int(request.form[‘size‘])
        condition = parseFilters(request)
        order = parseOrder(request)

        dbsession = DBSession()
        sql_str = ‘‘‘SELECT T2.* from(    
            SELECT T.*, rownum RN FROM(
            SELECT * FROM v_log where %s %s 
          )T) T2 WHERE RN BETWEEN :rowBegin and :rowEnd
        ‘‘‘%(condition, order)
        print sql_str
        logs = dbsession.execute(sql_str, {
            ‘rowBegin‘: (page-1)*size+1,
            ‘rowEnd‘: page*size
        })
        total = dbsession.execute(‘select count(*) from v_log where %s‘ %condition).first()[0]

        dbsession.close()
        return jsonify({‘result‘: ‘1‘, ‘data‘: [{
            ‘account‘: log.account,
            ‘nickname‘: log.nickname,
            ‘url‘: log.url,
            ‘post‘: log.post,
            ‘operatetime‘: log.operatetime
        } for log in logs], ‘total‘: total})
    return jsonify({‘result‘: ‘1‘})

后台管理，就没考虑sql注入，写的比较烂

def parseFilters(request):#解析页面传过来的各种条件
    filters = request.form[‘filters‘]
    sqlStr = ‘1=1 ‘
    filters = json.loads(filters)

    for f in filters:

        if f[‘operator‘] == ‘between‘:#特殊处理，接收时间
            timeStr = "to_date(‘%s‘,‘yyyy-MM-dd HH24:mi:ss‘)"
            sqlStr += "and %s between %s and %s" %(f[‘prop‘], timeStr%f[‘value‘][0], timeStr%f[‘value‘][1])

        elif f[‘operator‘] == ‘like‘:
            temp = ‘1=1‘ if f[‘type‘] == ‘and‘ else ‘1=2‘
            for prop in f[‘prop‘]:
                temp += " %s %s like ‘%%%s%%‘"% (f[‘type‘], prop, f[‘value‘])
            sqlStr += ‘and (%s)‘%temp

        elif f[‘operator‘] in [‘=‘, ‘>‘, ‘‘]:
            temp = ‘1=1‘ if f[‘type‘] == ‘and‘ else ‘1=2‘
            for prop in f[‘prop‘]:
                temp += " %s %s %s ‘%s‘" %(f[‘typo‘], prop, f[‘operator‘], f[‘value‘])
            sqlStr += ‘and (%s)‘ % temp
    return sqlStr


def parseOrder(request):
    order = request.form[‘order‘]
    orderProp = request.form[‘orderProp‘]
    if order==‘‘:
        return ‘‘
    else:
        return ‘order by %s %s‘%(orderProp, order)