https://github.com/ygidtu/Flask-peewee-datatables上我掛了一個(gè)demo
datatables：官網(wǎng)【https://datatables.net/】
datatables做用展示
如上圖所示闹获，datatables是一個(gè)非常好用的js庫(kù)期犬，方便在網(wǎng)頁(yè)中展示各種結(jié)果
一、安裝
Download：https://datatables.net/download/index
我常用兩種：
- 下載到本地以static文件的方式引入昌罩，但是哭懈，除了默認(rèn)主題，其他的主題會(huì)存在圖片定位不到的問題
- CDN
二茎用、基礎(chǔ)用法
官方最簡(jiǎn)單的教學(xué)：https://datatables.net/examples/basic_init/zero_configuration.html
什么都不加遣总，直接在HTML頁(yè)面中生成一個(gè)table，
<table id="tfbs">
<thead>
<tr>
<th>id</th>
<th>chrom</th>
<th>start</th>
<th>end</th>
<th>eid</th>
<th>tissue</th>
<th>order</th>
<th>predict</th>
</tr>
</thead>
<tbody></tbody>
</table>
然后直接通過JQuery調(diào)用DataTable即可轨功，直接使用
$(document).ready(function() {
$('#tfbs').DataTable( );
} );
當(dāng)然旭斥，各種細(xì)節(jié)也可以自行調(diào)整，具體請(qǐng)自行查詢官方文檔
三古涧、ajax
當(dāng)然垂券，這種成熟的JS庫(kù)當(dāng)然支持ajax的用法，它需要有特定的ajax格式
所需要的特定的json格式羡滑，格式如下：這只是非彻阶Γ基本的格式之一
{
"draw": 1,
"recordsTotal": 57,
"recordsFiltered": 57,
"data": [
[
"Airi",
"Satou",
"Accountant",
"Tokyo",
"28th Nov 08",
"$162,700"
]
}
ajax的JavaScript腳本如下：
$(document).ready(function() {
$('#example').DataTable( {
"processing": true,
"serverSide": true,
"ajax": {
"url": "scripts/jsonp.php",
"dataType": "jsonp"
}
} );
} );
三、Flask的配合問題
現(xiàn)成的Flask的API支持flask-rest柒昏、flask-restful和flask-peewee都無法修正為datatables的格式凳宙，因此，還是得自己動(dòng)手豐衣足食
為了能夠ajax的交互操作职祷，必須搞清楚datatables是通過何種方式向服務(wù)器傳遞參數(shù)的
- 最基礎(chǔ)的氏涩，datatables會(huì)通過在url后附加各種參數(shù)來完成數(shù)據(jù)傳遞
- 可以自己指定向api借口post數(shù)據(jù)
1. 拆分url參數(shù)
以最開始的例子為例届囚，我開了一個(gè)/api/enhancer的接口浊服，默認(rèn)datatables會(huì)通過如下的url獲取數(shù)據(jù)
GET /api/enhancer/?draw=17&columns%5B0%5D%5Bdata%5D=id&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=chrom&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=start&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=end&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=eid&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=tissue&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B6%5D%5Bdata%5D=order&columns%5B6%5D%5Bname%5D=&columns%5B6%5D%5Bsearchable%5D=true&columns%5B6%5D%5Borderable%5D=true&columns%5B6%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B6%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B7%5D%5Bdata%5D=myself&columns%5B7%5D%5Bname%5D=&columns%5B7%5D%5Bsearchable%5D=true&columns%5B7%5D%5Borderable%5D=true&columns%5B7%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B7%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=5&order%5B0%5D%5Bdir%5D=desc&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1510720968919
返回的數(shù)據(jù)data類惭等，如下：
返回的數(shù)據(jù)格式
目前蛀醉，已經(jīng)拆分出幾個(gè)參數(shù)
- draw：頁(yè)數(shù)尿背，所需第幾頁(yè)的數(shù)據(jù)
- %5B、%5D：
- 根據(jù)Stack OverFlow所講力惯，分別代表[, ]
- columns%5B0%5D%5Bdata%5D=id：
- 原義為columns[0][data]=id高每，經(jīng)過
cprint(get_parameter("columns[0][data]"), 'red')證實(shí)松邪，確實(shí)如此首繁。這一大段作郭，表明我的表格的第一列使用的是json中key值為id的這一項(xiàng)的數(shù)據(jù)陨囊，
- 原義為columns[0][data]=id高每，經(jīng)過
- columns%5B0%5D%5Bname%5D=：
- columns[0][name]=弦疮，這一項(xiàng)不是何意，估計(jì)是可以該列名
- columns%5B0%5D%5Bsearchable%5D=true：
- columns[0][searchable]=true蜘醋，能夠查詢
- columns%5B0%5D%5Borderable%5D=true：
- columns[0][orderable]=true胁塞，該列能夠排序
- columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=：
- columns[0][search][value]= ，按照什么數(shù)據(jù)來查詢?cè)摿?/li>
- columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false：
- columns[0][search][regex]=false压语，該列是否通過正則來匹配
- 然后重復(fù)啸罢，因此，通過這些調(diào)整就將各行各列的所有數(shù)據(jù)都調(diào)整好了
2. 參數(shù)的詳細(xì)用法
首先胎食，將數(shù)據(jù)只調(diào)整到一列扰才，開始解析各個(gè)參數(shù)的詳細(xì)用法
order
通過調(diào)整order調(diào)整到這樣一條url
api/enhancer/?draw=1&columns%5B0%5D%5Bdata%5D=id&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1510727303124
order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc：
order[column]=0&order[0][dir]=asc或者dir=desc
- draw：第幾次向該api發(fā)出請(qǐng)求
- order：column指定第幾列調(diào)整順序，order[0][dir=asc]或者order[0][dir=desc]指定排序方式
- start=0&length=10厕怜，length 代表每一頁(yè)上有多少數(shù)據(jù)衩匣，start是這一頁(yè)最開始的那一條是第幾條
search
GET /api/enhancer/?draw=13&columns%5B0%5D%5Bdata%5D=id&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=chrom&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=1&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=chr1&search%5Bregex%5D=false&_=1510727522537 HTTP/1.1
不出所料
- search%5Bvalue%5D=chr1
- search[value]=chr1
3. Python配置
通過以上的參數(shù)，相信參數(shù)的事情就解析清楚了粥航，那么就可以自行動(dòng)手琅捏，設(shè)計(jì)API來處理這些數(shù)據(jù)了，我使用的是peewee來處理底層的數(shù)據(jù)庫(kù)递雀，因此柄延，寫了一個(gè)類來處理這個(gè)問題
用法已經(jīng)放在github庫(kù)上了：https://github.com/ygidtu/Flask-peewee-datatables
#!/usr/bin/env python3
# -*- coding:utf-8 -*-
u"""
serverside的datatables配合flask和peewee使用的方法
"""
import re
from collections import OrderedDict
from flask import request
from peewee import BooleanField
from peewee import IntegerField
from peewee import FloatField
# flask配合datatables使用的類
class WorkWithDataTables(object):
u"""
2017.11.15
開始設(shè)計(jì)類，想辦法自行處理dtatables返回的參數(shù)
"""
def __init__(self, table, columns=None, join=None):
u"""
初始化
:param columns: 列表缀程，傳入查詢的columns搜吧， 如果不指定columns，默認(rèn)使用表中所有的列
:param join: 列表杨凑，傳入查詢的如果需要join表滤奈，可以通過這個(gè)join指定實(shí)現(xiàn)，目前只能join一個(gè)表
"""
# 構(gòu)建
if columns is None:
columns = list(table._meta.fields.values())
if join:
columns += list(join._meta.fields.values())
start = 0
tem = []
for i in columns:
tem.append([start, i])
start += 1
self.columns = OrderedDict(tem)
self.table = table
self.join = join
@staticmethod
def get_parameter(label, default=None):
u"""
:param label: url中指定的參數(shù)是
:param default: 如果url中沒有這個(gè)參數(shù)蠢甲，返回的默認(rèn)值僵刮，一般為None
:return:返回strip之后的參數(shù)
"""
tem = request.args.get(label)
if tem is None:
return default
return tem.strip()
def _searchable_(self, index):
u"""
datatables可以通過JavaScript的設(shè)置指定某些列可以被查詢据忘，某些不可以，
于是變有了這個(gè)函數(shù)搞糕，只有能夠被查詢的列勇吊，我才會(huì)查詢
"""
if self.get_parameter("columns[%d][searchable]" % index) == "true":
return True
else:
return False
def _set_order_(self):
u"""
根據(jù)datatables提交的參數(shù)，判定查詢結(jié)果的排序方式
:return: 返回特定排序方式的peewee Expression語句
"""
# 指定排序方式
order = self.get_parameter("order[0][column]")
order_by = None
if order:
order = int(order)
order_by = self.columns.get(order)
order_dir = self.get_parameter("order[0][dir]")
if order_by and order_dir == "desc":
order_by = order_by.desc()
return order_by
def _set_search_(self):
u"""
根據(jù)url參數(shù)窍仰，設(shè)定查詢的語句
:return:peewee的查詢Expression語句
"""
# 指定查詢的值是多少
search = self.get_parameter("search[value]")
# 判斷一下輸入的是否是boolean值汉规，true、false驹吮、yes针史、no均可，不分大小寫
if search is not None:
if re.search(r"^(true|yes)$", search, re.I):
search = True
elif re.search(r"^(false|no)$", search, re.I):
search = False
else:
search = search
#指定查詢的peewee Expression語句
search_condition = None
if search: ＃如果開啟search需求
for i in self.columns: # 遍歷所有的列碟狞，然后分別根據(jù)特定情形指定查詢語句啄枕，目前僅支持bool、float族沃、int
if self._searchable_(i):
# 如果遇到布爾型频祝，需要獨(dú)特的處理方法
if isinstance(self.columns[i], BooleanField) and\
not isinstance(search, bool):
if search_condition is None:
search_condition = (self.columns[i] == None)
else:
search_condition = (
self.columns[i] == None) | search_condition
continue
# if this column is integer type
if isinstance(self.columns[i], IntegerField):
try:
search = int(search)
if search_condition is None:
search_condition = (self.columns[i] == search)
else:
search_condition = (
self.columns[i] == search
) | search_condition
except:
continue
continue
# if this column is float type
if isinstance(self.columns[i], FloatField):
try:
search = float(search)
if search_condition is None:
search_condition = (self.columns[i] == search)
else:
search_condition = (
self.columns[i] == search
) | search_condition
except:
continue
continue
# 如果符合要求就不用管了，正常處理即可
if search_condition is None:
search_condition = (
self.columns[i].regexp(str(search) + ".*"))
else:
search_condition = (self.columns[i].regexp(
str(search) + ".*")) | search_condition
return search_condition
def query(self, condition=None, search=True, order=None, **kwargs):
u"""
查詢語句
:param condition: 外部指定的特定查詢要求脆淹，peewee Expression
:param search: boolean值常空，在后期是否能支持datatables的查詢框輸入值查詢
:param order: 指定初始化的順序，如果不設(shè)定盖溺，就使用默認(rèn)順序
:return:
"""
# 獲取參數(shù)
page = int(self.get_parameter("start", 1)) / 10 + 1
per_page = int(self.get_parameter("length", 10))
draw = int(self.get_parameter("draw", 1))
querys = self.table.select(*self.columns.values())
if order:
querys = querys.order_by(order)
# 指定非第一次的查詢漓糙，才會(huì)通過這個(gè)排序方式排序
if draw > 1:
order = self._set_order_()
querys = querys.order_by(order)
# 如果有外鏈表，就外鏈
if self.join:
querys = querys.join(self.join)
# 如果有查詢條件或者搜索條件烘嘱，就分別指定不同的條件
if condition and search:
search_condition = self._set_search_()
if search_condition:
condition = condition & search_condition
elif search:
condition = self._set_search_()
if condition:
querys = querys.where(condition)
# 如果有的表數(shù)據(jù)量比較大昆禽，查詢起來特別慢，就可以手動(dòng)指定一個(gè)值拙友，加快速度
if kwargs.get("total") is not None:
total = kwargs["total"]
else:
total = querys.count()
querys = querys.paginate(page, per_page)
# 構(gòu)建最終的json文件
data = [x for x in querys.dicts()]
return {
"data": data,
"draw": draw,
"start": page,
"length": per_page,
"recordsTotal": total,
"recordsFiltered": total
}
