调用函数时"Something went wrong: parameters are of unsupported type"

Question

我正在为一个学校项目做一个网站，我正在做一个复习生成器。我认为错误是由于不正确的数据类型传递的函数，但我不知道为什么。到目前为止，这部分代码是这样的:

truckReviewCreationSQL = """
INSERT INTO truckReviews (reviewID, truckID, user, comment, sosRating, qatRating, vfmRating, reply)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
"""
def ReviewGenerator():
db = sqlite3.connect('D:\Documents\wlayton\My Documents\Coding\Year 12\FA 2\FA2 - Food Truck Website\truckDB.db')
cursor = db.cursor()
global trucksTable
print("here")
for truck in trucksTable:
counter = 0
numberReviews = random.randint(50,150)
print("here")
while counter < numberReviews:
sos = random.randint(1,3) #Speed of service
qat = random.randint(1,3) #quality and taste
vfm = random.randint(1,3) #value for money
comment = "" 
logedIn = "Review-Robot"
RatingID = db.cursor().execute("SELECT reviewID + 1 FROM truckReviews ORDER BY reviewID DESC LIMIT 1").fetchone()
if RatingID == None:
RatingID = 0
else:
RatingID = RatingID[0]
cursor.execute(truckReviewCreationSQL, (RatingID, int(truck[0]), logedIn, comment, sos, qat, vfm, "",)) #adds review to db
db.commit()
counter = counter + 1
print("here - tru") #<------------------------------------ The error pops up after here
TruckRatingUpdate(truck[0]) #<-------- truck[0] is the id of a truck eg. 116
print("here - finish tru")

这是它调用的函数:

ratingTableUpdate = """
UPDATE truckRating
SET numReviews == ?
SET reviewAvg == ?
WHERE truckID == ? 
"""
def TruckRatingUpdate(truckID):
db = sqlite3.connect('D:\Documents\wlayton\My Documents\Coding\Year 12\FA 2\FA2 - Food Truck Website\truckDB.db')
cursor = db.cursor()
numberReviews = db.cursor().execute("SELECT count(truckID) FROM truckReviews WHERE truckID = ?", truckID).fetchone()
print(str(numberReviews) + " and " + str(numberReviews[0]))
if numberReviews[0] > 0:
sosRating = db.cursor().execute("SELECT sosRating FROM truckReviews WHERE truckID = ?", truckID).fetchall()
qatRating = db.cursor().execute("SELECT qatRating FROM truckReviews WHERE truckID = ?", truckID).fetchall()
vfmRating = db.cursor().execute("SELECT vfmRating FROM truckReviews WHERE truckID = ?", truckID).fetchall()
Total = 0
for rating in sosRating:
Total = Total + rating[0]
for rating in qatRating:
Total = Total + rating[0]
for rating in vfmRating:
Total = Total + rating[0]
average=Total/(numberReviews[0]*3)
print("here")
cursor.execute(ratingTableUpdate, (numberReviews[0], average, truckID,)) #updates review in db
db.commit()

这是它调用的函数，在调用它时，它给出了错误"出错了:参数的类型不支持"所以我假设我试图传递TruckRatingUpdate(truckID)函数是不允许的或不正确的。它应该通过其评论计算卡车的平均评级，然后用新值更新SQL表。谢谢,将

Answer 1

在ratingTableUpdate中有错误的SQL语法。应该只有一个SET子句，各列之间用,分隔。

但是不需要所有这些单独的查询，因为您可以使用UPDATE-FROM直接从TruckReviews更新TruckRating

def TruckRatingUpdate(truckID):
db = sqlite3.connect('D:\Documents\wlayton\My Documents\Coding\Year 12\FA 2\FA2 - Food Truck Website\truckDB.db')
cursor = db.cursor()
cursor.execute('''UPDATE TruckRatings
SET numReviews = x.num, x.ReviewAvg as av
FROM (
SELECT COUNT(*) as num, AVG(sosRationg + qatRating + vfmRating) AS av
FROM TruckReviews
WHERE TruckId = ?) AS x
WHERE TruckID = ?''', (TruckID, TruckID))
db.commit()