python - SQLite/Flask - 行值被滥用
问题描述
我正在尝试查询一个应该保存用户股票的表。如果找到该用户拥有的股票的结果,请在购买订单后使用新的股票数量对其进行更新。如果用户尚未拥有该股票,请将其添加到表中。
我遇到了一个错误, stockcheck = db.execute("SELECT * FROM holdings WHERE (userID = :userid, symbol = :symbol)", userid=session["user_id"], symbol=symbol)
我不确定“滥用行值”是什么意思。在错误中,它显示了它正在搜索的正确信息(我的用户 ID 和股票代码),所以不确定发生了什么。一如既往地感谢您的帮助!
代码:
@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
"""Buy shares of stock"""
if request.method == "GET":
return render_template("/buy.html")
else:
# collect user input - symbol
symbol = request.form.get("symbol").upper()
# if input is blank or symbol doesn't exist, return apology
if not symbol:
return apology("You must enter a stock symbol.", 300)
# collect user input - # of shares
shares = int(request.form.get("shares"))
# if blank or not a positive integer, return apology
if not shares:
return apology("Enter a valid number of shares.", 300)
# pull current price info from API
quote = lookup(symbol)
shareprice = quote["price"]
totalprice = shareprice * shares
# check users table to see how much cash user has
cashcheck = db.execute("SELECT cash FROM users WHERE id = :userid", userid = session["user_id"])
cash = cashcheck[0]["cash"]
if cash >= totalprice:
# in transactions table, insert userID, symbol, shares, shareprice, and totalprice
# transID should be autogenerated and autoincremented. date is also autofilled by SQLite.
db.execute("INSERT INTO transactions (userID, symbol, shares, shareprice, totalprice) VALUES (:userid, :symbol, :shares, :shareprice, :totalprice)",
userid=session["user_id"], symbol=symbol, shares=shares, shareprice=shareprice, totalprice=totalprice)
cash = cash - totalprice
# update cash balance
db.execute("UPDATE users SET cash = :cash WHERE id = :userid", cash=cash, userid=session["user_id"])
# update holdings table
# check if stock exists, if so update with new shares
stockcheck = db.execute("SELECT * FROM holdings WHERE (userID = :userid, symbol = :symbol)", userid=session["user_id"], symbol=symbol)
if stockcheck[0] > 0:
newshares = stockcheck[0] + shares
db.execute("UPDATE shares FROM holdings SET shares = :newshares WHERE (userID = :userid, symbol = :symbol)", newshares=newshares, userID=session["user_id"], symbol=symbol)
else:
# else, create stock and add shares
db.execute("INSERT INTO holdings (userID, symbol, shares) VALUES (:userID, :symbol, :shares)", userID=session["user_id"], symbol=symbol, shares=shares)
#return index
return redirect("/")
else:
# else, return apology (not enough cash)
return apology("Not enough cash balance to make execute this order.", 300)
错误:
DEBUG:cs50:SELECT * FROM holdings WHERE (userID = 4, symbol = 'F')
ERROR:application:Exception on /buy [POST]
Traceback (most recent call last):
File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2446, in wsgi_app
response = self.full_dispatch_request()
File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1951, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1820, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
raise value
File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1949, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1935, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/home/ubuntu/finance/helpers.py", line 34, in decorated_function
return f(*args, **kwargs)
File "/home/ubuntu/finance/application.py", line 96, in buy
stockcheck = db.execute("SELECT * FROM holdings WHERE (userID = :userid, symbol = :symbol)", userid=session["user_id"], symbol=symbol)
File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 21, in decorator
return f(*args, **kwargs)
File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 372, in execute
raise e
RuntimeError: row value misused
解决方案
当使用 WHERE 子句指定多个条件时,必须使用 AND 或 OR 而不是逗号来分隔条件:
stockcheck = db.execute("SELECT * FROM holdings WHERE (userID = :userid AND symbol = :symbol)", userid=session["user_id"], symbol=symbol)
推荐阅读
- xamarin - Xamarin:绑定 ContentView 内容不起作用,错误的实现
- java - 接口中的 Pojos
- input - 边界元法。如何处理标签 for/id?
- javascript - “回调”只是一个命名约定吗?
- django - 如何获取数据库表中不存在的自定义列?
- playframework - 玩2.6 Ebeans关系错误
- nginx - NginX 只允许特定 URL 上的内部 IP
- ssl - 如何在 Meteor 中正确使用 SSL?
- vue.js - VueJS 在没有 Vue Router 和 jQuery 的情况下处理 URL 片段以执行操作
- symfony - symfony fos 用户参数监听器注入失败