Use pytest fixture to mock a database connection in Python

This code shows how to use a pytest fixture and unittest.mock to replace a database connection with a Mock, enabling isolated tests without a real database.

Medium Python 3.9+ Aug 9, 2026 Testing & modern typing 19 views 0 copies

pytest fixtures unittest.mock sqlite testing mocking
Requires third-party packages — install first
pip install pytest

Python code

32 lines
Python 3.9+ 
import pytest
import sqlite3
from unittest.mock import Mock

class Database:
    def __init__(self, connection):
        self.connection = connection

    def get_user(self, user_id):
        cursor = self.connection.cursor()
        cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
        return cursor.fetchone()

@pytest.fixture
def mock_db():
    """Fixture that returns a Database with a mocked connection."""
    mock_conn = Mock()
    mock_cursor = Mock()
    mock_conn.cursor.return_value = mock_cursor
    mock_cursor.fetchone.return_value = (1, "Alice", "alice@example.com")
    return Database(mock_conn)

def test_get_user(mock_db):
    user = mock_db.get_user(1)
    assert user == (1, "Alice", "alice@example.com")
    mock_db.connection.cursor.assert_called_once()
    mock_db.connection.cursor().execute.assert_called_once_with(
        "SELECT * FROM users WHERE id = ?", (1,)
    )

if __name__ == "__main__":
    pytest.main([__file__, "-v"])

Output

stdout 
============================= test session starts ==============================
platform darwin -- Python 3.9.13, pytest-8.0.2, pluggy-1.4.0
rootdir: /path/to/your/project
plugins: anyio-4.2.0
collected 1 item

test_database.py .                                                            [100%]

============================== 1 passed in 0.02s =================================

How it works

The @pytest.fixture decorator marks mock_db as a fixture, which runs before each test that receives it as an argument. Inside the fixture, the Mock instance for the connection returns a mocked cursor, and fetchone is preconfigured to return a sample user tuple. The test passes because it uses the same mock objects to verify both the returned value and that SQL was executed with the expected parameters. Mocking the database keeps tests fast, deterministic, and free from real data dependencies.

Common mistakes

  • Forgetting to patch `cursor()` to return the same mock cursor, causing `fetchone` to be undetermined.
  • Having the fixture yield instead of return when no teardown is needed, adding unnecessary complexity.
  • Asserting interactions on the real cursor instead of the mock cursor's methods.
  • Not using `assert_called_once_with` when you need to verify both call count and arguments.

Variations

  1. Use `monkeypatch` to replace a module-level database factory that the class calls.
  2. Use `unittest.mock.patch` inside the fixture to patch a global connection object.

Real-world use cases

  • Isolating unit tests for repository functions that query user data without setting up a test database.
  • Speed up CI pipelines by removing slow database integration steps for pure logic tests.
  • Verifying SQL statements and parameters passed from domain code to the database layer.

Sponsored

Run locally

This sample needs third-party packages, so it cannot run in the browser IDE. Copy the code above, install the packages shown at the top, then run it in your own Python environment.

More from Testing & modern typing

Keep learning

Related tutorials and quizzes for this topic.

Tutorials

Quizzes