Updating HTML Table Every 60 Seconds

I am trying to use an ajax call to update an HTML table every 60 seconds on a flask app. I am very new to flask and jquery, and followed this similar stackoverflow question: Python Flask Refresh table every 60 seconds

However my table isn’t displaying any data.

Currently my app.py file is setup as below:

import MySQLdb
import sshtunnel
from datetime import date
import json

app = Flask(__name__)

@app.route("/")
def home():
    return render_template("nfl_template.html")

@app.route("/nfl")
def nfl():
                
    return render_template("nfl_template.html")


@app.route("/nfl_data")
def nfl_data():
        sshtunnel.SSH_TIMEOUT = 10
        sshtunnel.TUNNEL_TIMEOUT = 10
        data = ((),)
        with sshtunnel.SSHTunnelForwarder(
            ('ssh.pythonanywhere.com'),
            ssh_username='USER', ssh_password='***',
            remote_bind_address=('USER.mysql.pythonanywhere-services.com', 3306)
            ) as tunnel:
                connection = MySQLdb.connect(
                    user='USER',
                    passwd='***',
                    host='127.0.0.1', port=tunnel.local_bind_port,
                    db='USER$liveSports',
                )
            
                cursor = connection.cursor()
                query = ("""
                      SELECT * FROM nfl_data WHERE DATE(nfl_data.date)='{}'
                      """.format(str(date.today())))
                cursor.execute(query)
                data = cursor.fetchall()
        return json.dumps(data)

​x
 
import MySQLdbimport sshtunnelfrom datetime import dateimport json​app = Flask(__name__)​@app.route("/")def home():    return render_template("nfl_template.html")​@app.route("/nfl")def nfl():                    return render_template("nfl_template.html")​​@app.route("/nfl_data")def nfl_data():        sshtunnel.SSH_TIMEOUT = 10        sshtunnel.TUNNEL_TIMEOUT = 10        data = ((),)        with sshtunnel.SSHTunnelForwarder(            ('ssh.pythonanywhere.com'),            ssh_username='USER', ssh_password='***',            remote_bind_address=('USER.mysql.pythonanywhere-services.com', 3306)            ) as tunnel:                connection = MySQLdb.connect(                    user='USER',                    passwd='***',                    host='127.0.0.1', port=tunnel.local_bind_port,                    db='USER$liveSports',                )                            cursor = connection.cursor()                query = ("""                      SELECT * FROM nfl_data WHERE DATE(nfl_data.date)='{}'                      """.format(str(date.today())))                cursor.execute(query)                data = cursor.fetchall()        return json.dumps(data)​​​

And my nfl_template.html:

<!doctype html>
<html>
 <body>
 <script>
     setInterval(function() {
    $.ajax({
      type: "GET",
      url: "/nfl_data",
    })  .done(function( data ) {
        console.log(data);
        var tableHtml = '';
        for (row in data) {
          tableHtml += '<tr>';
          for (d in row) {
            tableHtml += '<td>' + d + '</td>';
          }
          tableHtml += '</tr>';
        }
        $("#table-box").html(tableHtml)
       }).fail(function(jqXHR, textStatus, errorThrown) {
         console.log(jqXHR, textStatus, errorThrown);
       });
      }, 1000 * 60); 
  </script>
    
    <table border="2" cellpadding="4" cellspacing="5" id="table-box">
        <th>Game Date</th>
        <th>Game Time</th>
        <th>Team 1</th>
        <th>Team 2</th>
        <th>Team 1 Score</th>
        <th>Team 2 Score</th>
        <th>Team 1 Odds</th>
        <th>Team 2 Odds</th>
        {% for row in data %}
            <tr>
            {% for d in row[:-2] %}
                <td>{{ d }}</td>
            {% endfor %}
            </tr>
        {% endfor %}
    </table>

 </body>
</html>

 
<!doctype html><html> <body> <script>     setInterval(function() {    $.ajax({      type: "GET",      url: "/nfl_data",    })  .done(function( data ) {        console.log(data);        var tableHtml = '';        for (row in data) {          tableHtml += '<tr>';          for (d in row) {            tableHtml += '<td>' + d + '</td>';          }          tableHtml += '</tr>';        }        $("#table-box").html(tableHtml)       }).fail(function(jqXHR, textStatus, errorThrown) {         console.log(jqXHR, textStatus, errorThrown);       });      }, 1000 * 60);   </script>        <table border="2" cellpadding="4" cellspacing="5" id="table-box">        <th>Game Date</th>        <th>Game Time</th>        <th>Team 1</th>        <th>Team 2</th>        <th>Team 1 Score</th>        <th>Team 2 Score</th>        <th>Team 1 Odds</th>        <th>Team 2 Odds</th>        {% for row in data %}            <tr>            {% for d in row[:-2] %}                <td>{{ d }}</td>            {% endfor %}            </tr>        {% endfor %}    </table>​ </body></html>​​

When I run the app, I get a page showing just the table headers but no data. I know that the SQL Query is correct and is returning data as I tested it separately, but using the ajax request I’m not getting any data to display.

If I run the SQL query inside the nfl route function and pass the data as an argument to the render_template the data displays, but it is not updating the table every 60 seconds.

Answer

If that is literally your entire template, then the problem is that you haven’t imported jQuery, so the “$” function doesn’t exist. You need to add:

<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
</head>

 
<head><script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script></head>​

Advertisement

Answer