Issue
I have create a test website using Flask that displays data from a postgres database, ordered by category, product, month and date. All the table rows below each category are hidden. Clicking on a row will expand/collapse 1 level of rows beneath it. So, clicking on a category will show all the products. Clicking on a product will show all the months with data.
Currently, as you can see in the code, all the records are retrieved when the page is loaded.
The test website is a simplification of a website that displays thousands of of records. I would like to use the website for a different db with millions of records.
Unfortunately, the page would take far to long to load and expand/collapse. Instead of collapsing/expanding the rows when clicked, I would like to query the db using Flask each time a category, product or month is expanded and add 1 level of rows to the table with the data returned by a query.
Can this be done with Flask, jinja2 and js?
html template with jinja2 code
<!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 code
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');};};
});
});
});
Solution
I was able to do this using XMLHTTPRequests with Flask and JS.
Answered By - jgm_GIS
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.