1

I'm on pset7 buy, and I just need some explanation on how the ON DUPLICATE KEY syntax is supposed to look (at least I assume that's the issue). Btw, id and symbol are both unique keys in my portfolio data sheet. As I understand it, this means that they can only be used once in combination on that data sheet.

Once I've gotten the user's buy order and confirmed s/he can afford the purchase, here's my code to add it to the portfolio data sheet:

    db.execute("""
        INSERT INTO portfolio
            (id, symbol, shares)
        VALUES
            (:id, :symbol, :shares)
        ON DUPLICATE KEY UPDATE
            (shares = shares + :shares)""",
        id=session["user_id"], symbol=symbol, shares=shares)

Error I'm getting says: RuntimeError: (sqlite3.OperationalError) near "ON": syntax error [SQL: "\n INSERT INTO portfolio\n (id, symbol, shares)\n VALUES\n (3, 'GOOGL', 1)\n ON DUPLICATE KEY UPDATE\n (shares = shares + 1)"]

Thanks for any help.

2

'ON DUPLICATE KEY UPDATE' is MySQL syntax. But is this really what you need to do? Remember from the spec:

Add one or more new tables to finance.db via which to keep track of the purchase. Store enough information so that you know who bought what at what price and when.

In order to keep the required level of detail, each buy transaction must be one row in the table. A primary key of symbol and user_id won't work if user can purchase more shares of an owned stock. Since you need to know "when" a transaction occurred, you probably need a DATETIME field in the table. That could be part of the primary key. Or better yet, create the table with an autoincrement key, like the user table, and add (non-unique) index on symbol,user_id. And don't forget to store price too.

For your reference, here is the sqlite INSERT documentation.

Hint: the syntax for current date and time is datetime('now')

4
  • Thank you for your response! OK. Maybe I should have mentioned I created a second table as well called 'history' with four fields, none are unique: id, symbol, shares, and datetime (I now realize I need to add price). Does that change anything? I wanted to be able to just query for the current portfolio separately from a sheet with all transaction history but maybe I shouldn't. I know eventually I'll have to display the user's current portfolio, current value of those stocks, and cash on hand. I'm having trouble figuring out the best way to do that. Maybe I need to think this through more.
    – Scott
    Mar 18 '17 at 16:56
  • Maybe the answer was TMI, bottom line: There is no "ON DUPLICATE KEY" syntax in sqlite. Mar 18 '17 at 17:12
  • HA! ::face palm:: OK, thank you. Guess my code's gonna have to be a little messier than I wanted.
    – Scott
    Mar 18 '17 at 17:19
  • hi, i'm currently working on pset7 sell. i have a question, if user bought 5 shares of the same stock in 5 different time i'll have 5 rows of the same symbol and user id each with 1 share in it, if user want to sell 1 share wouldn't it affect the other rows with the same user id and symbol? since sql iterate through the table and check for the user id and symbol to delete one share from it? (sorry if i'm confusing you i'm struggling with this problem right now your help will be much appreciated)
    – daniel ho
    Jun 8 '17 at 11:15

You must log in to answer this question.

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