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?

Answer 1

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}}