CS50 pset9: Why does 'SELECT last_insert_rowid()' return 0 instead of the expected value when using CS50 SQL library in Flask?

Question

        symbol_id = db.execute("SELECT symbol_id FROM symbols WHERE symbol = ?", quote["symbol"])
        if not symbol_id:
            db.execute("INSERT INTO symbols (symbol) VALUES (?)", quote["symbol"])
            symbol_id = db.execute("SELECT last_insert_rowid()")[0]["last_insert_rowid()"]
            print("the symbol id would be " + str(symbol_id))
        else:
            symbol_id = symbol_id[0]["symbol_id"]

This is from pset9, specifically the part in the buy function where I want a newly inserted symbol_id to be copied from the symbols table to the purchases table. The following line symbol_id = db.execute("SELECT last_insert_rowid()")[0]["last_insert_rowid()"] keeps returning a value of 0, whereas, if I were to use sqlite3 in the terminal and input the same SQL commands, I would get more correct values, ie, symbol_id would be values of 1 or larger. What could I be doing wrong?

To clarify, this is the schema of my SQL tables (omitted copy pasting the indices for the sake of simplicity):

CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, username TEXT NOT NULL, hash TEXT NOT NULL, cash NUMERIC NOT NULL DEFAULT 10000.00);

CREATE TABLE symbols (symbol_id INTEGER PRIMARY KEY, symbol TEXT NOT NULL);

CREATE TABLE purchases (purchase_id INTEGER PRIMARY KEY, user_id INTEGER, symbol_id INTEGER, shares INTEGER, price DECIMAL(10, 2), date DATETIME, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (symbol_id) REFERENCES symbols(symbol_id));

---

if it's of further help, here is my full buy function:

@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
    """Buy shares of stock"""
    if request.method == "POST":
        quote = lookup(request.form.get("symbol"))
        if not quote:
            return apology("Invalid symbol", 403)
        shares = int(request.form.get("shares"))
        if shares < 0:
            return apology("Shares can't be negative", 403)
        balance = db.execute("SELECT cash FROM users WHERE id = ?", session["user_id"])[0]["cash"]
        price = float(quote["price"]) * shares
        if  price > balance:
           return apology("Not enough cash balance", 403)
        balance = balance - price
        db.execute("UPDATE users SET cash = ? WHERE id = ?", balance, session["user_id"])
        symbol_id = db.execute("SELECT symbol_id FROM symbols WHERE symbol = ?", quote["symbol"])
        if not symbol_id:
            db.execute("INSERT INTO symbols (symbol) VALUES (?)", quote["symbol"])
            symbol_id = db.execute("SELECT last_insert_rowid()")[0]["last_insert_rowid()"]
            print("the symbol id would be " + str(symbol_id))
        else:
            symbol_id = symbol_id[0]["symbol_id"]
        db.execute("INSERT INTO purchases (user_id, symbol_id, price, shares, date) VALUES (?,?,?,?,?)", session["user_id"], \
                   symbol_id, price, shares, datetime.now())
        return redirect("/")
    else:
        return render_template("buy.html")

Answer 1

You're not doing anything wrong; it is a function of CS50's sql wrapper. There is a solution. From the spec hints:

If str is an INSERT, and the table into which data was inserted contains an autoincrementing PRIMARY KEY, then execute returns the value of the newly inserted row’s primary key.

NB it does not have to be an explicit AUTOINCREMENT; implicit as with INTEGER PRIMARY KEY is sufficient.