pset7 finance/index problem updating values in table

Question

First time asking question, sorry if formatted badly. I am trying to set up to update my price and total column in my sql table. I seem to have it mostly correct, as values are being updated properly, however all rows in table are being updated instead of just one at a time. As screenshot shows, it seems i am going through the loop and all 4 rows are being updated 4 times instead of each row just once. In the table itself, price shows the same for all rows, while total remains blank. what am i doing wrong with my loop?

rows = db.execute("SELECT symbol, shares, price, total FROM test WHERE sessionid=:id", id=session["user_id"])
for row in rows:
    quote = lookup(row["symbol"])
    price = float(quote['price'])
    total = price * int(row["shares"])
    db.execute("UPDATE test SET price=:price, total=:total WHERE symbol=:symbol", price=price, total=total, symbol=quote['symbol'])

Here is what the debugger code shows(looks ok here):

DEBUG:cs50:UPDATE test SET price=1207.33, total=6036.65 WHERE symbol='GOOG'
DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): api.iextrading.com
DEBUG:urllib3.connectionpool:https://api.iextrading.com:443 "GET /1.0/stock/aapl/quote HTTP/1.1" 200 None
DEBUG:cs50:UPDATE test SET price=215.05, total=430.1 WHERE symbol='AAPL'
DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): api.iextrading.com
DEBUG:urllib3.connectionpool:https://api.iextrading.com:443 "GET /1.0/stock/aapl/quote HTTP/1.1" 200 None
DEBUG:cs50:UPDATE test SET price=215.05, total=645.1500000000001 WHERE symbol='AAPL'
DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): api.iextrading.com
DEBUG:urllib3.connectionpool:https://api.iextrading.com:443 "GET /1.0/stock/aapl/quote HTTP/1.1" 200 None
DEBUG:cs50:UPDATE test SET price=215.05, total=215.05 WHERE symbol='AAPL'

Photo shows table in phpliteadmin

Answer 1

"It did exactly what I said, but not what I wanted!" ;-)

db.execute("UPDATE test SET price=:price, total=:total WHERE symbol=:symbol", price=price, total=total, symbol=quote['symbol'])

This SQL code says to update the price and total for EVERY row in the table that has the given symbol. So, any row with APPL gets updated. (Not sure what's going on with GOOG, maybe you did something previously to get the same data?)

In order to get this right, the code needs to select the exact row(s) to be modified. Since you're allowing multiple rows with the same symbol for a given user, you need to update WHERE symbol =... AND userid =... AND [something else]. I'm thinking that if you get the ID field in the first select, then that's all you need to identify the row to be updated, since it is a unique field.

As a side comment, if this is a transaction history table, then multiple rows for the same stock and user are appropriate, but then, why would anything in them need to be changed?

On the other hand, if this is a portfolio table, then why are there multiple rows for each stock+user combination? In this case, the combination of those two fields should be used as a unique index and the row created if it doesn't exist, updated as stocks are bought and sold, and deleted if all stocks are sold. Hint: ON DUPLICATE UPDATE is your friend.

[EDIT] To drill down a little farther, let's look at the 3 AAPL rows. The code is looping through. I'm not sure what's happening exactly. It would appear that it is setting each row's price to 215.05 three times. I'm not sure what's happening with the total because the phpliteadmin screenshot shows NULL. That means there's another problem. Why didn't the total update?

As for the GOOG updates, I have no idea what's happening there. My best guess is that the data provided above is somehow inconsistent with the actions taken. I'd delete all the data from the table and start fresh, adding rows and then updating one row only once, and checking the table after each action to record the contents and watch what happens.

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

Answer 2

Welcome to the forum!

None of the update statements in the debugger view updated any rows. None of them found a matching symbol in the test table. Notice, test.symbol in the database is goog, but the WHERE clause specifies GOOG.

From the Hints section in the spec:

If str is a DELETE or an UPDATE, then execute returns the number of rows deleted or updated by str.

You could store the result of the update query in a variable, as with
result = db.execute("UPDATE test SET price=:price, total=:total WHERE symbol=:symbol"... and then print that result, which you would see in the flask terminal (for debugging purposes).

sqlite has both upper and lower functions that you can use to manage the case issue. (python does too, but I don't have that doc on hand :)

I don't see where "all 4 rows are being updated 4 times". Perhaps that is not the complete debugger output? This shows exactly 4 updates executed, as one would expect from the for row in rows loop.