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
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))
{'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
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
[('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
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)
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)]
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(...)haslastrowidafter an INSERT androwcountafter an UPDATE or DELETE. - Always include a
WHERE id = ?clause in an UPDATE or DELETE, and commit after each change.
Show 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.
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