Database rollback fixtures in PyTest - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the test outcome with this rollback fixture?

Given the following pytest fixture that uses a database transaction rollback, what will be the output of the test function?

PyTest

import pytest

@pytest.fixture
def db_transaction(db_connection):
    transaction = db_connection.begin()
    yield
    transaction.rollback()

def test_insert(db_transaction, db_connection):
    db_connection.execute("INSERT INTO users (id, name) VALUES (1, 'Alice')")
    result = db_connection.execute("SELECT COUNT(*) FROM users").fetchone()[0]
    print(result)

A 1

B 0

C Raises an exception because of missing commit

D None

💡 Hint

Think about when the rollback happens in relation to the test execution.

The fixture starts a transaction before the test and rolls it back after the test finishes. During the test, the insert is visible, so the count is 1. After the test, the rollback undoes the insert.

❓ assertion

intermediate

Which assertion correctly verifies rollback behavior?

You want to test that after a test using a rollback fixture, the database table 'orders' is empty. Which assertion in the test will correctly verify this?

PyTest

def test_orders_empty_after_rollback(db_connection):
    count = db_connection.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
    # Which assertion is correct here?

A assert count is None

B assert count != 0

C assert count == 0

D assert count > 0

💡 Hint

Rollback should remove all inserted rows after the test.

Rollback undoes all changes, so the table should be empty, meaning count is zero.

🔧 Debug

advanced

Why does this rollback fixture not undo changes?

Consider this pytest fixture: @pytest.fixture def db_rollback(db_connection): yield db_connection.rollback() A test using this fixture inserts a row, but the row remains after the test. Why?

A Rollback is called before yield, so changes are undone too early

B The fixture commits changes before rollback, making rollback ineffective

C The database connection does not support rollback

D The transaction was never started, so rollback has no effect

💡 Hint

Think about what is missing before yield to start a transaction.

Without starting a transaction, rollback does nothing because changes are auto-committed.

❓ framework

advanced

Which pytest fixture scope is best for database rollback tests?

You want to run tests that modify the database but rollback after each test to keep tests isolated. Which pytest fixture scope is best to achieve this?

A function

B module

C session

D class

💡 Hint

Rollback should happen after each individual test.

Function scope runs the fixture for each test function, allowing rollback after each test to isolate changes.

🧠 Conceptual

expert

Why use database rollback fixtures instead of test data cleanup?

In automated testing, why is using a database rollback fixture preferred over manually deleting test data after tests?

A Manual deletion is faster but less reliable than rollback

B Rollback is faster and guarantees test isolation by undoing all changes atomically

C Rollback requires less database permissions than manual deletion

D Manual deletion can rollback changes automatically

💡 Hint

Think about atomicity and test isolation.

Rollback undoes all changes in one step, ensuring no leftover data and better test isolation. Manual deletion can miss data or cause side effects.