Data from SQLITE to an HTML table in a Flask page

Question

I have a table in SQLite3 and I need to take nearly all the columns in the table and output them to a web page. For other pages I've used flask, but that has always been a case of passing 3 or 4 single value variables into the template.

I'm guessing it's something like passing either the cursor or, more likely, the rows from the cursor.fetchall() call into the template then a for row in rows loop in the template?

Answer 1

It should be:

<table>
{% for item in items %}
    <tr>
        <td>{{column1}}</td>
        <td>{{column2}}</td>
        <td>{{column3}}</td>
        <td>{{column4}}</td>
    </tr>
{% endfor %}
</table>

Answer 2

You need to follow the below:

[HTML]:

<table>
{% for item in items %}
<tr>
    <td>{{item[0]}}</td>
    <td>{{item[1]}}</td>
    <td>{{item[2]}}</td>
    <td>{{item[3]}}</td>
</td>
{% endfor %}

[python code]:

cursor = db.execute('SELECT column1,column2,column3,column4 FROM tablename')
items = cursor.fetchall()
return render_template('print_items.html', items=items)

Answer 3

Pass your data as some sort of collection, whether that's something like a dictionary in the official tutorial, or something simpler, like a tuple or list.

Yes, in your template, you'll use a {% for item in collection %} -> {% endfor %} loop to render out all of the data from your database.

Example Python method:

@app.route('/print_items')
def print_items():
    cursor = db.execute('SELECT column1,column2,column3,column4 FROM tablename')
    return render_template('print_items.html', items=cursor.fetchall())

Example template section:

<table>
{% for item in items %}
    <tr>
        <td>column1</td>
        <td>column2</td>
        <td>column3</td>
        <td>column4</td>
    </td>
{% endfor %}