Flask SQLite查询问题：showtable函数失效、query1聚合函数返回疑问

问题原因 #

• 事务未提交导致数据未持久化：你封装的dbconn函数仅返回游标，丢失了数据库连接对象。SQLite默认开启事务，create路由中执行的所有插入操作未调用commit()提交，修改仅在当前连接生效，连接关闭后自动回滚。showtable使用新连接访问数据库时自然查询不到数据。
• 聚合查询结果未正确读取：cur.execute()返回的是游标对象本身，不是查询结果，需要调用fetch类方法才能拿到实际返回的数据。

修复方案 #

1. 修改数据库连接封装 #

将连接对象和游标一同返回，方便后续提交事务、关闭连接：

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

2. 修改create路由，新增事务提交逻辑 #

增加表存在判断避免重复创建报错，插入完成后提交事务：

@app.route('/createdb',methods = ['POST', 'GET'])
def create():
    con, cur = dbconn()
    # 增加表存在判断，避免重复创建报错
    cur.execute("CREATE TABLE IF NOT EXISTS 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")) 
    # 提交事务，将修改持久化到磁盘
    con.commit()
    
    cur.execute("select * from orders")
    rows = cur.fetchall()
    # 操作完成关闭连接
    con.close()
    return render_template("onlineshoprecords.html", rows = rows)

3. 修改showtable路由，补充连接关闭逻辑 #

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

4. 修改query1路由，正确读取聚合查询结果 #

sum查询为单行单列结果，用fetchone()取第一个元素即可：

@app.route("/query1",methods = ['GET','POST'])  
def query1():  
    con, cur = dbconn()
    cur.execute("select sum(price) from orders")  
    total = cur.fetchone()[0]
    con.close()
    return f'结果是：{total}'

内容的提问来源于stack exchange，提问作者PRJ