1

I am trying to implement Buy in pset7. When I buy shares from different companies, the db updates ok. However, if I try to buy more shares for a company I already hold stock in, it does not update. I am assuming it has something to do with how I have set up the unique fields in the db. I created a composite index on the id and symbol fields and made it unique. In applications.py I check whether a user holds particular shares and then either insert or update a row. I set out what I think is the relevant code below. Any help would be greatly appreciated.

else:
        #check whether the user already holds these shares 
        rows = db.execute("SELECT * FROM portfolio WHERE id =:id AND symbol = :symbol", id = session['user_id'], symbol = quote)

        #if shares already held, update holding
        if len(rows) != 0:
            portfolio = db.execute("UPDATE portfolio SET shares = shares + new_shares WHERE id = :id", id = session['user_id'])
        #if new holding, input relevant info in db   
        else:
            portfolio = db.execute("INSERT INTO portfolio (id, symbol, shares, price) VALUES (:id, :symbol, :shares, :price)", id = session['user_id'], symbol = quote['symbol'], shares = new_shares, price = quote['price'])
1

See if this rings any bells....

INSERT INTO portfolio (id, symbol, shares) VALUES(?, ?, ?) 
                    ON DUPLICATE KEY UPDATE ...

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

6
  • I have updated as follows but still no joy: portfolio = db.execute("INSERT INTO portfolio (id, symbol, shares, price) VALUES (:id, :symbol, :shares, :price) ON DUPLICATE KEY UPDATE shares + :shares", id = session['user_id'], symbol = quote['symbol'], shares = new_shares, price = quote['price']) The error message seems to show that purchase info is being captured ok but the update is not happening RuntimeError: (sqlite3.OperationalError) near "ON": syntax error [SQL: "INSERT INTO portfolio (id, symbol, shares, price) VALUES (3, 'AAPL', 10, 138.99) ON DUPLICATE KEY UPDATE shares + 10"]
    – Breninio
    Mar 15 '17 at 11:21
  • ON DUPLICATE KEY UPDATE shares + :shares"... This won't work. shares + shares produces a value, but where are you putting it? You're on the right track, but this is purely a syntax error. Hint: What you have done would be the same as ` size + 3; ` as a c statement.
    – Cliff B
    Mar 15 '17 at 16:48
  • Thanks for all the help so far but I still cant get it to work. I went back last night and looked at the Week 9 lecture hoping I would see my error but I cant. I think I know what you are getting at Cliff B but I dont know how to get at the existing value of shares in portfolio so that I can update its value with the new shares bought. I have tried curly braces, single quotes, portfolio.shares but none work. In your example above, how do I get at the value in size in SQL?
    – Breninio
    Mar 16 '17 at 9:18
  • WHERE id =... AND symbol = ... ON DUPLICATE KEY.... Also, make sure the primary key is created on BOTH the ID and symbol fields.
    – Cliff B
    Mar 16 '17 at 18:07
  • I'm getting more confused. Its a shame this ON DUPLICATE KEY concept is not covered in the lecture or a short or something. I have now updated the line of code to portfolio = db.execute("INSERT INTO portfolio (id, symbol, shares, price) VALUES (:id, :symbol, :shares, :price) Where id = :id AND symbol = :symbol ON DUPLICATE KEY UPDATE 'shares' = 'shares' + :shares", id = session['user_id'], symbol = quote['symbol'], shares = new_shares, price = quote['price']) but still getting syntax error.
    – Breninio
    Mar 17 '17 at 7:17
0
portfolio = db.execute("UPDATE portfolio SET shares = shares + new_shares WHERE id = :id", id = session['user_id'])

Where is new_shares declared and how does that get into the SQL statement? You probably also need to ensure you are updating the row with not only the correct id, but also the correct symbol too.

6
  • Thanks Ste. new_shares is declared within Quote as follows: new_shares = int(request.form.get("shares")) It is part of the IF statement above the Else statement I have shown above. What do you mean by "how does that get into the SQL statement?".
    – Breninio
    Mar 14 '17 at 22:16
  • Well, as you know, in order to access a variable in an SQL.execute() statement, you have to use it as :var and then declare what var is at the end of the statement. Mar 14 '17 at 22:27
  • I changed the relevant line to this but still not working. Any ideas? Thanks a mill. #if shares already held, update holding if len(rows) == 1: portfolio = db.execute("UPDATE portfolio SET shares = :update_shares WHERE id = :id AND symbol = :symbol", id = session['user_id'], symbol = quote, update_shares = shares + new_shares)
    – Breninio
    Mar 14 '17 at 22:48
  • When you write update_shares = shares + new_shares, are you attempting to retrieve shares from the database or is it a variable in your python code? Try this: portfolio = db.execute("UPDATE portfolio SET shares = shares + :new_shares WHERE id = :id AND symbol = :symbol", new_shares = new_shares, id = session['user_id'], symbol = quote) Mar 14 '17 at 23:03
  • Thanks again Ste for your help with this. I tried the above changes but still the same result. It seems that the purchase goes through i.e. no error message and I get to index, but when I look at the db, there is no change in the number of shares.
    – Breninio
    Mar 14 '17 at 23:19

You must log in to answer this question.

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