Database fixture patterns in PyTest - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of pytest fixture scope behavior

Consider the following pytest fixtures and test functions. What will be the output when running the tests?

PyTest

import pytest

@pytest.fixture(scope="module")
def db_connection():
    print("Setup DB connection")
    yield "connection"
    print("Teardown DB connection")

@pytest.fixture(scope="function")
def db_cursor(db_connection):
    print("Setup DB cursor")
    yield "cursor"
    print("Teardown DB cursor")

def test_query1(db_cursor):
    print("Running test_query1")
    assert db_cursor == "cursor"

def test_query2(db_cursor):
    print("Running test_query2")
    assert db_cursor == "cursor"

A

Setup DB cursor
Setup DB connection
Running test_query1
Teardown DB cursor
Setup DB cursor
Running test_query2
Teardown DB cursor
Teardown DB connection

B

Setup DB connection
Setup DB cursor
Running test_query1
Setup DB cursor
Running test_query2
Teardown DB cursor
Teardown DB cursor
Teardown DB connection

C

Setup DB connection
Setup DB cursor
Running test_query1
Teardown DB cursor
Setup DB cursor
Running test_query2
Teardown DB cursor
Teardown DB connection

D

Setup DB connection
Setup DB cursor
Running test_query1
Teardown DB cursor
Teardown DB connection
Setup DB cursor
Running test_query2
Teardown DB cursor

💡 Hint

Remember that module-scoped fixtures run once per module, function-scoped fixtures run per test function.

The module-scoped fixture 'db_connection' sets up once before any tests run and tears down after all tests. The function-scoped fixture 'db_cursor' sets up and tears down around each test function. So the output shows setup and teardown of cursor twice, but connection only once.

❓ assertion

intermediate

Correct assertion for database fixture test

You have a pytest fixture that returns a database session object. Which assertion correctly verifies that the session is active during a test?

PyTest

import pytest

@pytest.fixture
def db_session():
    class Session:
        def __init__(self):
            self.active = True
        def close(self):
            self.active = False
    session = Session()
    yield session
    session.close()

def test_session_active(db_session):
    # Which assertion below is correct here?
    pass

A assert db_session.active == False

B assert db_session.active is True

C assert db_session is None

D assert db_session.closed is True

💡 Hint

The session should be active during the test, so active should be True.

The fixture yields a session object with active=True. The test runs while active is True. After the test, close() sets active to False. So the correct assertion during the test is that active is True.

🔧 Debug

advanced

Identify the cause of fixture reuse failure

Given the following pytest fixture and tests, why does the second test fail?

PyTest

import pytest

@pytest.fixture(scope="function")
def db_conn():
    conn = open_db_connection()
    yield conn
    conn.close()

def test_insert(db_conn):
    db_conn.insert("data")
    assert db_conn.count() == 1

def test_query(db_conn):
    result = db_conn.query("select *")
    assert len(result) == 1

A The connection is closed before test_insert runs, causing insert to fail.

B The fixture does not yield the connection properly, causing the connection to be None in test_query.

C The tests run in parallel causing race conditions on the database connection.

D The fixture scope is 'function', so each test gets a new connection; data inserted in test_insert is lost before test_query runs.

💡 Hint

Think about how fixture scope affects data persistence between tests.

Because the fixture scope is 'function', each test gets a fresh database connection. The data inserted in test_insert is not present in test_query's connection, causing the query to return empty results.

❓ framework

advanced

Best fixture pattern for transactional tests

Which pytest fixture pattern ensures that each test runs inside a database transaction that is rolled back after the test, keeping the database clean?

A Use a function-scoped fixture that starts a transaction before yield and rolls back after yield.

B Use a module-scoped fixture that commits transactions after each test.

C Use a function-scoped fixture that commits transactions before yield and rolls back after yield.

D Use a session-scoped fixture that never rolls back transactions.

💡 Hint

Think about how to isolate test data changes per test.

Starting a transaction before the test and rolling it back after ensures no test data persists. Function scope ensures this happens for each test individually.

🧠 Conceptual

expert

Choosing fixture scope for integration tests with database

You have a large test suite with many integration tests that use a database. Tests modify data and depend on previous data state. Which fixture scope and pattern is best to balance test speed and data consistency?

A Use a session-scoped fixture to create a single database connection and reset data manually between tests.

B Use function-scoped fixtures with transactions rolled back after each test to isolate data changes.

C Use module-scoped fixtures that create and tear down the database for each module of tests.

D Use class-scoped fixtures that share a connection and commit changes after each test.

💡 Hint

Consider the trade-off between setup overhead and data isolation.

Session-scoped fixtures reduce setup overhead by reusing the connection. Manual data reset between tests balances speed and consistency for large suites where full isolation per test is costly.