我使用Flask创建了一个测试网站，该网站显示来自postgres数据库的数据，按类别，产品，月份和日期排序。每个类别下面的所有表行都是隐藏的。单击一行将展开/折叠它下面的一行1层。所以,点击一个类别将显示所有产品。点击一个产品将显示所有月份的数据。
当前，正如您在代码中看到的，所有记录都是在加载页面时检索的。
测试网站是对显示数千条记录的网站的简化。我想使用不同的数据库与数百万条记录的网站。
不幸的是，页面需要很长时间才能加载和展开/折叠。而不是崩溃/扩展行点击时，我想查询数据库使用Flask每次一个类别，产品或月被扩展，并添加1级行与查询返回的数据表。
这可以用Flask, jinja2和js来完成吗?
html模板与jinja2代码
<!DOCTYPE html>
<html>
<meta name="viewport" content="width=device-width, initial-scale=1">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script type=text/javascript src="{{url_for('static', filename='js/test.js') }}"></script>
<link rel= "stylesheet" type= "text/css" href= "{{ url_for('static',filename='styles/test.css') }}">
<title>Test Table</title>
<body>
<table class = "products">
<thead>
<tr>
<th>Product</th>
<th>Month</th>
<th>Date</th>
</tr>
</thead>
<tbody>
{% for category in types %}
<tr class="category" data-level="0" vis_id = "{{ category.category}}">
<td colspan = 3 class="nosides">
<label>{{ category.category}}</label>
</td
</tr>
{% for product in category.products %}
<tr class="parent" data-level="1" vis_id = "{{ product.product }}" par_vis_id = "{{ category.category}}">
<td colspan = 3 class="nosides">
<label>{{ product.product }}</label>
</td>
</tr>
{% for month in product.months %}
<tr class="parent" data-level="2" vis_id = "{{ product.product }}_{{ month.month }}" par_vis_id = "{{ product.product }}">
<td colspan=1 class="noright"></td>
<td colspan=2 class="nosides">
<label>{{ month.month }}</label>
</td>
</tr>
{% for date in month.date %}
<tr class="child" data-level="3" vis_id = "{{ date.date }}" par_vis_id = "{{ product.product }}_{{ month.month }}">
<td colspan = 2></td>
<td>{{ date.date }}</td>
</tr>
{% endfor %}
{% endfor %}
{% endfor %}
{% endfor %}
</tbody>
</table>
</body>
</html>
Python Flask代码
import sys, os
from flask import Flask, render_template
import pandas as pd
import pandas.io.sql as psql
@app.route('/test_table')
def test_table():
con = set_con('db')
sys_list = []
#select all the records
df = pd.read_sql("select category, product, TO_CHAR(date, 'Mon YYYY') as month, TO_CHAR(date, 'YYYY-MM-DD HH24:MI:SS') as date
from db order by category, product, date desc", con)
query = "Select distinct product from db order by product"
#get a list of categories, products and months, then loop over each.
df_products = pd.read_sql(query, con)
query = "Select distinct category from products"
df_sys = pd.read_sql(query,con)
for category in df_sys['category'].values:
products_list = []
df_sys_tmp = df[df['category']==category]
if df_sys_tmp.empty:
continue
for product in df_products['product'].values:
df_product_tmp = df_sys_tmp[df_sys_tmp['product']==product]
if df_product_tmp.empty:
continue
months = df_product_tmp['month'].unique()
tmp_list_months = []
for month in months:
#loop over all the records for this particular category, product, and month.
df_recs = df_product_tmp[df_product_tmp['month']==month]
if df_recs.empty:
continue
tmp_list_recs = []
for i, row in df_recs.iterrows():
tmp_list_recs.append({'date':row['date']})
tmp_list_months.append({'month':month, 'date':tmp_list_recs})
products_list.append({'product':product, 'months':tmp_list_months})
sys_list.append({'category':category, 'products':products_list})
return render_template(test.html',types=sys_list)
if __name__ == '__main__':
main()
js
function toggleIf(element, condition) {
if (condition ===true || condition === 'true') { element.show(); }
else { element.hide(); }
};
$(document).ready(function() {
function getChildren($row) {
var children = [], level = $row.attr('data-level');
while($row.next().attr('data-level') > level) {
children.push($row.next());
$row = $row.next();
};
return children.sort((a, b) => parseFloat(a.attr('data-level')) - parseFloat(b.attr('data-level')));;
};
$('.parent').on('click', function() {
var children = getChildren($(this));
var datalevel = $(this).attr('data-level')
var vis_id = $(this).attr('vis_id')
$.each(children, function() {
par_vis_id = $(this).attr('par_vis_id')
if (datalevel==1){
if ($(this).attr('data-level')==2){
$(this).toggle();}
else{
if ($(this).is(":visible")){
$(this).toggle();}}}
else{$(this).toggle();}
sessvisible = $(this).is(":visible")
if (sessvisible) {
sessionStorage.setItem(par_vis_id, 'true')
}
else{
try {
sessionStorage.removeItem(par_vis_id);}
catch { };};});
});
$('.product').on('click', function() {
var children = getChildren($(this));
var vis_id = $(this).attr('vis_id')
$.each(children, function() {
item_visible = $(this).is(":visible")
if ($(this).attr('data-level')==1){
$(this).toggle();
product_visible = $(this).is(":visible")
if (!product_visible) {
try {
sessionStorage.removeItem($(this).attr('vis_id'));}
catch { };}} else{
if (item_visible){
$(this).toggle();}
try {
sessionStorage.removeItem($(this).attr('vis_id'));}
catch { };
}})
if (product_visible) {
sessionStorage.setItem(vis_id, 'true')
}
else{
try {
sessionStorage.removeItem(vis_id);}
catch { };};
});
$('.parent').on('custom', function() {
var children = getChildren($(this));
var datalevel = $(this).attr('data-level')
var vis_id = $(this).attr('vis_id')
$.each(children, function() {
if (datalevel==1){
if ($(this).attr('data-level')==2){
$(this).toggle();}
else{
if ($(this).is(":visible")){
$(this).toggle();}}}
else{$(this).toggle();}
});
});
$('.product').on('custom', function() {
var children = getChildren($(this));
var vis_id = $(this).attr('vis_id')
$.each(children, function() {
item_visible = $(this).is(":visible")
if ($(this).attr('data-level')==1){
$(this).toggle();}
else{
if (item_visible){
$(this).toggle();}}
});
});
$('.product').each(function(i, tr) {
var isvisible = $(this).is(":visible")
var children = getChildren($(this));
var vis_id = $(this).attr('vis_id')
if (sessionStorage.getItem(vis_id) !== null){
$(this).trigger('custom');
}
$.each(children, function() {
var datalevel = $(this).attr('data-level')
if (datalevel !== '3'){
var vis_id = $(this).attr('vis_id')
if (sessionStorage.getItem(vis_id) !== null){
$(this).trigger('custom');};};
});
});
});
我能够使用Flask和JS的xmlhttprequest来做到这一点。