How can I capture the rowid of newly inserted row in SQLite/Flask?

Question

I want to insert a new row into a table, and return the newly created auto-incremented id from that row so I can execute a second command, inserting that new id into a join table.

I've tried using solutions from other SO posts but they don't work for my case (e.g., they call for cursor.x but I'm not using "cursor").

I created a simple example for sharing my code:

SQLite schema for 3 tables:

CREATE TABLE trees (id INTEGER, name TEXT NOT NULL, PRIMARY KEY (id));
CREATE TABLE birds (id INTEGER, name TEXT NOT NULL, PRIMARY KEY (id));
CREATE TABLE BirdsToTrees (
birdID INTEGER NOT NULL,
treeID INTEGER NOT NULL,
FOREIGN KEY (birdID) REFERENCES birds(id) ON DELETE CASCADE,
FOREIGN KEY (treeID) REFERENCES trees(id) ON DELETE CASCADE
);

Test data in birds table:

id | name
1 | sparrow
2 | dodo
3 | cardinal
4 | bluejay
5 | woodpecker
6 | emu
7 | chicken

Flask app code:

@app.route("/", methods = ["GET", "POST"])
def index():
    if request.method == "GET":
        treeList = db.execute("SELECT * FROM trees")
        birdList = db.execute("SELECT * FROM birds")
        return render_template ("SQLtest.html", treeList = treeList, birdList = birdList)
    else:
        newBird = request.form.get("newBird")
        db.execute("INSERT INTO birds (name) VALUES (?)", newBird)
        newBirdID = db.execute("SELECT last_insert_rowid() FROM birds")
        print(f"You really saw a {newBird}? Its ID is now {newBirdID}")
        return redirect("/")

When I used the web form to submit "chicken" as a new bird, it was successfully inserted with the id of 7. But the printed output of my Flask console was:

You really saw a chicken? Its ID is now [{'last_insert_rowid()': 0}, {'last_insert_rowid()': 0}, {'last_insert_rowid()': 0}, {'last_insert_rowid()': 0}, {'last_insert_rowid()': 0}, {'last_insert_rowid()': 0}, {'last_insert_rowid()': 0}]

So it returned a list of 7 identical dictionaries, rather than the integer 7. Can anyone help?

Here are a few other attempts that failed, along with the error messages:

newBirdID = cursor.lastrowid
#NameError: name 'cursor' is not defined

newBirdID = db.lastrowid
#AttributeError: 'SQL' object has no attribute 'lastrowid'

newBirdID = db.execute("INSERT INTO birds (name) VALUES (?) RETURNING id", newBird) 
#RuntimeError: near "RETURNING": syntax error

BTW, that RETURNING syntax works fine at the command line in SQLite, e.g.

sqlite> INSERT INTO birds (name) VALUES "chicken" RETURNING id;
id
7

But it fails every time when I do it via db.execute with the "?" placeholder.

After execute insert, just check lastrowid - https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.lastrowid "This read-only attribute provides the rowid of the last modified row. It is only set if you issued an INSERT or a REPLACE statement using the execute() method." — h4z3, Sep 22 '21 at 15:40
Ah, I just read the whole details... https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.execute — h4z3, Sep 22 '21 at 15:43

score 1 · Accepted Answer · answered Sep 22 '21 at 15:48

Your problem is that you do execute directly on the connection and not the cursor.

Docs explain how that shortcut works:

execute(sql[, parameters]) This is a nonstandard shortcut that creates a cursor object by calling the cursor() method, calls the cursor’s execute() method with the parameters given, and returns the cursor.

https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.execute

See at the end. "returns the cursor". This means we can still get the use the Cursor.lastrowsid which you tried!

So just... save the returned cursor and get lastrowid from it. :)

cur = db.execute("INSERT INTO birds (name) VALUES (?)", newBird)
newBirdID = cur.lastrowid

This answer put me on the right path. When I tried it, I got:`newBirdID = cur.lastrowid AttributeError: 'int' object has no attribute 'lastrowid'` So that didn't work, but clearly cur was an integer, and indeed its value was the id of the newly inserted row. So it turns out that the db.execute statement itself returns the lastrowid -- at least in this case it does. So I used this: `newBirdID = db.execute("INSERT INTO birds (name) VALUES (?)", newBird)` and it works! — Bluemont, Sep 22 '21 at 18:38

How can I capture the rowid of newly inserted row in SQLite/Flask?

1 Answers1