pset7 question about tables in buy

Question

Greetings I am having trouble understanding how tables in sql work in buy. So from reading other materials (pset7 buy - ON DUPLICATE KEY syntax question) am I suppose to always make a new row even though the bought stock is the same as another one and just total the columns that need to be totaled up at the end and use that value to be displayed on the html page instead of updating the tables? Something that is also not working when I try to insert it is the current date and time does anyone know what I am doing wrong?

def buy():
    """Buy shares of stock."""

    # if user reached route via POST (as by submitting a form via POST)
    if request.method == "POST":

        # ensure symbol was submitted
        quote = lookup(request.form.get("symbol"))
        if not request.form.get("symbol") or quote == None:
            return apology("must provide symbol/symbol does not exist")

        #ensure shares was submitted as positive integer
        number = request.form.get("shares")
        try:
            number = int(number)
            if number < 0:  #if not a positive int print message and ask for input again by apology
                return apology("must provide shares as a positive integer")
        except ValueError:
            return apology("must provide shares as a integer")

        #select how much cash the logged on use has and execute it on the portfolio table
        usercash = db.execute("SELECT * FROM users WHERE id = :id", id = session["user_id"])
        price =quote.get("price")
        symbol = quote.get("symbol")
        name = quote.get("name")
        stockt = price * number
        cash = usercash[0]["cash"]
        now = datetime.datetime.now()

         #check if user has enough cash for beginning
        if stockt > cash:
            return apology("Not enough minerals")

        #in portfolio insert information using values from user and make new rows of whatever was bought
        db.execute("INSERT INTO portfolio (user_id, Symbol, Name, Shares, Price, Total, Datetime) VALUES(:id, :symbol, :name, :shares, :price, :total, :t)"
                    ,id = session["user_id"], symbol = symbol, name = name, shares = number, price = price, total = stockt, t=now)

        #add up all the totals  to put into the printed html row that is actualy shown
        #db.execute("SELECT SUM(Shares) FROM portfolio WHERE symbol = :same", same = symbol)

        #render template with new cash total and updated values
        return render_template("portfolio.html", sym=symbol, )

    return render_template("buy.html")

Answer 1

Ok, I may be wrong about this and I can delete my answer so it stays unanswered if I am.

I think the spec is a little murky referenced by Dino on the question linked above. Dino interpreted it to mean that each buy transaction needs a separate date/time, but I'd argue that it could also mean generally what was the users last transaction date with that specific share. So OK, maybe that doesn't make sense and he is in the right of it, but then why, if you look further down the list, the specification requires you to implement history which does exactly what we are talking about here: it records what the user bought, how much, and more importantly when each transaction occurred. I think it would be reading into the spec a little too much to think that the writer expects the user to keep track of every buy/sell transaction and the date only to do the same thing again later unless the idea is that history is implemented on the very same table which I asked about here:

How to factor out similarities in history and portfolio tables?

If you don't need to keep a list of each transaction separately that the user buys/sells of the same stock, I think it may be easier to check if the user has already bought any shares of the same name similar to the way that the login function checks if a user is trying to login with a username that already exists in the users table. If the user doesn't have any shares of the stock they are trying to buy then you can insert the stock into the portfolio as usual, otherwise you can instead update the current number of shares that the user owns using the UPDATE keyword in sqlite3. This way you can update the shares and total that the user owns in the portfolio. Updating the price is a little wacky, but I suppose you could each time a user buys, although it doesn't really matter until they sell their shares anyway.

Again, this could all be wrong, and maybe someone else has more insight on this. I submitted this project a little bit ago myself, so I can keep you posted if I missed points because I didn't follow the spec closely enough.

EDIT

I got 100/100 on pset7 in the gradbook, but I don't know how they grade these types of psets. Whether it is a human or a computer, it makes me think that this isn't a criteria they are checking even if they do want this greater accuracy in the portfolio table, so it may not be worth spending too much time worrying about. That said, it certainly makes for a fun challenge, however I question its worth seeing as the spec as the student to record the transactions in the same scope in the history function at least in my opinion.