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.

Practice

1. What is the main purpose of using database fixtures in pytest?

easy

A. To speed up the database server

B. To write SQL queries inside test functions

C. To prepare and clean test data automatically before and after tests

D. To replace the need for assertions in tests

Solution

1. Step 1: Understand what fixtures do

   Fixtures in pytest are used to set up and tear down resources needed for tests, such as database data.

2. Step 2: Identify the role of database fixtures

   Database fixtures specifically prepare test data before tests run and clean it up after tests finish, ensuring tests run reliably.

3. Final Answer:

   To prepare and clean test data automatically before and after tests -> Option C

4. Quick Check:

   Database fixtures = setup and cleanup [OK]

Hint: Fixtures handle setup and cleanup automatically [OK]

Common Mistakes:

• Thinking fixtures run SQL queries inside tests
• Believing fixtures speed up the database server
• Confusing fixtures with assertions

2. Which of the following is the correct way to write a pytest fixture that sets up a database connection and tears it down after the test using yield?

easy

A. def db():
conn = connect()
yield conn
conn.close()

B. def db():
conn = connect()
conn.close()
yield conn

C. def db():
yield connect()
conn.close()

D. def db():
conn = connect()
return conn
conn.close()

Solution

1. Step 1: Understand yield usage in fixtures

   Using yield in a fixture splits setup (before yield) and teardown (after yield).

2. Step 2: Check each option's order

   def db(): conn = connect() yield conn conn.close() sets up connection, yields it, then closes connection after test. Others close before yield or have unreachable code.

3. Final Answer:

   def db():\n conn = connect()\n yield conn\n conn.close() -> Option A

4. Quick Check:

   Setup before yield, teardown after yield [OK]

Hint: Yield separates setup and teardown in fixtures [OK]

Common Mistakes:

• Closing connection before yield
• Placing code after return (unreachable)
• Yielding before setup

3. Given the following pytest fixture and test, what will be printed when the test runs?

import pytest

@pytest.fixture
def sample_db():
    data = {'count': 0}
    yield data
    data['count'] += 1


def test_increment(sample_db):
    print(sample_db['count'])
    sample_db['count'] += 5
    print(sample_db['count'])

medium

A. 1\n6

B. 0\n5

C. 0\n0

D. 5\n10

Solution

1. Step 1: Analyze fixture setup and teardown

   The fixture yields data with 'count' 0. After test, it increments 'count' by 1 (not affecting test output).

2. Step 2: Trace test function prints

   First print shows initial 0. Then 'count' is increased by 5, so second print shows 5.

3. Final Answer:

   0\n5 -> Option B

4. Quick Check:

   Yielded data count = 0, incremented in test = 5 [OK]

Hint: Yield returns setup data; teardown runs after test [OK]

Common Mistakes:

• Thinking teardown runs before test prints
• Assuming fixture modifies data before yield
• Confusing fixture teardown with test code

4. Identify the error in this pytest fixture that is supposed to setup a test database and clean it after tests:

@pytest.fixture
def test_db():
    conn = connect_db()
    conn.execute('CREATE TABLE users')
    return conn
    conn.execute('DROP TABLE users')
    conn.close()

medium

A. The cleanup code after return is never executed

B. The fixture should use yield instead of return for cleanup

C. The table creation SQL is incorrect

D. The fixture is missing the @pytest.mark decorator

Solution

1. Step 1: Check the fixture structure

   Code after return statement is unreachable and will never run.

2. Step 2: Understand cleanup execution

   Cleanup code must run after test, so it should be placed after yield or before return, but not after return.

3. Final Answer:

   The cleanup code after return is never executed -> Option A

4. Quick Check:

   Code after return is unreachable [OK]

Hint: Code after return in fixture won't run [OK]

Common Mistakes:

• Thinking return allows cleanup after it
• Confusing yield and return usage
• Ignoring unreachable code warnings

5. You want to create a pytest fixture that sets up a test database with multiple tables and ensures all tables are dropped after tests, even if a test fails. Which pattern best achieves this?

hard

A. Create tables once globally without cleanup to speed up tests

B. Create tables inside each test and drop them at the end of each test

C. Use return in fixture to return connection, then drop tables in a separate teardown function

D. Use a fixture with yield: create tables before yield, drop tables after yield

Solution

1. Step 1: Understand reliable setup and teardown

   Using yield in fixtures allows setup before tests and guaranteed cleanup after, even if tests fail.

2. Step 2: Evaluate options for cleanup guarantee

   Use a fixture with yield: create tables before yield, drop tables after yield uses yield to create tables before tests and drop them after, ensuring cleanup always runs.

3. Final Answer:

   Use a fixture with yield: create tables before yield, drop tables after yield -> Option D

4. Quick Check:

   Yield fixture ensures setup and guaranteed teardown [OK]

Hint: Yield fixtures guarantee cleanup after tests [OK]

Common Mistakes:

• Skipping cleanup causing leftover tables
• Relying on test code for cleanup
• Avoiding yield and missing teardown