0

So I think I know where the problem is, it's trying to update the variable shares taken from the form, and add the shares from the form and the new amount of shares, which is not helpful. I need to grab the value of shares from the database of the user, so how do I do that? I just don't know how to grab the variable from the database. Help would be appreciated!

db.execute("UPDATE portfolio\ SET :shares = :shares + :newshares\ WHERE :user_id AND :stock IN Portfolio", user_id=session["user_id"], stock=stock["symbol"], shares = shares, newshares = newshares)

Improve this question

1 Answer 1

Reset to default
0

The code would need to do a select on the table to find out how many shares are owned and then update the value. In this method, you need to do a SELECT from the table to find out if the user owns the stock, (no rows returned if he doesn't) and if so, how many shares. Then, if they don't own the stock, insert a new row, or if they do, update the number of shares using an UPDATE query.

If this were using a full database product and not sqlite 3, you could use ON DUPLICATE KEY UPDATE and do it all in one query.

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

Improve this answer
3
  • Thanks for the help, but I was told ON DUPLICATE KEY UPDATE doesn't work anymore in this version of SQL?
    – Joel Banks
    May 20, 2019 at 15:14
  • Hmmm... it's not that it doesn't work anymore. I was just reminded that the IDE is using sqlite 3. ON UPDATE was never part of it. That's the downside of using a subset of SQL. So, you'd have to use the first method I discussed.
    – Cliff B
    May 20, 2019 at 21:51
  • So a pseudo-code of that would look something like: Select shares & user id = true, update, else: insert? EDIT: How do I run a normal command (ex return apology("hi")) inside of the db.execute?
    – Joel Banks
    May 27, 2019 at 0:31

You must log in to answer this question.

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