sqlite db called from flask only returns variables, not values

Question

I have a flask app that queries a sqlite db:

@app.route('/<subject_id>')
def subject_id_lookup(subject_id):
    entries = query_db('select visitdt, cvnotes from exam where id = ?',
                        [subject_id], one=True)
    return render_template('show_results.html', entries = entries)

I am using flask functions largely unchanged from the docs including query_db()

def query_db(query, args=(), one = False):
    """Queries the database and returns a list of dictionaries"""
    cur = g.db.execute(query, args)
    rv = [dict((cur.description[idx][0], value)
        for idx, value in enumerate(row)) for row in cur.fetchall()]
    return (rv[0] if rv else None) if one else rv

Finally here is my show_results.html file:

{% extends "layout.html" %}
{% block body %}
    <ul class=entries>
        {% for entry in entries %}
        <li><h2>{{ entry }}</h2>
        <br>
        {% else %}
        <li><em>No entry here</em>
        {% endfor %}
    </ul>
    {% endblock %}

The query runs fine but the nothing is returned except the variable names visitdt & cvnotes. When I change the line above to <li><h2>{{ entry.cvnotes }}</h2>, it returns nothing. How can I modify my query to show results from my subject_id_lookup() function?

Answer 1

The issue is that query_db returns different things depending on whether you specify one=True or one=False.

>>> query_db(your_query, [some_id], one=True)
{visittd: "a value", cvnotes: "some notes"}

>>> query_db(your_query, [some_id], one=False)
[{visittd: "a value", cvnotes: "some notes"}] # Note the wrapping list

When you enumerate over a dictionary the result is the keys in the dictionary - when you enumerate over a list, the result is the entries in the list.

>>> for thing in query_db(your_query, [some_id], one=True):
...    print thing
visitdt
cvnotes

>>> for thing in query_db(your_query, [some_id], one=False):
...    print thing
{visittd: "a value", cvnotes: "some notes"}

If you want to use the same template and you know there is only going to be one value returned for one id (or if you are fine with dealing with more than one value) simply remove the one=True keyword argument in subject_id_lookup. entries will then be a list of one dictionary containing the keys visitdt and cvnotes - when you iterate over it in your template each entry will be a result dictionary (rather than a key in the single result dictionary) and {{ entry.cvnotes }} will work.