CRUD Operations

Most database applications revolve around four core actions.

These actions are called CRUD:

• Create
• Read
• Update
• Delete

If you can perform CRUD operations, you can build many real applications.

Examples:

• student systems
• inventory tools
• finance trackers
• booking apps
• login systems

In this lesson, you will use Python with SQLite to perform all four operations.

Project Setup

We will use a database named:

store.db

And a table named:

products

Step 1: Create Database and Table

import sqlite3

conn = sqlite3.connect("store.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
)
""")

conn.commit()

Table Design

id

Unique product id.

name

Product name.

price

Product price.

CREATE (Insert Data)

Create means adding new records.

Example: Insert One Product

cursor.execute("""
INSERT INTO products (name, price)
VALUES ('Pen', 2.5)
""")

conn.commit()

Insert Multiple Products

items = [
    ("Book", 5.0),
    ("Bag", 20.0),
    ("Pencil", 1.0)
]

cursor.executemany(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    items
)

conn.commit()

Why Use ?

? placeholders safely insert values and prevent SQL injection.

Always prefer this style for user data.

READ (Select Data)

Read means viewing stored records.

Select All

cursor.execute("SELECT * FROM products")

rows = cursor.fetchall()

print(rows)

Example output:

[(1, 'Pen', 2.5), (2, 'Book', 5.0)]

Select Specific Columns

cursor.execute("SELECT name, price FROM products")
print(cursor.fetchall())

Select One Row

cursor.execute("SELECT * FROM products WHERE id = 1")
print(cursor.fetchone())

UPDATE (Change Data)

Update means modifying existing records.

Change Product Price

cursor.execute("""
UPDATE products
SET price = 3.0
WHERE name = 'Pen'
""")

conn.commit()

Update Using Placeholders

cursor.execute(
    "UPDATE products SET price = ? WHERE name = ?",
    (6.0, "Book")
)

conn.commit()

DELETE (Remove Data)

Delete means removing records.

Delete One Product

cursor.execute(
    "DELETE FROM products WHERE name = ?",
    ("Pencil",)
)

conn.commit()

Important Warning

Always use WHERE carefully.

Without it:

DELETE FROM products;

This removes all rows.

Full CRUD Example

import sqlite3

conn = sqlite3.connect("store.db")
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL
)
""")

cursor.execute(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    ("Pen", 2.5)
)

cursor.execute("SELECT * FROM products")
print(cursor.fetchall())

cursor.execute(
    "UPDATE products SET price = ? WHERE name = ?",
    (3.0, "Pen")
)

cursor.execute(
    "DELETE FROM products WHERE name = ?",
    ("Pen",)
)

conn.commit()
conn.close()

Common Beginner Errors

Forgetting commit()

Changes may not save.

Missing WHERE

Could update or delete many rows.

Wrong Placeholder Data

Use tuples correctly:

("Pen",)

Single-item tuples need a comma.

Table Does Not Exist

Create the table first.

Code Along

Create:

school.db

Build table:

students(id, name, grade)

Do all CRUD actions.

Mini Challenge

Build a task tracker.

Database:

tasks.db

Table:

tasks(id, title, done)

Tasks:

1. Insert two tasks
2. Show all tasks
3. Mark one as done
4. Delete one task
5. Show final rows

Real World Use Case

CRUD powers almost every app: users, products, invoices, bookings, messages, and reports.

Quiz

1. What does CRUD stand for?
2. Which SQL command is used for Read?
3. Why is WHERE important in Update/Delete?
4. Why use ? placeholders?
5. Why call commit()?

Assignment

Create a contacts database with name and phone number, then perform all four CRUD actions.

Summary

You learned how to perform Create, Read, Update, and Delete operations using Python and SQLite.