Course topics

By WebNest Studio Engineering Team

Python Tutorial

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.

Why it matters

Create, read, update and delete are the four operations behind almost every screen of an application: sign up, view a profile, edit it, remove it. Writing them as small, safe functions is the core of back-end work, and it is the same whether you later use raw SQL, an ORM or a web framework.

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.

Exercise

Complete the three functions for a products table. The program adds two products, changes the price of the first, deletes the second and prints how many rows the delete removed, followed by the rows that remain.

Expected output: 1 then [(1, 'pen', 12)]

Python · starter code
import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER)")


def add_product(name, price):
    # TODO: insert a row and return its new id
    pass


def update_price(product_id, price):
    # TODO: change the price of one product
    pass


def delete_product(product_id):
    # TODO: delete one product and return how many rows were removed
    pass


pen_id = add_product("pen", 10)
book_id = add_product("book", 50)
update_price(pen_id, 12)
print(delete_product(book_id))
print(connection.execute("SELECT id, name, price FROM products").fetchall())
Show hints
  • The cursor returned by connection.execute(...) has lastrowid after an INSERT and rowcount after an UPDATE or DELETE.
  • Always include a WHERE id = ? clause in an UPDATE or DELETE, and commit after each change.
Show solution
Python · solution
import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER)")


def add_product(name, price):
    cursor = connection.execute("INSERT INTO products (name, price) VALUES (?, ?)", (name, price))
    connection.commit()
    return cursor.lastrowid


def update_price(product_id, price):
    connection.execute("UPDATE products SET price = ? WHERE id = ?", (price, product_id))
    connection.commit()


def delete_product(product_id):
    cursor = connection.execute("DELETE FROM products WHERE id = ?", (product_id,))
    connection.commit()
    return cursor.rowcount


pen_id = add_product("pen", 10)
book_id = add_product("book", 50)
update_price(pen_id, 12)
print(delete_product(book_id))
print(connection.execute("SELECT id, name, price FROM products").fetchall())

Quick quiz

1. Which SQL statements correspond to create, read, update and delete?

  • A. INSERT, SELECT, UPDATE, DELETE
  • B. CREATE, READ, UPDATE, DROP
  • C. ADD, GET, SET, REMOVE
  • D. INSERT, FETCH, ALTER, DELETE
Show answer

A. These four statements work on the rows of a table.

2. What does an UPDATE statement with no WHERE clause do?

  • A. Nothing
  • B. Changes every row in the table
  • C. Raises an error
  • D. Changes only the first row
Show answer

B. The same is true of DELETE. Always check the WHERE clause.

3. Can a placeholder be used for a column name, for example in ORDER BY?

  • A. Yes
  • B. Only in SQLite
  • C. No; placeholders are only for values, so the column must be checked against a list of allowed names
  • D. Only for numeric columns
Show answer

C. Checking against a fixed list of names is called whitelisting.

Interview questions

What is the difference between fetchone(), fetchmany() and fetchall()?

Show answer

fetchone() returns the next row, or None when there are no more. fetchmany(n) returns the next n rows as a list. fetchall() returns all remaining rows as a list, which can use a great deal of memory for a large result. A cursor can also be looped over directly, which reads the rows one at a time.

How do you let a user choose the column to sort by without risking SQL injection?

Show answer

A column name cannot be passed as a placeholder, so it must be put into the SQL text. To do that safely, compare the user's choice with a fixed set of permitted column names and use it only if it is in that set, falling back to a default otherwise. The user's text itself is never inserted into the query.

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 in the playground

Write and run your own code for this topic, or start from the exercise or an example. Runs Python in your browser. Your code for each topic is kept in this browser.

Run the examples

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

WebNest Studio

Written by

WebNest Studio Engineering Team

Practical lessons from developers working with Java, Python, React, databases and production systems.

Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering