Database fixture patterns in PyTest - Build an Automation Script

Test user creation and retrieval using database fixture

Preconditions (3)

Step 1: Use a pytest fixture to connect to the database and start a transaction

Step 2: Insert a user with username 'testuser' and email '[e-mail]' into the 'users' table

Step 3: Query the 'users' table to retrieve the inserted user by username

Step 4: Verify the retrieved user's email matches '[e-mail]'

Step 5: Rollback the transaction to leave the database unchanged

✅ Expected Result: The inserted user is found with the correct email, and the database remains unchanged after the test

Automation Requirements - pytest

Assertions Needed:

Assert that the retrieved user's email equals '[e-mail]'

Best Practices:

Use pytest fixtures to manage database connection and transaction lifecycle

Use explicit commit or rollback to keep database clean after tests

Use parameterized queries to avoid SQL injection

Keep test data isolated and cleaned up after test

Automated Solution

PyTest

import pytest
import psycopg2
from psycopg2.extras import RealDictCursor

@pytest.fixture(scope='function')
def db_connection():
    conn = psycopg2.connect(
        dbname='testdb', user='testuser', password='testpass', host='localhost'
    )
    conn.autocommit = False
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    yield cursor
    conn.rollback()
    cursor.close()
    conn.close()

def test_insert_and_retrieve_user(db_connection):
    cursor = db_connection
    insert_query = """
        INSERT INTO users (username, email) VALUES (%s, %s)
    """
    cursor.execute(insert_query, ('testuser', '[e-mail]'))

    select_query = """
        SELECT username, email FROM users WHERE username = %s
    """
    cursor.execute(select_query, ('testuser',))
    user = cursor.fetchone()

    assert user is not None, "User should be found in database"
    assert user['email'] == '[e-mail]', f"Expected email '[e-mail]', got {user['email']}"

The db_connection fixture creates a database connection and cursor with a transaction started. It yields the cursor to the test, then rolls back any changes to keep the database clean.

The test test_insert_and_retrieve_user uses this fixture to insert a user with parameterized queries to avoid SQL injection. Then it queries the user back and asserts the email matches the expected value.

Using a fixture for connection management and rollback ensures tests do not affect each other or the real data, following best practices for database testing.

Common Mistakes - 4 Pitfalls

Not rolling back or cleaning up database changes after test

Hardcoding SQL queries with string concatenation

Opening and closing database connections inside each test

Using global or module-level fixtures with shared state

Bonus Challenge

Now add data-driven testing with 3 different user inputs for username and email

Show Hint