CODEWITHSUNDEEP
pythonsqliteflask
Brandon Zemel
Brandon Zemel

Reputation: 97

SQLite/Flask - Row Value Misused

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

Upvotes: 1

Views: 379

Answers (2)

Eduardo Matsuoka
Eduardo Matsuoka

Reputation: 716

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)

Upvotes: 1

Mike Organek
Mike Organek

Reputation: 12484

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>

Upvotes: 0

Related Questions