CODEWITHSUNDEEP
pythonsqlite
Bluemont
Bluemont

Reputation: 45

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

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: enter image description here

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.

Upvotes: 1

Views: 3413

Answers (1)

h4z3
h4z3

Reputation: 5478

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

Upvotes: 1

Related Questions