Flask and HTML - From Flask return sqlite3 query result to HTML and render it with Jinja2

Question

I am trying to achieve continuous loading of my webpage. I have managed to get the JavaScript working so that when the user is on the bottom of my webpage, it calls a Python Flask function to get next pair of data. Where I need your help however is the part to return the results from sqlite3 query back to HTML so that I can iterate over it with Jinja2's {% for post in loadPosts %) command.

This is what I currently have: Python3:

@app.route("/load", methods=["GET", "POST"])
def load():
    conn = sqlite3.connect('imageboard.db')
    c = conn.cursor()
    #Defines what to do when the request is GET
    if request.method == 'GET':
        print('1')
        session['numberOfPosts'] = int(session.get('numberOfPosts')) + 2
        print('2')
        numberOfPostsVariable = str(session.get('numberOfPosts'))
        print('3')
        #loadedPosts = c.execute('SELECT * FROM posts ORDER BY date, time DESC LIMIT 2 {number}'.format(number=numberOfPostsVariable))
        loadedPosts = c.execute('SELECT * FROM posts ORDER BY date, time DESC LIMIT 2 OFFSET ' + str(numberOfPostsVariable))
        for row in loadedPosts:
            print(row)
        print('4')
        return jsonify(list(loadedPosts))
    
    #Defines what to do when the request is POST
    if request.method == 'POST':
        print('1')
        session['numberOfPosts'] = int(session.get('numberOfPosts')) + 2
        print('2')
        numberOfPostsVariable = str(session.get('numberOfPosts'))
        print('3')
        #loadedPosts = c.execute('SELECT * FROM posts ORDER BY date, time DESC LIMIT 2 {number}'.format(number=numberOfPostsVariable))
        loadedPosts = c.execute('SELECT * FROM posts ORDER BY date, time DESC LIMIT 2 OFFSET ' + str(numberOfPostsVariable))
        for row in loadedPosts:
            print(row)
        print('4')
        return jsonify(list(loadedPosts))

HTML / JavaScript / Jinja2:

<script>
    window.onscroll = function(ev) {
    if ((window.innerHeight + window.scrollY) >= document.body.offsetHeight) {
        //Define what to do when user is on the bottom of my webpage
            $.ajax({
            url: '/load',
            data: $('form').serializeArray(),
            type: 'POST',
            success: function(response) {
                $('#response').text(JSON.stringify(response));
                response =  JSON.stringify(response);
                
                var loadNewPostsVariable;
                
                loadNewPostsVariable = '{% for row in response %}<div class="container-fluid" align="center"><span class="badge badge-info">#row[0] / Pridané dňa row[2] o row[1]<br>používateľom row[3]</span><h6>row[4]</h6><p>row[5]</p><img src="{{ url_for("static", filename="images/"+row[6]) }} alt="myText" height="20%" class="img-fluid"></div><p>&nbsp;</p> {% endfor %}'
            
                //Add this <div> element into the body of the webpage
                document.body.innerHTML += loadNewPostsVariable
                console.log(response)
            }
        });
    };
};
</script>

I suppose that the issue could be somewhere between these lines of my HTML / JavaScript code:

success: function(response) {
                $('#response').text(JSON.stringify(response));
                response =  JSON.stringify(response);

The returned data from my sqlite3 query look like this (these are the kind of data I need to be able to iterate over with Jinja2 and continuously show them (without refreshing the webpage)):

(15697753, '14:16:45', '21.7.2020', 'wqw', 'asfsaf', 'fz', '15697753_02.jpg')
(3102620, '14:16:33', '21.7.2020', 'neisor', 'banan', 'asfafs', '3102620_IMG_5952.jpg')

Any help is much appreciated.

Thank you

GAEfan · Accepted Answer · 2020-07-21 19:55:12Z

1

This may need more attention, but first, I think you should try (adding linebreaks for readability):

var loadNewPostsVariable = ` (<--that's a backtick)

{% for row in response %}
    {% set image_name = "images/"+{{ row[6] }} %}

    <div class="container-fluid" align="center">
        <span class="badge badge-info">#{{ row[0] }} / Pridané dňa {{ row[2]}} o {{ row[1] }} 
            <br>
            používateľom {{ row[3] }}
        </span>
        <h6>{{ row[4] }}</h6>
        <p>
            {{ row[5] }}
        </p>
        <img src="{{ url_for("static", filename=image_name }} alt="myText" height="20%" class="img-fluid">
    </div>
    <p>&nbsp;</p> 
{% endfor %}`

answered Jul 21, 2020 at 19:55

GAEfan

11.4k2 gold badges20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Flask and HTML - From Flask return sqlite3 query result to HTML and render it with Jinja2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related