Ajax call to Python Flask function does not load sqlite table without page refresh

Question

I have a Flask project and need to load a SQLite table into an HTML table. I need the table to be loaded very frequently in order to get real time information of what's in the table; however, I do not want to refresh the entire page in order to do this.

Right now I am using an ajax call every five seconds; the call is successfully going through every five seconds (I know this because I put a print hello statement in the Flask method it is calling to see if it's being called and it is, but my HTML table does not load the updated database unless I refresh the whole page. So the ajax call goes through, but the HTML is not rendering with the new database information (I purposely change the database state to test this). If I refresh the page using the browser's reload button, however, then the HTML table will update and show the new contents of the SQlite table.

Here is my HTML code:

<table class="table table-responsive table-hover">
<thead>
  <tr>
    <th>Incident #</th>
    <th>Username</th>
    <th>Description</th>
    <th>Resolve Incident</th>
  </tr>
</thead>
<tbody>
  {% for item in items %}
  <tr>
    <td>{{item[0]}}</td>
    <td>{{item[1]}}</td>
    <td>{{item[2]}}</td>
    <td>
      <button type="button" class="btn btn-primary btn-xs" data-toggle="modal" data-target="#ModalResolveForm">Resolve Ticket</button>
    </td>
  </tr>
  {% endfor %}
</tbody>
</table>

Here is my ajax call:

function loadDatabase(){
window.setInterval(function(){
    $.ajax({
      url: "/queue",
      type: "GET"
    });
}, 5000);
}

And here is my Flask views.py method:

@app.route('/queue', methods=['GET', 'POST'])
def addQueue():

    conn = sqlite3.connect('snow_db.db')
    cur = conn.cursor()

    cur.execute("SELECT username, description, incident FROM tickets")
    items = cur.fetchall()

    print("hello")

    # return jsonify({'array' : items})
    return render_template("queue.html", items = items)

As you can see from the commented out code in the Flask function, I have also tried returning the items in Json format and actually was able to load the HTML table from this Json; however, I had the same issue of the table not loading the updated Json/sqlite data unless I did an actual page refresh.

All your help is appreciated as I have been trying tirelessly to figure out this problem, but cannot. Thank you!

score 1 · Answer 1 · answered Jul 24 '17 at 01:11

That is completely normal:

Flask renders a template, and returns the HTML.
The browser renders the HTML.

An AJAX request is strictly calling the server and getting data. Then it's up to you to handle the data received.

Here your data is the rendered template, but you're not doing anything with it when you receive it.

Ideally, you should pull the table DATA only through the ajax call, and render the table on the client side (using a Javascript library, like JQuery, or a framework like Angular / React...)

For a quick hack, you could simply replace the body HTML with the HTML you received.

you'd need to parse the HTML into an element.

function loadDatabase(){
window.setInterval(function(){
    $.ajax({
      url: "/queue",
      type: "GET"
    }, function(data)) {
      // do something with data
});
}, 5000);
}

you can do something like:

// create a div element
var doc = Document.createElement('div');
// put the html into the element
doc.innerHtml = data;
//and then you need to lookup your table or at least the body element, because you're getting the whole HTML.
var body = doc.findElementByTagName('body')
// and put that in the actual document body
document.body.innerHtml = body.innerHtml;

Again, this is a quick hack and you should not be doing this but rather update the table only, by rendering it on the client/browser side, in javascript.

Ajax call to Python Flask function does not load sqlite table without page refresh

1 Answers1