Database rollback fixtures in PyTest - Test Execution Trace

Test Overview

This test uses a pytest fixture to start a database transaction before the test and roll it back after the test finishes. It verifies that changes made during the test do not persist in the database.

Test Code - pytest

PyTest

import pytest
import sqlite3

@pytest.fixture
def db_connection():
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
    conn.commit()
    conn.execute('BEGIN')
    yield conn
    conn.rollback()
    conn.close()

def test_insert_user(db_connection):
    cursor = db_connection.cursor()
    cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
    cursor.execute('SELECT COUNT(*) FROM users')
    count = cursor.fetchone()[0]
    assert count == 1

    # After test ends, rollback fixture will undo this insert

Execution Trace - 5 Steps

Step	Action	System State	Assertion	Result
1	Test starts and pytest calls the db_connection fixture	An in-memory SQLite database is created with a 'users' table	-	PASS
2	Test function test_insert_user runs and inserts a user named 'Alice'	Database now has one row in 'users' table	-	PASS
3	Test queries the count of users in the table	Query returns count = 1	Assert that count == 1	PASS
4	Test finishes, pytest resumes fixture teardown	Fixture calls rollback on the database connection	-	PASS
5	Fixture closes the database connection	In-memory database is discarded	-	PASS

Failure Scenario

Failing Condition: If the table 'users' does not exist or insert fails

Execution Trace Quiz - 3 Questions

What does the fixture do after the test finishes?

A Commits the changes permanently

B Deletes the database file

C Rolls back the database changes and closes the connection

D Does nothing

Key Result

Using a rollback fixture ensures tests do not leave data behind, keeping the database clean and tests independent.