SQLite/Flask - Row Value Misused

Question

I'm trying to query a table that's supposed to hold the stock holdings of a user. If a result for that stock owned by that user is found, update it with the new amount of shares after the buy order. If the user doesn't own the stock already, add it to the table.

I'm hitting an error at stockcheck = db.execute("SELECT * FROM holdings WHERE (userID = :userid, symbol = :symbol)", userid=session["user_id"], symbol=symbol) and I'm not sure what "row value misused" means. In the error, it shows the right info (my userID and the stock symbol) that it's searching for, so not sure whats going on. Help is appreciated as always!

Code:

@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)

Error:

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

Did you try to remove the parentheses from (userID = :userid, symbol = :symbol)? Also i'm not quite sure if you need an AND/OR Operator between the two conditions instead of a comma. — Jonas
– Jonas, Commented Jun 28, 2020 at 15:29
It was the AND operator instead of the comma, that fixed it. Thanks! — Brandon Zemel
– Brandon Zemel, Commented Jun 28, 2020 at 15:54

Eduardo Matsuoka · Accepted Answer · 2020-06-28 15:47:53Z

1

When using a WHERE clause to specify more than one condition, you must use AND or OR instead of commas to separate your conditions:

stockcheck = db.execute("SELECT * FROM holdings WHERE (userID = :userid AND symbol = :symbol)", userid=session["user_id"], symbol=symbol)

answered Jun 28, 2020 at 15:47

Eduardo Matsuoka

7369 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brandon Zemel Over a year ago

That was all that was needed. Onto the next error lol

Eduardo Matsuoka Over a year ago

Glad that could help! I did this project last year and really learned a lot.

Mike Organek · Accepted Answer · 2020-06-28 15:39:27Z

0

Your query is not written correctly. What you want is where userid = 4 and symbol='F'

sqlite> create table users (cash int, userid int, symbol text);
sqlite> select * from users;
sqlite> insert into users values (10, 1, 'SWHC');
sqlite> select * from users;
10|1|SWHC
sqlite> select * from users where userid = 1 and symbol = 'SWHC';
10|1|SWHC
sqlite> select * from users where (userid = 1, symbol = 'SWHC');
SQL error: near ",": syntax error
sqlite>

answered Jun 28, 2020 at 15:39

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Collectives™ on Stack Overflow

SQLite/Flask - Row Value Misused

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related