Module 12 · Python for SDETs
The API said success —
but did it save?
The senior move on top of the capstone: verify the persisted side effect, not just the response
read-only
sqlite3 — stdlib
JOIN
the record
A 201 is the app's word — the row is the record
What the app REPORTS
201 Created
{ "id": 7, "total": 59.98 }
💬The response is the app's word: "I accepted it."
What the app PERSISTED
# orders
id=7 · status=pending · total=59.98
# order_items
order_id=7 · product_id=1 · qty=2
🗄️The database row is the record: "here is what saved."
The pattern you reuse everywhere
Arrange & act via API → assert via DB
reset_db
→
api.post("/api/orders")
→
db.execute("SELECT …")
→
persisted ✓
writes go through the app
the DB is your read-only verification layer
🔒Let the app own its writes — its rules and computed columns run. You only read to confirm the effect, so a test can never corrupt the app's data.
Open it read-only, pass values as ?
import sqlite3
con = sqlite3.connect(
f"file:{DB_PATH.as_posix()}?mode=ro", uri=True)
con.row_factory = sqlite3.Row
row = con.execute(
"SELECT id, name, stock FROM products"
" WHERE slug = ?", ("wireless-mouse",),
).fetchone()
🛡️mode=ro makes writing impossible — a stray query can't corrupt the app's data. An INSERT raises OperationalError.
💉The value goes in as a ? parameter, never pasted into the SQL string. The one non-negotiable habit.
📦sqlite3 is in the standard library — zero install.
201 = accepted · row = saved · JOIN = saved correctly
The parent — the orders row
order = db.execute(
"SELECT status, total FROM orders"
" WHERE id = ?", (order_id,)).fetchone()
assert order is not None
assert order["status"] == "pending"
assert order["total"] == pytest.approx(59.98)
The children — order_items via JOIN
items = db.execute(
"""SELECT oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = ?"""
, (order_id,)).fetchall()
assert [(i["product_id"], i["quantity"]) for i in items] == [(1, 2)]
Reach what the API won't return
# the API never hands the password back — rightly
row = db.execute(
"SELECT password FROM users WHERE email = ?",
stored = row["password"]
assert stored != "customer123"
assert stored.startswith("$2")
assert len(stored) == 60
🔑"Is the password stored hashed, not plain text?" is unanswerable from the outside…
🔎…and one SELECT away from the inside. Some truths only live in the schema.
Same idea, on the job
SQLite → production: swap the driver, not the pattern
sqlite3
→
psycopg
·
PyMySQL
|
?
→
%s
|
file path
→
connection string
connect → parameterize → assert → close
🧭The SQL you wrote (SELECT, WHERE, JOIN) is standard, and a Postgres NUMERIC just comes back as a Decimal. The container comes from one docker run — or a CI service container.
🗄️
You can verify
the record
Response, UI — and the persisted truth the API sits on top of
🛡️
You can now
A read-only connection, a parameterized query, and a JOIN that proves it saved
🧭
On the job
SQLite today, Postgres tomorrow — the same four steps, only the driver changes
🧩
Bolt it on
Add DB verification to your capstone — "how do you know it actually saved?"