Database fixture patterns in PyTest - Test Execution Trace

Test Overview

This test uses a pytest fixture to set up a temporary database table before the test runs and cleans it up after. It verifies that data can be inserted and queried correctly within the test.

Test Code - pytest

PyTest

import pytest
import sqlite3

@pytest.fixture
 def db_connection():
    # Setup: create in-memory SQLite database
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
    conn.commit()
    yield conn
    # Teardown: close connection
    conn.close()

def test_insert_and_query(db_connection):
    cursor = db_connection.cursor()
    cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
    db_connection.commit()
    cursor.execute('SELECT name FROM users WHERE id=1')
    result = cursor.fetchone()
    assert result[0] == 'Alice'

Execution Trace - 7 Steps

Step	Action	System State	Assertion	Result
1	Test starts and pytest identifies the db_connection fixture	No database connection exists yet	-	PASS
2	Fixture db_connection runs: creates in-memory SQLite database and users table	In-memory SQLite database with empty users table	-	PASS
3	Test function test_insert_and_query runs using db_connection fixture	Database connection active with users table	-	PASS
4	Insert a user named 'Alice' into users table	users table has one row with name 'Alice'	-	PASS
5	Query the users table for user with id=1	Query returns ('Alice',)	Assert that fetched name is 'Alice'	PASS
6	Test completes successfully	Database connection still open	-	PASS
7	Fixture teardown: close the database connection	Database connection closed, resources cleaned	-	PASS

Failure Scenario

Failing Condition: The users table does not exist or insert/query fails

Execution Trace Quiz - 3 Questions

What does the db_connection fixture do before the test runs?

A Inserts test data into the users table

B Closes the database connection

C Creates an in-memory SQLite database and users table

D Runs the test function

Key Result

Using fixtures to set up and tear down database state ensures tests run in isolation and do not depend on external data, making tests reliable and repeatable.