pset7 buy - ON DUPLICATE KEY syntax question

Question

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.

Answer 1

'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')