0

This is my code so far. It works. Now I am at the stage where you need to check and see if user already has that same stock. If the user has it, I have to add it, if not, just insert it to the table.

# check if the user already has same stock
            if :
           db.execute("UPDATE portfolio SET number = number + :bought WHERE id = :id", id = session['user_id'], bought = number_of_shares)

I don't know how I would select like for example I want to write something like this

n = db.execute("SELECT number FROM portfolio WHERE id = :id WHERE stock = :stock", id = session['user_id'], stock = quote['symbol']) 

if n is not None:
    db.execute("UPDATE portfolio SET number = number + :bought WHERE id = :id WHERE stock = :stock", id = session['user_id'], stock = quote['symbol'], bought = number_of_shares)         

Does two WHERE work? I am not sure if this is a right approach. Help me out please.

Thank you.

@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
    """Buy shares of stock"""

    # if user clicks buy
    if request.method == "POST":

        symbol = request.form.get("symbol")
        number_of_shares = int(request.form.get("shares"))
        quote = lookup(symbol)

        # check symbol box is empty
        if not symbol:
            return apology("You must type symbol!")

        # check number box is empty
        if not number_of_shares:
            return apology("You mnust type number of shares!")

        # check stock exists
        if not quote:
            return apology("Stock doesn't exist!")

        # check number of shares is higher than 0
        if number_of_shares <= 0:
            return apology("Number of shares must be higher than 0!")

        # get the cash value of the user from the database
        result = db.execute("SELECT cash FROM users WHERE id = :id", id = session['user_id'])

        check_cash = float(result[0]['cash'])

        total_price = float(quote['price'] * number_of_shares)

        # check if cash value of the user is higher than price x number of shares
        if check_cash >= total_price:

            # check if the user already has same stock
            if :
               db.execute("UPDATE portfolio SET number = number + :bought WHERE id = :id", id = session['user_id'], bought = number_of_shares)

            # if it is the first time user buying the stock
            else:
                # insert number of new shares into database
                db.execute("INSERT INTO portfolio(id, stock, number, price) VALUES(:id, :stock, :number, :price)", id = session['user_id'], stock = quote['symbol'], number = number_of_shares, price = quote['price'])

            # update cash after purchase
            db.execute("UPDATE users SET cash = cash - :spent WHERE id = :id", id = session['user_id'], spent = total_price)

            # return to homepage
            return redirect('/')

        else:
            return apology("You need more cash to purchase stock!")

    # User reached route via GET (as by clicking a link or via redirect)
    else:
        return render_template("buy.html")
0

There should be only one WHERE, but you can have as many AND clauses as you like. Here's a template to describe how it will work. It applies to select and to update as well. You need to figure out what to change to fit your needs. The angle brackets just enclose my sample fields and comments. Do not use them in your actual code. Consider it part of the learning process. ;-)

SELECT  <field>,<optionally more fields separated by commas> FROM <mytable>  
        WHERE field1=x and field2 = y and field3 = z

You can also google "sql select" or "sql update", etc.

Here's a bonus. "INSERT.... ON DUPLICATE UPDATE..." You can google those terms too. ;-)

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .