CRUD Operations in Python0%

CRUD Operations in Python

Intermediate8 min read•Updated: 2026-10-01

CRUD Operations in Python with SQLite

CRUD stands for Create, Read, Update, and Delete—the four foundational data manipulation operations powering nearly every software application and REST API. In this guide, we explore how to perform each operation efficiently using Python's sqlite3 module.

1. Setting Up Our Sample Schema

Let's work with an e-commerce products table:

1import sqlite3
2 
3def get_connection():
  conn = sqlite3.connect("store.db")
  conn.row_factory = sqlite3.Row
  return conn
7 
8with get_connection() as conn:
  conn.execute("""
  CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      category TEXT NOT NULL,
      price REAL NOT NULL,
      stock INTEGER DEFAULT 0
  );
  """)

2. CREATE: Single and Bulk Inserts

Single Insert (`cursor.lastrowid`)

When inserting a single record, cursor.lastrowid retrieves the auto-generated primary key:

1with get_connection() as conn:
  cursor = conn.cursor()
  cursor.execute(
      "INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?);",
      ("Wireless Ergonomic Mouse", "Electronics", 2999.0, 45)
  )
  product_id = cursor.lastrowid
  print(f"Product created with ID: {product_id}")

High-Performance Bulk Inserts with `executemany()`

Avoid looping over single execute() statements for thousands of records. Calling cursor.executemany() executes the batch within a single optimized transaction:

1inventory_batch = [
  ("USB-C Fast Charger 65W", "Electronics", 1499.0, 100),
  ("Mechanical Gaming Keyboard", "Electronics", 4999.0, 25),
  ("Noise-Cancelling Headphones", "Audio", 8999.0, 15),
  ("Laptop Stand Aluminum", "Accessories", 1299.0, 60),
6]
7 
8with get_connection() as conn:
  cursor = conn.cursor()
  cursor.executemany(
      "INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?);",
      inventory_batch
  )
  print(f"Batch inserted: {cursor.rowcount} rows added.")

3. READ: Querying, Filtering, and Aggregation

Parameterized Filtering and Sorting

1def find_products(category: str, max_price: float):
  with get_connection() as conn:
      cursor = conn.cursor()
      cursor.execute(
          """
          SELECT id, name, price, stock 
          FROM products 
          WHERE category = ? AND price <= ? 
          ORDER BY price ASC;
          """,
          (category, max_price)
      )
      return cursor.fetchall()
14 
15matching_items = find_products("Electronics", 3000.0)
16for item in matching_items:
  print(f" - #{item['id']} {item['name']}: ₹{item['price']:,.2f} (In stock: {item['stock']})")

SQL Aggregations

1with get_connection() as conn:
  cursor = conn.cursor()
  cursor.execute("""
      SELECT 
          COUNT(*) AS total_items, 
          AVG(price) AS average_price,
          MAX(price) AS highest_price
      FROM products;
  """)
  stats = cursor.fetchone()
  print(f"Total Catalog Size : {stats['total_items']}")
  print(f"Average Price      : ₹{stats['average_price']:,.2f}")
  print(f"Most Expensive     : ₹{stats['highest_price']:,.2f}")

4. UPDATE: Modifying Existing Records

Golden Rule of Database Updates: Always include a specific WHERE clause! Omitting WHERE updates every single row in the entire table!

Check cursor.rowcount to verify how many rows were modified:

1def update_product_price(product_id: int, new_price: float) -> bool:
  with get_connection() as conn:
      cursor = conn.cursor()
      cursor.execute(
          "UPDATE products SET price = ? WHERE id = ?;",
          (new_price, product_id)
      )
      # Verify if an actual row was modified
      if cursor.rowcount == 0:
          print(f"Warning: No product found with ID {product_id}.")
          return False
      else:
          print(f"Product #{product_id} price updated to ₹{new_price:,.2f}.")
          return True
15 
16update_product_price(1, 2499.0)

5. DELETE: Removing Records (Hard vs. Soft Deletes)

Hard Delete (Permanent Removal)

1def delete_product(product_id: int):
  with get_connection() as conn:
      cursor = conn.cursor()
      cursor.execute("DELETE FROM products WHERE id = ?;", (product_id,))
      if cursor.rowcount > 0:
          print(f"Product #{product_id} permanently deleted.")
      else:
          print(f"Product #{product_id} not found.")
9 
10delete_product(4)

Soft Delete Pattern (Industry Best Practice)

Instead of permanently wiping records from disk, enterprise applications add an is_active or deleted_at column:

1-- Soft Delete: Flag record as inactive rather than deleting bytes
2UPDATE products SET is_active = 0 WHERE id = 10;
3 
4-- Query only active items:
5SELECT * FROM products WHERE is_active = 1;

⚠️ Common Intermediate Mistakes & Gotchas

1. SQL Injection via String Interpolation

Never format user input into SQL queries using f-strings or %s. Always use parameterized queries with ? placeholders:

1# ❌ DANGEROUS SQL INJECTION VULNERABILITY
2cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")
3 
4# ✅ SECURE PARAMETERIZED QUERY
5cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))

2. `UPDATE` or `DELETE` Without a `WHERE` Clause

Executing an UPDATE or DELETE statement without a WHERE clause modifies or deletes EVERY row in the entire table!

💻 Try It Yourself: Complete CRUD Lifecycle Script

Scenario

Write an end-to-end Python script that inserts a record into an SQLite table, reads it back, updates its price, and finally deletes the record using parameterized queries.

Complete Solution

1import sqlite3
2 
3with sqlite3.connect(":memory:") as conn:
  cur = conn.cursor()
  cur.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, price REAL)")
  
  # 1. CREATE
  cur.execute("INSERT INTO books (title, price) VALUES (?, ?)", ("Python Mastery", 599.0))
  book_id = cur.lastrowid
  print(f"Created Book ID: {book_id}")
  
  # 2. READ
  cur.execute("SELECT * FROM books WHERE id = ?", (book_id,))
  print("Read Record:", cur.fetchone())
  
  # 3. UPDATE
  cur.execute("UPDATE books SET price = ? WHERE id = ?", (499.0, book_id))
  print("Updated price to ₹499.00.")
  
  # 4. DELETE
  cur.execute("DELETE FROM books WHERE id = ?", (book_id,))
  print(f"Deleted Book ID: {book_id}. Remaining records: {cur.execute('SELECT COUNT(*) FROM books').fetchone()[0]}")

Expected Output

1Created Book ID: 1
2Read Record: (1, 'Python Mastery', 599.0)
3Updated price to ₹499.00.
4Deleted Book ID: 1. Remaining records: 0

Interactive Quiz• Knowledge Check

Practice Quiz

Question 1

Which method on a cursor should you use to insert a list of 500 records in a single batch?

Question 2

How can you retrieve the auto-generated primary key of the most recently inserted row?

Question 3

What cursor attribute indicates the number of rows affected by an UPDATE or DELETE statement?

Question 4

What critical mistake happens if an UPDATE statement is executed without a WHERE clause?

Question 5

What is the primary difference between a "Hard Delete" and a "Soft Delete"?

🚀 What's Next?

In the next lesson, we will continue your intermediate Python journey with Project: Student Records Database (11: Database Basics with SQLite).

👉 Continue to Next Lesson: Project: Student Records Database →

Next Lesson

Project: Student Records Database

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Connecting Python with SQLite	Project: Student Records Database

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Project: Student Records Database

CRUD Operations in Python

Intermediate8 min read•Updated: 2026-10-01

Study Materials Cheatsheet

CRUD Operations in Python with SQLite

1. Setting Up Our Sample Schema

Let's work with an e-commerce products table:

1import sqlite3
2 
3def get_connection():
  conn = sqlite3.connect("store.db")
  conn.row_factory = sqlite3.Row
  return conn
7 
8with get_connection() as conn:
  conn.execute("""
  CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      category TEXT NOT NULL,
      price REAL NOT NULL,
      stock INTEGER DEFAULT 0
  );
  """)

2. CREATE: Single and Bulk Inserts

Single Insert (`cursor.lastrowid`)

When inserting a single record, cursor.lastrowid retrieves the auto-generated primary key:

1with get_connection() as conn:
  cursor = conn.cursor()
  cursor.execute(
      "INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?);",
      ("Wireless Ergonomic Mouse", "Electronics", 2999.0, 45)
  )
  product_id = cursor.lastrowid
  print(f"Product created with ID: {product_id}")

High-Performance Bulk Inserts with `executemany()`

Avoid looping over single execute() statements for thousands of records. Calling cursor.executemany() executes the batch within a single optimized transaction:

1inventory_batch = [
  ("USB-C Fast Charger 65W", "Electronics", 1499.0, 100),
  ("Mechanical Gaming Keyboard", "Electronics", 4999.0, 25),
  ("Noise-Cancelling Headphones", "Audio", 8999.0, 15),
  ("Laptop Stand Aluminum", "Accessories", 1299.0, 60),
6]
7 
8with get_connection() as conn:
  cursor = conn.cursor()
  cursor.executemany(
      "INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?);",
      inventory_batch
  )
  print(f"Batch inserted: {cursor.rowcount} rows added.")

3. READ: Querying, Filtering, and Aggregation

Parameterized Filtering and Sorting

1def find_products(category: str, max_price: float):
  with get_connection() as conn:
      cursor = conn.cursor()
      cursor.execute(
          """
          SELECT id, name, price, stock 
          FROM products 
          WHERE category = ? AND price <= ? 
          ORDER BY price ASC;
          """,
          (category, max_price)
      )
      return cursor.fetchall()
14 
15matching_items = find_products("Electronics", 3000.0)
16for item in matching_items:
  print(f" - #{item['id']} {item['name']}: ₹{item['price']:,.2f} (In stock: {item['stock']})")

SQL Aggregations

1with get_connection() as conn:
  cursor = conn.cursor()
  cursor.execute("""
      SELECT 
          COUNT(*) AS total_items, 
          AVG(price) AS average_price,
          MAX(price) AS highest_price
      FROM products;
  """)
  stats = cursor.fetchone()
  print(f"Total Catalog Size : {stats['total_items']}")
  print(f"Average Price      : ₹{stats['average_price']:,.2f}")
  print(f"Most Expensive     : ₹{stats['highest_price']:,.2f}")

4. UPDATE: Modifying Existing Records

Golden Rule of Database Updates: Always include a specific WHERE clause! Omitting WHERE updates every single row in the entire table!

Check cursor.rowcount to verify how many rows were modified:

1def update_product_price(product_id: int, new_price: float) -> bool:
  with get_connection() as conn:
      cursor = conn.cursor()
      cursor.execute(
          "UPDATE products SET price = ? WHERE id = ?;",
          (new_price, product_id)
      )
      # Verify if an actual row was modified
      if cursor.rowcount == 0:
          print(f"Warning: No product found with ID {product_id}.")
          return False
      else:
          print(f"Product #{product_id} price updated to ₹{new_price:,.2f}.")
          return True
15 
16update_product_price(1, 2499.0)

5. DELETE: Removing Records (Hard vs. Soft Deletes)

Hard Delete (Permanent Removal)

1def delete_product(product_id: int):
  with get_connection() as conn:
      cursor = conn.cursor()
      cursor.execute("DELETE FROM products WHERE id = ?;", (product_id,))
      if cursor.rowcount > 0:
          print(f"Product #{product_id} permanently deleted.")
      else:
          print(f"Product #{product_id} not found.")
9 
10delete_product(4)

Soft Delete Pattern (Industry Best Practice)

Instead of permanently wiping records from disk, enterprise applications add an is_active or deleted_at column:

1-- Soft Delete: Flag record as inactive rather than deleting bytes
2UPDATE products SET is_active = 0 WHERE id = 10;
3 
4-- Query only active items:
5SELECT * FROM products WHERE is_active = 1;

⚠️ Common Intermediate Mistakes & Gotchas

1. SQL Injection via String Interpolation

Never format user input into SQL queries using f-strings or %s. Always use parameterized queries with ? placeholders:

1# ❌ DANGEROUS SQL INJECTION VULNERABILITY
2cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")
3 
4# ✅ SECURE PARAMETERIZED QUERY
5cursor.execute("SELECT * FROM users WHERE name = ?", (user_input,))

2. `UPDATE` or `DELETE` Without a `WHERE` Clause

Executing an UPDATE or DELETE statement without a WHERE clause modifies or deletes EVERY row in the entire table!

💻 Try It Yourself: Complete CRUD Lifecycle Script

Scenario

Write an end-to-end Python script that inserts a record into an SQLite table, reads it back, updates its price, and finally deletes the record using parameterized queries.

Complete Solution

1import sqlite3
2 
3with sqlite3.connect(":memory:") as conn:
  cur = conn.cursor()
  cur.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, price REAL)")
  
  # 1. CREATE
  cur.execute("INSERT INTO books (title, price) VALUES (?, ?)", ("Python Mastery", 599.0))
  book_id = cur.lastrowid
  print(f"Created Book ID: {book_id}")
  
  # 2. READ
  cur.execute("SELECT * FROM books WHERE id = ?", (book_id,))
  print("Read Record:", cur.fetchone())
  
  # 3. UPDATE
  cur.execute("UPDATE books SET price = ? WHERE id = ?", (499.0, book_id))
  print("Updated price to ₹499.00.")
  
  # 4. DELETE
  cur.execute("DELETE FROM books WHERE id = ?", (book_id,))
  print(f"Deleted Book ID: {book_id}. Remaining records: {cur.execute('SELECT COUNT(*) FROM books').fetchone()[0]}")

Expected Output

1Created Book ID: 1
2Read Record: (1, 'Python Mastery', 599.0)
3Updated price to ₹499.00.
4Deleted Book ID: 1. Remaining records: 0

Interactive Quiz• Knowledge Check

Practice Quiz

Question 1

Which method on a cursor should you use to insert a list of 500 records in a single batch?

Question 2

How can you retrieve the auto-generated primary key of the most recently inserted row?

Question 3

What cursor attribute indicates the number of rows affected by an UPDATE or DELETE statement?

Question 4

What critical mistake happens if an UPDATE statement is executed without a WHERE clause?

Question 5

What is the primary difference between a "Hard Delete" and a "Soft Delete"?

🚀 What's Next?

In the next lesson, we will continue your intermediate Python journey with Project: Student Records Database (11: Database Basics with SQLite).

👉 Continue to Next Lesson: Project: Student Records Database →

Next Lesson

Project: Student Records Database

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Connecting Python with SQLite	Project: Student Records Database

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

CRUD Operations in Python with SQLite

1. Setting Up Our Sample Schema

2. CREATE: Single and Bulk Inserts

Single Insert (cursor.lastrowid)

High-Performance Bulk Inserts with executemany()

3. READ: Querying, Filtering, and Aggregation

Parameterized Filtering and Sorting

SQL Aggregations

4. UPDATE: Modifying Existing Records

5. DELETE: Removing Records (Hard vs. Soft Deletes)

Hard Delete (Permanent Removal)

Soft Delete Pattern (Industry Best Practice)

⚠️ Common Intermediate Mistakes & Gotchas

1. SQL Injection via String Interpolation

2. UPDATE or DELETE Without a WHERE Clause

💻 Try It Yourself: Complete CRUD Lifecycle Script

Scenario

Complete Solution

Expected Output

Practice Quiz

🚀 What's Next?

Project: Student Records Database

Related Lessons

Practice Quiz

Loading Page Content...

CRUD Operations in Python with SQLite

1. Setting Up Our Sample Schema

2. CREATE: Single and Bulk Inserts

Single Insert (cursor.lastrowid)

High-Performance Bulk Inserts with executemany()

3. READ: Querying, Filtering, and Aggregation

Parameterized Filtering and Sorting

SQL Aggregations

4. UPDATE: Modifying Existing Records

5. DELETE: Removing Records (Hard vs. Soft Deletes)

Hard Delete (Permanent Removal)

Soft Delete Pattern (Industry Best Practice)

⚠️ Common Intermediate Mistakes & Gotchas

1. SQL Injection via String Interpolation

2. UPDATE or DELETE Without a WHERE Clause

💻 Try It Yourself: Complete CRUD Lifecycle Script

Scenario

Complete Solution

Expected Output

Practice Quiz

🚀 What's Next?

Project: Student Records Database

Related Lessons

Practice Quiz

Single Insert (`cursor.lastrowid`)

High-Performance Bulk Inserts with `executemany()`

2. `UPDATE` or `DELETE` Without a `WHERE` Clause

Single Insert (`cursor.lastrowid`)

High-Performance Bulk Inserts with `executemany()`

2. `UPDATE` or `DELETE` Without a `WHERE` Clause