APPLYDJINN
← pytest: reliable Python testsProgress & review ↗

Typorithm · medium

Owned SQLite integration and rollback

pythonpytesttestingindependent-practice
Saved on this device
Full lesson · 4 learning steps Open any exercise for focused practice
1

Step 1 of 4 · intro card

Owned SQLite integration and rollback

0%

Concept and contract

Exercise a real transaction without using the application database.

A repository test can use a real database engine while still owning every byte of its state. An in-memory SQLite connection is enough to demonstrate inserts, parameter binding and rollback. It does not require the application’s live database, user credentials or a network service.

This is an integration test of Python code with SQLite semantics. It is not evidence about another database engine’s isolation levels, schema migrations or deployment configuration. State the boundary honestly and use the real target engine for claims that depend on its behavior.

Mechanism and worked example

The fixture creates a connection, initializes one table, yields it and closes it in finally.

The fixture creates a connection, initializes one table, yields it and closes it in finally. The helper inserts a parameterized value. A normal insertion is checked through an independent SELECT query. A second test raises inside the connection context and confirms the transaction rolled back.

The context manager manages commit or rollback for a transaction; it does not itself replace the need to close the connection. Keeping close in the fixture makes that ownership explicit. Bound parameters prevent values from becoming SQL syntax and also preserve quotes correctly.

Complete worked example

python

import sqlite3
import pytest


def add_name(db, value):
    db.execute("INSERT INTO names(value) VALUES (?)", (value,))


@pytest.fixture
def db():
    connection = sqlite3.connect(":memory:")
    connection.execute("CREATE TABLE names(value TEXT NOT NULL)")
    try:
        yield connection
    finally:
        connection.close()


def test_inserted_value(db):
    add_name(db, "Ada")
    assert db.execute("SELECT value FROM names").fetchall() == [("Ada",)]


def test_failed_transaction_rolls_back(db):
    with pytest.raises(RuntimeError, match="cancel"):
        with db:
            add_name(db, "temporary")
            raise RuntimeError("cancel")
    assert db.execute("SELECT COUNT(*) FROM names").fetchone()[0] == 0
Reasoning
The fixture creates a connection, initializes one table, yields it and closes it in finally. The helper inserts a parameterized value. A normal insertion is checked through an independent SELECT query. A second test raises inside the connection context and confirms the transaction rolled back. The context manager manages commit or rollback for a transaction; it does not itself replace the need to close the connection. Keeping close in the fixture makes that ownership explicit. Bound parameters prevent values from becoming SQL syntax and also preserve quotes correctly.
Open in local Python playground ↗

Focused test decision: context is in the complete example

python

db.execute("INSERT INTO names(value) VALUES (?)", (value,))
assert db.execute("SELECT COUNT(*) FROM names").fetchone()[0] == 1
Focused test decision
Type this short test decision after understanding the complete runnable module. Desktop typing records practice; Run tests in the local playground executes real pytest.

Counterexamples and common mistakes

The controlled helper prepends wrong text, so the read-back assertion fails.

The controlled helper prepends wrong text, so the read-back assertion fails. Using the same helper for both write and expected-value construction would weaken the test. Checking only row count could miss corrupted content or values assigned to the wrong columns.

Never point a teaching test at production and promise that rollback makes it harmless. DDL behavior, implicit commits, external side effects and code paths outside the transaction can differ. This fixture creates only its own private in-memory table.

Controlled bug: the tests must report failure

python

import sqlite3
import pytest


def add_name(db, value):
    db.execute("INSERT INTO names(value) VALUES (?)", ("wrong " + value,))


@pytest.fixture
def db():
    connection = sqlite3.connect(":memory:")
    connection.execute("CREATE TABLE names(value TEXT NOT NULL)")
    try:
        yield connection
    finally:
        connection.close()


def test_inserted_value(db):
    add_name(db, "Ada")
    assert db.execute("SELECT value FROM names").fetchall() == [("Ada",)]


def test_failed_transaction_rolls_back(db):
    with pytest.raises(RuntimeError, match="cancel"):
        with db:
            add_name(db, "temporary")
            raise RuntimeError("cancel")
    assert db.execute("SELECT COUNT(*) FROM names").fetchone()[0] == 0
Reasoning
The controlled helper prepends wrong text, so the read-back assertion fails. Using the same helper for both write and expected-value construction would weaken the test. Checking only row count could miss corrupted content or values assigned to the wrong columns. Never point a teaching test at production and promise that rollback makes it harmless. DDL behavior, implicit commits, external side effects and code paths outside the transaction can differ. This fixture creates only its own private in-memory table.
Open in local Python playground ↗

Independent practice

Add a name containing an apostrophe and assert exact read-back.

Add a name containing an apostrophe and assert exact read-back. Retain the rollback test. This tests parameter handling with a small representative boundary rather than attempting a live exploit or touching external data.

Type the execute call with a parameter tuple before running the full module. Remember that a one-element tuple needs a trailing comma. Predict how removing apostrophes in an attempted cleanup step would silently corrupt a legitimate name.

Step 1: Add an exact apostrophe-containing name round-trip in the owned SQLite fixture. Step 2: Keep the existing passing cases. Add the smallest new assertion first; predict the failure before running pytest.

Practice starter: solve before reading the solution

python

import sqlite3
import pytest


def add_name(db, value):
    db.execute("INSERT INTO names(value) VALUES (?)", (value,))


@pytest.fixture
def db():
    connection = sqlite3.connect(":memory:")
    connection.execute("CREATE TABLE names(value TEXT NOT NULL)")
    try:
        yield connection
    finally:
        connection.close()


def test_inserted_value(db):
    add_name(db, "Ada")
    assert db.execute("SELECT value FROM names").fetchall() == [("Ada",)]


def test_failed_transaction_rolls_back(db):
    with pytest.raises(RuntimeError, match="cancel"):
        with db:
            add_name(db, "temporary")
            raise RuntimeError("cancel")
    assert db.execute("SELECT COUNT(*) FROM names").fetchone()[0] == 0

# Task: Add an exact apostrophe-containing name round-trip in the owned SQLite fixture.
Reasoning
Add an exact apostrophe-containing name round-trip in the owned SQLite fixture.
Open in local Python playground ↗
  • Hint 1: Use the existing bound-value helper.
  • Hint 2: Assert the stored tuple contains exactly O'Neil.

Solution, tests and trade-offs

The solution adds the quoted-name case.

The solution adds the quoted-name case. The practice probe strips apostrophes before binding the value. The ordinary Ada insertion and rollback behavior remain green, but the new exact-value assertion rejects the loss.

This verifies value preservation for the documented input, not a complete security audit. A real repository also needs constraints, conflict policy, transaction ownership and error mapping tests. Keep those contracts separate enough that a failure identifies the responsibility that broke.

Practice solution and acceptance checks

python

import sqlite3
import pytest


def add_name(db, value):
    db.execute("INSERT INTO names(value) VALUES (?)", (value,))


@pytest.fixture
def db():
    connection = sqlite3.connect(":memory:")
    connection.execute("CREATE TABLE names(value TEXT NOT NULL)")
    try:
        yield connection
    finally:
        connection.close()


def test_inserted_value(db):
    add_name(db, "Ada")
    assert db.execute("SELECT value FROM names").fetchall() == [("Ada",)]


def test_failed_transaction_rolls_back(db):
    with pytest.raises(RuntimeError, match="cancel"):
        with db:
            add_name(db, "temporary")
            raise RuntimeError("cancel")
    assert db.execute("SELECT COUNT(*) FROM names").fetchone()[0] == 0

def test_quoted_name_preserved(db):
    add_name(db, "O'Neil")
    assert db.execute("SELECT value FROM names").fetchall() == [("O'Neil",)]
Reasoning
The solution adds the quoted-name case. The practice probe strips apostrophes before binding the value. The ordinary Ada insertion and rollback behavior remain green, but the new exact-value assertion rejects the loss. This verifies value preservation for the documented input, not a complete security audit. A real repository also needs constraints, conflict policy, transaction ownership and error mapping tests. Keep those contracts separate enough that a failure identifies the responsibility that broke.
Open in local Python playground ↗

Interview reasoning and next step

Explain why a database context manager and connection.close solve different lifecycle problems.

Explain why a database context manager and connection.close solve different lifecycle problems. Show how the rollback assertion is independent from the inserted data and why bound values differ from interpolated SQL text.

Next control clocks and randomness. Integration correctness still depends on deterministic input; a genuine database does not make a test reliable if its expected result changes with the wall clock or global random state.

Optional stretch — after the core task works: Introduce one deliberate implementation mistake and explain which assertion catches it. A green run alone is not evidence that every possible bug is covered.

Computer lab: https://applydjinn.com/typorithm/pytest-lab-20261106.zip. Run verify.py in an isolated environment. Worked examples and solutions pass, controlled counterexamples fail, and the new practice assertions reject a mutation missed by the baseline. Run --practice pyq-sqlite after changing the supplied exercise. Python executes on your computer or in the browser, without submitting code to a server. Native question progress is a separate learning record.

Exercise a real transaction without using the application database.