How to retrieve inserted id after inserting row in SQLite using Python?

When working with SQLite databases in Python, we typically use the sqlite3 library to perform database operations. After inserting a row into the database, you may want to retrieve the ID of the newly inserted row (assuming the table has an auto-incrementing primary key). This can be achieved by using the lastrowid attribute. I will illustrate this process with a specific example.

Assume we have a database example.db containing a table users with the following structure:

sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
);

In this table, id is an auto-incrementing primary key.

First, we need to connect to the SQLite database and create a cursor object for executing SQL statements:

python
import sqlite3

# Connect to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

Next, we can insert a row into the users table and use lastrowid to retrieve the ID of the newly inserted row:

python
# Insert a row into users table
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))

# Commit the transaction
conn.commit()

# Retrieve the inserted row's ID
inserted_id = cursor.lastrowid
print("Inserted row ID:", inserted_id)

In this example, we add a new user named Alice with an age of 30 to the users table. After executing the insert operation, we use the cursor.lastrowid attribute to retrieve the ID of the newly inserted row. This is a convenient method for tracking new data IDs, particularly useful when inserting related data into other tables.

Finally, it is important to close the cursor and connection:

python
# Close cursor and connection
cursor.close()
conn.close()

By following this process, you can effectively insert data and retrieve the ID of the newly inserted row when using Python with SQLite databases. This approach is crucial for handling relational data, helping maintain data integrity and consistency.