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.

Practice

1. What is the main purpose of a database rollback fixture in pytest?

easy

A. To permanently save test data for later use

B. To speed up database queries during tests

C. To create new database tables before tests

D. To undo database changes after each test to keep tests independent

Solution

1. Step 1: Understand the role of rollback fixtures

   Rollback fixtures undo any changes made to the database during a test to keep tests isolated.

2. Step 2: Compare options with rollback purpose

   Only To undo database changes after each test to keep tests independent describes undoing changes after tests, which matches rollback behavior.

3. Final Answer:

   To undo database changes after each test to keep tests independent -> Option D

4. Quick Check:

   Rollback fixture purpose = undo changes [OK]

Hint: Rollback means undo changes after test [OK]

Common Mistakes:

• Confusing rollback with speeding up queries
• Thinking rollback creates tables
• Assuming rollback saves data permanently

2. Which of the following is the correct way to define a pytest fixture that rolls back database changes after a test?

easy

A. @pytest.fixture
def db_fixture():
setup_db()
yield
rollback_db()

B. @pytest.fixture
def db_fixture():
rollback_db()
yield
setup_db()

C. @pytest.fixture
def db_fixture():
yield
setup_db()
rollback_db()

D. @pytest.fixture
def db_fixture():
setup_db()
rollback_db()
yield

Solution

1. Step 1: Understand yield usage in fixtures

   Yield separates setup (before yield) and teardown (after yield) in pytest fixtures.

2. Step 2: Identify correct order for rollback

   Setup happens before yield, rollback (cleanup) after yield. @pytest.fixture def db_fixture(): setup_db() yield rollback_db() follows this order.

3. Final Answer:

   @pytest.fixture\ndef db_fixture():\n setup_db()\n yield\n rollback_db() -> Option A

4. Quick Check:

   Setup before yield, rollback after yield [OK]

Hint: Setup before yield, cleanup after yield [OK]

Common Mistakes:

• Placing rollback before yield
• Calling setup after yield
• Not using yield at all

3. Given this pytest fixture and test, what will be the final count of records in the database after the test runs?

@pytest.fixture
def db_fixture():
    connect_db()
    yield
    rollback_db()


def test_add_record(db_fixture):
    add_record_to_db('test')
    assert count_records() == 1

medium

A. 0

B. Raises an error

C. 1

D. Depends on previous tests

Solution

1. Step 1: Understand fixture behavior with yield

   The fixture sets up connection, yields control to test, then rolls back changes after test finishes.

2. Step 2: Analyze test effect on database

   The test adds one record and asserts count is 1 during test, but rollback removes it after test.

3. Final Answer:

   0 -> Option A

4. Quick Check:

   Rollback clears changes after test [OK]

Hint: Rollback clears test changes after test ends [OK]

Common Mistakes:

• Assuming record stays after test
• Confusing assert inside test with final state
• Thinking rollback happens before test

4. You wrote this fixture but your database changes are not rolling back after tests:

@pytest.fixture
def db_fixture():
    setup_db()
    rollback_db()
    yield

What is the main problem?

medium

A. Yield is missing, so fixture never runs

B. Setup_db should be called after yield

C. Rollback is called before yield, so changes are undone before test runs

D. Rollback_db should be called twice for safety

Solution

1. Step 1: Check order of setup, yield, and rollback

   Rollback must happen after yield to undo changes after test runs.

2. Step 2: Identify error in fixture code

   Rollback is called before yield, so changes are undone before test, not after.

3. Final Answer:

   Rollback is called before yield, so changes are undone before test runs -> Option C

4. Quick Check:

   Rollback after yield for cleanup [OK]

Hint: Rollback must be after yield to undo test changes [OK]

Common Mistakes:

• Calling rollback before yield
• Forgetting yield entirely
• Calling setup after yield

5. You want to write a pytest fixture that starts a database transaction before each test and rolls it back after, ensuring tests run fast and isolated. Which fixture code correctly achieves this behavior?

hard

A. @pytest.fixture
def db_transaction():
yield
start_transaction()
rollback_transaction()

B. @pytest.fixture
def db_transaction():
start_transaction()
yield
rollback_transaction()

C. @pytest.fixture
def db_transaction():
start_transaction()
rollback_transaction()
yield

D. @pytest.fixture
def db_transaction():
rollback_transaction()
start_transaction()
yield

Solution

1. Step 1: Understand transaction lifecycle in fixtures

   Start transaction before yield to begin test with transaction active.

2. Step 2: Rollback after yield to undo changes after test

   Rollback must happen after yield to clean up changes made during test.

3. Final Answer:

   @pytest.fixture\ndef db_transaction():\n start_transaction()\n yield\n rollback_transaction() -> Option B

4. Quick Check:

   Start before yield, rollback after yield [OK]

Hint: Start transaction before yield, rollback after yield [OK]

Common Mistakes:

• Calling rollback before yield
• Calling start_transaction after yield
• Not using yield to separate setup and cleanup