loop through peewee results in flask

Question

based on this reply:

cursor = db.execute_sql('select * from tweets;')
for row in cursor.fetchall():
    print row

cursor = db.execute_sql('select count(*) from tweets;')
res = cursor.fetchone()
print 'Total: ', res[0]

from: Python Peewee execute_sql() example

how to take it to flask app and then display in a webpage?

is this correct:

model.py

def get_statistics():
        cursor = finDB.execute_sql('CALL Allstatistics;')
        for row in cursor.fetchall():
                return row

app.py

@app.route("/finance")
def finance():
        stats = model.get_statistics()
        return render_template('/finance.html', stats=stats)

but how to display it in a table?

you want to display them in a html table?, please show how the stats look like? stats is for what data type?? a list? a dict? — Espoir Murhabazi, Jan 05 '18 at 13:41
Yes in HTML , when I run this stored procedure directly in mysql, I get 6 columns and 22 rows: text | -55585.73 | -24834.28 | -2069.94 | -895.19 | NULL — michal, Jan 05 '18 at 14:09
I'm obviously not... in my peewee model: def get_tags(self): return tag_list.select(), gives me output from the table, then in flask app.py I just do output = model.get_tags() and then in app route: @app.route(.... output=output...), then on a webpage I can loop through using column headers {{ row.header }} but none of this works with db.execute_sql() method. — michal, Jan 05 '18 at 15:14
`for row in cursor.fetchall(): return row` doesn't look quite right to me. — bgse, Jan 05 '18 at 15:16
this is from other reply by @coliefer, and he designed it, so should know best... — michal, Jan 05 '18 at 15:19
@michal There is a difference, in his reply he `print` the rows in his loop... your function is supposed to `return` all rows, but will only return the first one. — bgse, Jan 05 '18 at 15:22
@bgse, I've noticed this, but If I use print instead of return I get error when loading the page: TypeError: 'NoneType' object is not iterable — michal, Jan 05 '18 at 15:24
@michal See answer below for explanation of the root cause of your problem. — bgse, Jan 05 '18 at 15:36

bgse · Accepted Answer · 2018-01-05T16:29:10.747

The issue is with your adaption of:

for row in cursor.fetchall():
    print row

This will print all the rows returned by fetchall(), one by one.

You tried to adapt this into a function returning all rows:

def get_statistics():
    cursor = finDB.execute_sql('CALL Allstatistics;')
    for row in cursor.fetchall():
        return row

Now this will return the first row only, as the return statement terminates your loop on first iteration.

What you really want is something like this:

def get_statistics():
    cursor = finDB.execute_sql('CALL Allstatistics;')
    return cursor.fetchall()

This will correctly return all rows in the cursor, or None if there are no result rows.

With checking if there is a non-empty result, and instead of None returning an empty list, you could do it this way:

def get_statistics():
    cursor = finDB.execute_sql('CALL Allstatistics;')
    rows = cursor.fetchall()
    if rows:
        return rows
    return []

Regarding cursor.fetchone(), this will return the next available row of the cursor, or None if no more rows are available. For example, you can iterate over all available rows in your cursor like this:

rows = []
row = cursor.fetchone() # fetch first row, or None if empty result
while row is not None:
    rows.append(row)
    row = cursor.fetchone() # fetch the next row, if None loop terminates
return rows # return all collected results

For your use-case, it might be interesting to construct a more convenient data structure for your result, e.g. a list of dicts:

rows = []
row = cursor.fetchone()
while row is not None:
    rows.append({'foo': row[0], 'bar': row[1], 'baz': row[2]})
    row = cursor.fetchone()
return rows

Note that this can be similarly achieved like this:

rows = []
for row in cursor.fetchall():
    rows.append({'foo': row[0], 'bar': row[1], 'baz': row[2]})
return rows

You can then write in your template, looping for row in rows:

foo is {{row['foo']}} and bar is {{row['bar']}}

Or you could construct a list of namedtuple, allowing you to write in template:

foo is {{row.foo}} and bar is {{foo.bar}}

The first option works ok with {{ row[0] }}. the second option crashes the app and highlights syntax error for rows=cursor.fetchall():. None of the options work for {{row.ColumnHeader}} — michal, Jan 05 '18 at 15:51
@michal The 2nd option had an extra `:` left over from a previous edit, sorry about that. None of these will work for `{{row.ColumnHeader}}` as each `row` is of type `list`, and will not have the fields as attributes named by column headers. — bgse, Jan 05 '18 at 15:55
My bad, typo error, added ':' after fetchall(), both option works correctly, Thanks! — michal, Jan 05 '18 at 15:59
Can you take it one step further and explain fetchone() ? def get_stats_summary(): cursor = finDB.execute_sql('call AllStatisticsSum()') return cursor.fetchone() then try to loop through no no thing is showing — michal, Jan 05 '18 at 16:01
@michal See edit, you can use `fetchone()` to fetch a single row from the cursor. — bgse, Jan 05 '18 at 16:20
OK, I got it. in app.py: summary = model.get_summary(), then on the web page {{ summary[0] }} {{ summary[1] }} — michal, Jan 05 '18 at 16:20
thanks for that, however if sql query returns only 1 row I feel its bad practice to loop through rows... as there is only and always 1 row — michal, Jan 05 '18 at 16:22
@michal Indeed, when you expect one and only one result, you'd probably even want to safeguard against `empty result` and `more than one result`. — bgse, Jan 05 '18 at 16:25

loop through peewee results in flask

1 Answers1