lastrowid returning None in sqlite3 in python 3

Question

I'm having a bit of trouble trying to fix a problem I'm having in retrieving the last insert id from a query in SQLite3 using Python. Here's a sample of my code:

import sqlite3

# Setup our SQLite Database
conn = sqlite3.connect('value_serve.db')
conn.execute("PRAGMA foreign_keys = 1") # Enable Foreign Keys
cursor = conn.cursor()

# Create table for Categories
conn.executescript('DROP TABLE IF EXISTS Category;')
conn.execute('''CREATE TABLE    Category (
                id              INTEGER PRIMARY KEY AUTOINCREMENT,
                category        CHAR(132),
                description     TEXT,
                parent_id       INT,
                FOREIGN KEY     (parent_id) REFERENCES Category (id)
                );''')

conn.execute("INSERT INTO Category (category, parent_id) VALUES ('Food', NULL)")
food_category = cursor.lastrowid
conn.execute("INSERT INTO Category (category, parent_id) VALUES ('Beverage', NULL)")
beverage_category = cursor.lastrowid
...
conn.commit()  # Commit to Database

No matter what I do, when I try to get the value of 'food_category' I get a return value of 'None'.

Any help would be appreciated, thanks in advance.

Answer 1

The lastrowid value is set per cursor, and only visible to that cursor.

You need to execute your query on the cursor that executed the query to get the last row id. You are asking an arbitrary cursor, one that never actually is used to execute the query for a last row id, but that cursor can't know that value.

If you actually execute the query on the cursor object, it works:

cursor.execute("INSERT INTO Category (category, parent_id) VALUES ('Food', NULL)")
food_category = cursor.lastrowid

The connection.execute() function creates a new (local) cursor for that query and the last row id is only visible on that local cursor. That cursor is returned when you use connection.execute(), so you could get the same value from that return value:

cursor_used = conn.execute("INSERT INTO Category (category, parent_id) VALUES ('Food', NULL)")
food_category = cursor_used.lastrowid