Flask SQLite querying database-CodePudding

I am trying to perform some operations on my database. The table 'orders' get fetched in create function. But why is it not working in the showtable() function? It seems a logical error. I want to use aggregate functions also. How can I return the result of query1()?

from flask import Flask, render_template, request, url_for, redirect
import sqlite3 as sql
app = Flask(__name__)

def dbconn():
    con = sql.connect("onlineshop.db")
    con.row_factory = sql.Row
    cur = con.cursor()
    return cur

@app.route('/createdb',methods = ['POST', 'GET'])
def create():
    cur=dbconn()
    cur.execute("CREATE TABLE orders(item TEXT, price REAL, cust_name TEXT)")

    cur.execute("INSERT into orders (item, price, cust_name) values (?,?,?)",("Oximeter","50","Alice"))
    cur.execute("INSERT into orders (item, price, cust_name) values (?,?,?)",("Sanitizer","20","Paul"))
    cur.execute("INSERT into orders (item, price, cust_name) values (?,?,?)",("Mask","10","Anita"))
    cur.execute("INSERT into orders (item, price, cust_name) values (?,?,?)",("Sanitizer","20","Tara"))
    cur.execute("INSERT into orders (item, price, cust_name) values (?,?,?)",("Thermometer","30","Bob")) 
    cur.execute("INSERT into orders (item, price, cust_name) values (?,?,?)",("Mask","10","Alice")) 
    #return " Table is created."
    cur.execute("select * from orders")
    rows = cur.fetchall()
    return render_template("onlineshoprecords.html", rows = rows)

@app.route('/showtable',methods = ['GET','POST'])  
def showtable():  
    cur=dbconn()  
    cur.execute("SELECT * FROM orders")
    rows = cur.fetchall()
    return render_template("onlineshoprecords.html", rows = rows)

@app.route("/query1",methods = ['GET','POST'])  
def query1():  
    cur=dbconn()
    res1=cur.execute("select sum(price) from orders")  
    return f' result is :{res1}'

if __name__ == '__main__':
   app.run(debug = True)

HTML

<!DOCTYPE html>
<head></head>
<body>
    <table>
        <tr>
        <th>Item</th>
        <th>Price</th>
        <th>Customer Name</th>
    </tr>

    {% for row in rows %}    
        <tr align="center">  
            <td>{{row["item"]}} </td>
            <td>{{row["price"]}}</td> 
            <td>{{row["cust_name"]}}</td> 
        </tr>     
    {% endfor %}  
</table>
</body>
</html>

CodePudding user response：

After

cur=dbconn()  
cur.execute("SELECT * FROM orders")
rows = cur.fetchall()

rows is list of tuples, not dicts, so in your template you should access it by providing index not key, i.e. replace

    <td>{{row["item"]}} </td>
    <td>{{row["price"]}}</td> 
    <td>{{row["cust_name"]}}</td>

using

    <td>{{row[0]}}</td>
    <td>{{row[1]}}</td> 
    <td>{{row[2]}}</td>