Flask+SQLite开发API遭遇sqlite3.ProgrammingError错误求助

我用Flask开发连接SQLite数据库的API，编写了一个将库存数量减1的函数，但运行时出现错误：sqlite3.ProgrammingError: Error binding parameter 1: type 'sqlite3.Row' is not supported。尝试了各种谷歌搜索技巧都没找到解决方案，其他功能均正常，实在困惑。

代码实现 #

import sqlite3
from flask import Flask, g

app = Flask(__name__)
database = './stock.db'

def get_db():
    db = getattr(g, '_database', None)
    if db is None:
        db = g._database = sqlite3.connect(database)
        db.row_factory = sqlite3.Row
    return db

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()

def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    rv = cur.fetchall()
    cur.close()
    return (rv[0] if rv else None) if one else rv

def set_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    get_db().commit() # just bad. just just bad.
    # ignore comment before this one, its incorrect
    # actually it's probably correct
    cur.close()

@app.route('/')
def index():
    manual = open("stockmanual", "r")
    return manual.read()

@app.route('/remove/<int:barcode>', methods=['GET'])
def removequantity(barcode):
    barcode = query_db('SELECT * FROM stock WHERE Barcode = ?',
                              [barcode], one=True)
    
    if barcode is None:
        return 'null'

    set_db('''
    UPDATE stock
    SET Quantity = Quantity - 1
    WHERE Barcode = ?
    ''', [barcode])

完整错误信息 #

Traceback (most recent call last):
  File "/usr/lib/python3.11/site-packages/flask/app.py", line 2190, in wsgi_app
    response = self.full_dispatch_request()
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/site-packages/flask/app.py", line 1486, in full_dispatch_request
    rv = self.handle_user_exception(e)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/site-packages/flask/app.py", line 1484, in full_dispatch_request
    rv = self.dispatch_request()
         ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/site-packages/flask/app.py", line 1469, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/lost/projects/ppddvvapi/stock.py", line 58, in removequantity
    set_db('''
  File "/home/lost/projects/ppddvvapi/stock.py", line 40, in set_db
    cur = get_db().execute(query, args)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.ProgrammingError: Error binding parameter 1: type 'sqlite3.Row' is not supported

问题原因 #

你在removequantity函数中，将传入的整数类型参数barcode覆盖成了query_db返回的sqlite3.Row对象。后续调用set_db更新数据时，把这个sqlite3.Row对象作为参数传给SQL语句，而SQLite无法识别该类型，因此抛出参数绑定错误。

修复方案 #

修改查询结果的变量名，避免覆盖原有的barcode参数：

@app.route('/remove/<int:barcode>', methods=['GET'])
def removequantity(barcode):
    # 重命名查询结果变量，避免覆盖传入的barcode参数
    stock_item = query_db('SELECT * FROM stock WHERE Barcode = ?',
                              [barcode], one=True)
    
    if stock_item is None:
        return 'null'

    set_db('''
    UPDATE stock
    SET Quantity = Quantity - 1
    WHERE Barcode = ?
    ''', [barcode])  # 使用原来的整数类型barcode参数

另外，优化set_db函数，复用同一个数据库连接对象，减少不必要的连接操作：

def set_db(query, args=(), one=False):
    db = get_db()
    cur = db.execute(query, args)
    db.commit()
    cur.close()

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