Populating html page with database rows

Question

I am trying to send the results of an SQLite query to a Jinja2 template through Flask. To do this I execute .fetchall() on my query, then I pass the result of that to render_template() as an argument. On the html template, I iterate through each row that was passed in order to show the 'title' attribute of each row. The expected behavior is that the final html will be populated with the data passed through render_template(). I'm pretty sure I've done this exactly as it is shown on the tutorial project in the official Flask docs.

This is the code that is fetching the rows from the database.

@bp.route('/', methods=('GET',))
def archive():
"""Returns a page with all post titles, each one is a link to it's respective blog page"""
db = get_db()

rows = db.execute('SELECT id, title, Timestamp FROM blog ORDER BY Timestamp DESC').fetchall()

return render_template('archive.html', rows=rows)

And here is the Jinja2 template I am trying to populate

<!--Template for archive page; displays a link to every post on the blog-->
{% extends 'blog.html' %}

{% block header %}
    <h1>All Posts<h1>
{% endblock %}

{% block body %}
    {% for row in rows %}
        {% if row['title'] %}
            <h2>{{ row['title'] }}</h2>
        {% endif %}
    {% endfor %}
{% endblock %}

Edit: Here is my get_db() method

def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect(
                current_app.config['DATABASE'],
                detect_types=sqlite3.PARSE_DECLTYPES
        )
        g.db.row_factory = sqlite3.Row

    return g.db

v25 v25 · Accepted Answer · 2020-07-23T22:39:15

    {% for row in rows %}
        {% if row['title'] %}
            <h2>{{ row['title'] }}</h2>
        {% endif %}
    {% endfor %}

This is incorrect because it assumes each item in the rows list is a dictionary which may have a key title.

The return value of the fetchall method is actually a list of tuples. You could add the debug line print (rows) before you render the template to observe this. You'll find it's looks like this:

[(1, 'First Post', 1579817674),
 (2, 'Second Post', 1579817682),
 (3, 'Final Post', 1579817693)
]

So the equivelant code for what you're trying to do would be:

{% for row in rows %}
  {% if row[1] %}
    <h2>{{ row[1] }}</h2> 
  {% endif %}
{% endfor %}

Of course this has the disadvantage that your template code assumes the order of columns returned by the SQL query. If you change that query and add another column to the results, you'd need to change the numeric key in the template.

A better approach might be to use a row factory. So your Python code becomes:

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

@bp.route('/', methods=('GET',))
def archive():
    db = get_db()
    
    # Set this per route, or in your `get_db` method
    db.row_factory = dict_factory

    result = db.execute('SELECT id, title, Timestamp FROM blog ORDER BY Timestamp DESC')
    rows  = result.fetchall()
    
    print (rows)
    
    return render_template('archive.html', rows=rows)

You'll now find that rows is the list of dictionaries you expected:

[{'id': 1, 'title': 'First Post', 'Timestamp': 1579817674},
 {'id': 2, 'title': 'Second Post', 'Timestamp': 1579817682},
 {'id': 3, 'title': 'Final Post', 'Timestamp': 1579817693},
]

Your original template code should work with this.

Populating html page with database rows

2 Answers