CRUD Operations with sqlite3

CRUD — Create, Read, Update, Delete — is the core of most database-backed programs, from a to-do app to an e-commerce back end. In this lesson you will write small, reusable functions for each operation, check how many rows a statement affected, and learn the patterns for filtering, searching, IN lists, sorting and pagination without ever building unsafe SQL.

The Four Operations

Create uses INSERT; cursor.lastrowid gives the new row's id. Read uses SELECT with WHERE, ORDER BY and LIMIT. Update uses UPDATE ... SET ... WHERE and Delete uses DELETE FROM ... WHERE; both report the number of affected rows in cursor.rowcount, which tells you whether the record existed. Never run UPDATE or DELETE without a WHERE clause unless you really mean every row.

Dynamic Queries Done Safely

Placeholders can only stand for values. For a search term, put the wildcards in the value: ("%" + term + "%",). For an IN list, generate one ? per item and pass the items as parameters. Table and column names cannot be parameters, so when the user chooses a sort column, map their choice through a whitelist dictionary of allowed columns instead of inserting their text into the SQL.

Examples

Create, read, update and delete functions

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.execute("""CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary INTEGER NOT NULL
)""")

def create(name, department, salary):
    with conn:
        cur = conn.execute("INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)",
                           (name, department, salary))
    return cur.lastrowid

def read(emp_id):
    row = conn.execute("SELECT * FROM employees WHERE id = ?", (emp_id,)).fetchone()
    return dict(row) if row else None

def update_salary(emp_id, new_salary):
    with conn:
        cur = conn.execute("UPDATE employees SET salary = ? WHERE id = ?", (new_salary, emp_id))
    return cur.rowcount

def delete(emp_id):
    with conn:
        cur = conn.execute("DELETE FROM employees WHERE id = ?", (emp_id,))
    return cur.rowcount

asha = create("Asha", "Engineering", 90000)
ravi = create("Ravi", "Sales", 55000)
meera = create("Meera", "Engineering", 82000)

print(read(asha))
print("updated rows:", update_salary(ravi, 60000), read(ravi)["salary"])
print("deleted rows:", delete(meera), "| after delete:", read(meera))
print("deleting again:", delete(meera))

Output

{'id': 1, 'name': 'Asha', 'department': 'Engineering', 'salary': 90000}
updated rows: 1 60000
deleted rows: 1 | after delete: None
deleting again: 0

Searching, IN lists, whitelisted sorting and pagination

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT, price INTEGER)")
conn.executemany("INSERT INTO books (title, author, price) VALUES (?, ?, ?)", [
    ("Python Crash Notes", "Asha", 450),
    ("Deep Python", "Ravi", 899),
    ("SQL for Beginners", "Meera", 350),
    ("Python Testing", "Asha", 599),
    ("Data with Pandas", "Kiran", 750),
])

term = "python"                                      # LIKE is case-insensitive for ASCII
print(conn.execute("SELECT title FROM books WHERE title LIKE ? ORDER BY title",
                   (f"%{term}%",)).fetchall())

authors = ["Asha", "Kiran"]
placeholders = ", ".join("?" for _ in authors)       # "?, ?" — values are still parameters
query = f"SELECT title, author FROM books WHERE author IN ({placeholders}) ORDER BY id"
print(conn.execute(query, authors).fetchall())

def page(number, size=2, sort="price"):
    allowed = {"price": "price", "title": "title"}   # column names cannot be parameters
    column = allowed.get(sort, "id")
    return conn.execute(f"SELECT title, price FROM books ORDER BY {column} LIMIT ? OFFSET ?",
                        (size, (number - 1) * size)).fetchall()

print(page(1))
print(page(2))
print(page(1, sort="price; DROP TABLE books"))       # unknown choice falls back to id

Output

[('Deep Python',), ('Python Crash Notes',), ('Python Testing',)]
[('Python Crash Notes', 'Asha'), ('Python Testing', 'Asha'), ('Data with Pandas', 'Kiran')]
[('SQL for Beginners', 350), ('Python Crash Notes', 450)]
[('Python Testing', 599), ('Data with Pandas', 750)]
[('Python Crash Notes', 450), ('Deep Python', 899)]

A small repository class with a dataclass model

Python

import sqlite3
from dataclasses import dataclass

@dataclass
class Task:
    title: str
    done: bool = False
    id: int | None = None

class TaskRepository:
    def __init__(self, conn):
        self.conn = conn
        conn.execute("CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL)")

    def add(self, task):
        with self.conn:
            task.id = self.conn.execute("INSERT INTO tasks (title, done) VALUES (?, ?)",
                                        (task.title, int(task.done))).lastrowid
        return task

    def all(self, only_open=False):
        sql = "SELECT id, title, done FROM tasks" + (" WHERE done = 0" if only_open else "") + " ORDER BY id"
        return [Task(title, bool(done), id_) for id_, title, done in self.conn.execute(sql)]

    def complete(self, task_id):
        with self.conn:
            return self.conn.execute("UPDATE tasks SET done = 1 WHERE id = ?", (task_id,)).rowcount == 1

repo = TaskRepository(sqlite3.connect(":memory:"))
for title in ["Buy milk", "Write report", "Call Ravi"]:
    repo.add(Task(title))
print(repo.complete(2), repo.complete(99))
for task in repo.all(only_open=True):
    print(task)

Output

True False
Task(title='Buy milk', done=False, id=1)
Task(title='Call Ravi', done=False, id=3)

Common Mistakes

Running UPDATE or DELETE without a WHERE clause and changing every row.
Not checking rowcount, so updates to non-existent records fail silently.
Putting % wildcards in the SQL string instead of in the parameter value.
Inserting user-chosen column names straight into SQL instead of using a whitelist.
Using LIMIT/OFFSET pagination without ORDER BY, which gives unpredictable pages.

Key Points to Remember

INSERT → lastrowid; UPDATE/DELETE → rowcount tells you how many rows changed.
Wildcards for LIKE belong in the parameter value.
Generate one placeholder per item for IN lists.
Whitelist identifiers such as sort columns — they cannot be parameters.
Wrapping SQL in small functions or a repository class keeps the rest of the code clean.

Practice the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

Try Create, read, update and delete functions in Webnest Codelab Try Searching, IN lists, whitelisted sorting and pagination in Webnest Codelab Try A small repository class with a dataclass model in Webnest Codelab