时间戳

首页 > 解决方案 > 使用查询获取大数据并在 Python 中使用烧瓶显示在 HTML 表中

python - 使用查询获取大数据并在 Python 中使用烧瓶显示在 HTML 表中

问题描述

我在 sql server 中有一个包含 229,000 行的表，我尝试使用select命令和 Flask（在 python 中）以 HTML 格式显示记录的数据。我必须在我的 HTML 表中显示该表的所有记录值，以便相关团队可以看到它的所有数据。我的代码如下所示： 问题是我在这方面使用了分页，但是加载表格需要很长时间，有时浏览器会冻结并停止工作。我很感激是否有人可以在这方面指导我？

sql.py

from datetime import datetime 
from flask import Flask , render_template, request
import pyodbc   
import pypyodbc 
import os
from waitress import serve
from flask import render_template, redirect, request    

app = Flask(__name__)


@app.route('/index', methods=['GET', 'POST'])
def ShowResult():
    # creating connection Object which will contain SQL Server Connection    
    connection = pypyodbc.connect('Driver={SQL Server}; Server=Server; Database=DB; UID=UserID; PWD= {Password};')# Creating Cursor    
    
    cursor = connection.cursor()    
    cursor.execute("""select A,B,C,D from TABLE""")    
    Result=cursor.fetchall()
    return render_template('index.html', Result=Result)


if __name__ == '__main__':
    serve(app,port=5009)

索引.html

<body oncontextmenu='return false' class='snippet-body'>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.2/css/jquery.dataTables.min.css">
<script type="text/javascript" src="https://cdn.datatables.net/1.10.2/js/jquery.dataTables.min.js"></script>
    <div class="container">
        <div class="row header" style="text-align:center;color:green">
            <h3>Bootstrap table with pagination</h3>
        </div>
        <table id="example" class="table table-striped table-bordered" style="width:100%;font-family: tahoma !important;">
          <thead>
                    <tr>
                    <th>A</th>
                    <th>B</th>
                    <th>C</th>
                    <th>D</th>
                    </tr>
                    </thead>
                     
                    <tbody>
                    {% for row in Result %}  
                    <tr>
                    <td>{{row[0]}}</td>
                    <td>{{row[1]}}</td>
                    <td>{{row[2]}}</td>
                    <td>{{row[3]}}</td>
                    <td>{{row[4]}}</td>
                    <td style="word-break:break-all; width: 200px;"><p style="word-wrap: break-word;  word-wrap: break-word; width: 200px;">{{row[5]}}</p></td>
                    </tr>
                    {% endfor %}
                    </tbody>
        </table>
    </div>
                    <script type='text/javascript' src='https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js'></script>
                    <script type='text/javascript'>
                    //$(document).ready(function() {
    //$('#example').DataTable();
    $(document).ready(function() {
        $('#example').DataTable( {
            serverSide: true,
            ordering: false,
            searching: false,
            ajax: function ( Result, callback, settings ) {
                var out = [];
     
                for ( var i=Result.start, ien=Result.start+Result.length ; i<ien ; i++ ) {
                    out.push( [ i+'-1', i+'-2', i+'-3', i+'-4', i+'-5', i+'-6' ] );
                }
     
                setTimeout( function () {
                    callback( {
                        draw: data.draw,
                        data: out,
                        recordsTotal: 5000000,
                        recordsFiltered: 5000000
                    } );
                }, 50 );
            },
            scrollY: 200,
            scroller: {
                loadingIndicator: true
            },
        } );
    } );</script>
</body>

标签： pythonsql-serverflaskpagination

解决方案

当您提取那么多数据时，浏览器将冻结。

为避免这种情况，请使用服务器端分页并向后端提供带有页码的查询。

PAGE_SIZE = 10

def get_paginated_result(page):
    # creating connection Object which will contain SQL Server Connection    
    connection = pypyodbc.connect('Driver={SQL Server}; Server=Server; Database=DB; UID=UserID; PWD= {Password};')  # Creating Cursor    
    cursor = connection.cursor()    

    order_by = 'id'  # should always be validated (if you get from args)
    offset = (page - 1) * PAGE_SIZE
    cursor.execute(
        "select A, B, C, D from TABLE ORDER BY %s OFFSET %d ROWS FETCH NEXT %d ROWS ONLY"
        % (order_by, offset, PAGE_SIZE)
    )    
    Result=cursor.fetchall()
    return result


@app.route('/index', methods=['GET', 'POST'])
def view_index():
    page = abs(int(request.args.get('page')))
    Result = get_paginated_result(page)
    return render_template('index.html', Result=Result)


@app.route('/ajax_table', methods=['GET'])
def ajax_table():
    page = abs(int(request.args.get('page')))
    Result = get_paginated_result(page)
    return render_template('ajax_table.html', Result=Result)

之后，您需要提取表格以分离 HTML 文件ajax_table.html。因此，您将拥有可以分页的主页和表格。对于分页，建议使用Bootstrap 分页并添加 JS 代码以从另一个页面加载/ajax_table并替换表格内容。

推荐阅读