求助：用Ajax+jQuery实现Flask中SQLite数据无刷新实时展示

Hey there! I see you're working on a real-time dashboard with Flask and SQLite, and you want to avoid SocketIO by using Ajax + jQuery instead. Let's fix this up step by step—here's how to modify your code to get seamless, no-refresh updates:

First, Update Your Flask Backend #

The main issue with your current /get_data route is that it returns raw text instead of structured JSON, which makes it hard for jQuery to parse. We'll adjust it to return JSON, and optimize the database query to fetch only the latest record (no need to loop through all rows):

from flask import Flask, render_template, request, jsonify  # Add jsonify for JSON responses
app = Flask(__name__)
app.debug = True
import sqlite3
import datetime
import time

# Retrieve latest data from database
@app.route("/get_data")
def getData():
    conn = sqlite3.connect('sagemdb')
    curs = conn.cursor()
    # Fetch only the most recent record (adjust ORDER BY to your actual time column if needed)
    curs.execute("SELECT * FROM STBdata ORDER BY rowid DESC LIMIT 1")
    row = curs.fetchone()
    conn.close()

    # Handle empty database case
    if row:
        DateTimee = str(row[0])
        memUsage = row[1]
    else:
        DateTimee = "No data available"
        memUsage = 0

    # Return structured JSON
    return jsonify({
        'time': DateTimee,
        'memUsage': memUsage
    })

# Main route with initial data
@app.route("/")
def index():
    # Fetch initial data for template rendering
    conn = sqlite3.connect('sagemdb')
    curs = conn.cursor()
    curs.execute("SELECT * FROM STBdata ORDER BY rowid DESC LIMIT 1")
    row = curs.fetchone()
    conn.close()

    DateTimee1 = str(row[0]) if row else "No data available"
    memUsage1 = row[1] if row else 0

    templateData = {
        'time': DateTimee1,
        'memUsage': memUsage1,
    }
    return render_template('index.html', **templateData)

if __name__ == "__main__":
    app.run(host='127.0.0.1', port=4000)

Next, Modify Your HTML to Add Ajax Updates #

We'll add jQuery (for easy Ajax calls) and a script that periodically fetches new data and updates the page without reloading:

<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Highstock Example</title>
<!-- Include jQuery (you can also download it locally instead of using CDN) -->
<script src="https://code.jquery.com/jquery-3.6.4.min.js"></script>
<script>
$(document).ready(function() {
    // Function to fetch and update data
    function refreshDashboard() {
        $.ajax({
            url: '/get_data',
            type: 'GET',
            dataType: 'json',
            success: function(data) {
                // Update the time and memory usage elements
                $('h3:first').text(data.time);
                $('h3:eq(1)').text(data.memUsage);
                
                // Optional: If you want to update your Highstock chart, add code here
                // Example (adjust based on your chart setup):
                // const timestamp = new Date(data.time).getTime();
                // chart.series[0].addPoint([timestamp, data.memUsage], true, true);
            },
            error: function() {
                console.log('Oops, failed to load new data!');
            }
        });
    }

    // Run once on page load, then every 5 seconds (adjust interval as needed)
    refreshDashboard();
    setInterval(refreshDashboard, 5000); // 5000ms = 5 seconds
});
</script>
</head>
<body>
<h3>{{time}}</h3>
<h3>{{memUsage}}</h3>
<div id="container" style="height: 400px; min-width: 310px"></div>
</body>
</html>

Key Changes Explained: #

• Flask JSON Response: Using jsonify ensures the backend sends data in a format jQuery can easily read, instead of raw text.
• Optimized Query: ORDER BY rowid DESC LIMIT 1 grabs only the latest record (replace rowid with your actual time column name if you have one, like datetime, for better accuracy).
• Ajax Auto-Refresh: The refreshDashboard function runs every 5 seconds (adjust the 5000 value to change the interval) to fetch new data and update the page elements dynamically.
• Error Handling: Added a simple error log to help debug if the Ajax call fails.

Quick Tips: #

• Test with your browser's dev tools (F12 → Network tab) to check if the /get_data request is working and returning the right data.
• If you're using Highstock, uncomment and adjust the chart update code in the Ajax success callback to add new data points to your graph.

内容的提问来源于stack exchange，提问作者hamdi