Home > front end >  Updating HTML Table Every 60 Seconds
Updating HTML Table Every 60 Seconds

Time:10-19

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)


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>

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.

CodePudding user response:

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>
  • Related