Many programs need to access a database for their operation, which means that many of the units these programs are made of also access a database. The point is that the purpose of a database is to store information and make it accessible in other, arbitrary places; in other words, databases exist to break the isolation of units. The same problem applies to other information stores as well: for example, files in permanent storage.

How do we deal with that? After all, just leaving the units that interact with the database untested is no solution. We need to create an environment where the database connection works as usual, but where any changes that are made do not last. There are a few different ways in which we can do this but, no matter what the details are, we need to set up the special database connection before each test that uses it, and we need to destroy any changes after each such test.

The unittest helps us do this by providing test fixtures via the setUp and tearDown methods of the TestCase class. These methods exist for us to override, with the default versions doing nothing.

Here's some database-using code (let's say it exists in a file called employees.py), for which we're going to write tests:

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

    def add_employee(self, first, last, date_of_employment):
        cursor = self.connection.cursor()
        cursor.execute('''insert into employees
                            (first, last, date_of_employment)
                          values
                            (:first, :last, :date_of_employment)''',
                       locals())
        self.connection.commit()

        return cursor.lastrowid

    def find_employees_by_name(self, first, last):
        cursor = self.connection.cursor()
        cursor.execute('''select * from employees
                          where
                            first like :first
                          and
                            last like :last''',
                       locals())

        for row in cursor:
            yield row

    def find_employees_by_date(self, date):
        cursor = self.connection.cursor()
        cursor.execute('''select * from employees
                          where date_of_employment = :date''',
                       locals())

        for row in cursor:
            yield row

We'll start off by importing the needed modules and introducing our TestCase subclass:

from unittest import TestCase
from sqlite3 import connect, PARSE_DECLTYPES
from datetime import date
from employees import Employees

class test_employees(TestCase):

We need a setUp method to create the environment that our tests depend on. In this case, that means creating a new database connection to an in-memory-only database, and populating that database with the needed tables and rows:

    def setUp(self):
        connection = connect(':memory:',
                             detect_types = PARSE_DECLTYPES)
        cursor = connection.cursor()

        cursor.execute('''create table employees
                            (first text,
                             last text,
                             date_of_employment date)''')

        cursor.execute('''insert into employees
                            (first, last, date_of_employment)
                          values
                            ("Test1", "Employee", :date)''',
                       {'date': date(year = 2003,
                                     month = 7,
                                     day = 12)})

        cursor.execute('''insert into employees
                            (first, last, date_of_employment)
                          values
                            ("Test2", "Employee", :date)''',
                       {'date': date(year = 2001,
                                     month = 3,
                                     day = 18)})

        self.connection = connection

We need a tearDown method to undo whatever the setUp method did, so that each test can run in an untouched version of the environment. Since the database is only in memory, all we have to do is close the connection, and it goes away. The tearDown method may end up being much more complicated in other scenarios:

    def tearDown(self):
        self.connection.close()

Finally, we need the tests themselves:

    def test_add_employee(self):
        to_test = Employees(self.connection)
        to_test.add_employee('Test1', 'Employee', date.today())

        cursor = self.connection.cursor()
        cursor.execute('''select * from employees
                          order by date_of_employment''')

        self.assertEqual(tuple(cursor),
                         (('Test2', 'Employee', date(year = 2001,
                                                     month = 3,
                                                     day = 18)),
                          ('Test1', 'Employee', date(year = 2003,
                                                     month = 7,
                                                     day = 12)),
                          ('Test1', 'Employee', date.today())))

    def test_find_employees_by_name(self):
        to_test = Employees(self.connection)

        found = tuple(to_test.find_employees_by_name('Test1', 'Employee'))
        expected = (('Test1', 'Employee', date(year = 2003,
                                               month = 7,
                                               day = 12)),)

        self.assertEqual(found, expected)

    def test_find_employee_by_date(self):
        to_test = Employees(self.connection)

        target = date(year = 2001, month = 3, day = 18)
        found = tuple(to_test.find_employees_by_date(target))

        expected = (('Test2', 'Employee', target),)

        self.assertEqual(found, expected)

We just used a setUp method in our TestCase, along with a matching tearDown method. Between them, these methods made sure that the environment in which the tests were executed was the one they needed (that was setUp's job) and that the environment of each test was cleaned up after the test was run, so that the tests didn't interfere with each other (this was the job of tearDown). The unittest made sure that setUp was run once before each test method, and that tearDown was run once after each test method.

Because a test fixture—as defined by setUp and tearDown—gets wrapped around every test in a TestCase class, the setUp and tearDown methods for the TestCase classes that contain too many tests can get very complicated and waste a lot of time dealing with details that are unnecessary for some of the tests. You can avoid this problem by simply grouping together those tests that require specific aspects of the environment into their own TestCase classes. Give each TestCase an appropriate setUp and tearDown, only dealing with those aspects of the environment that are necessary for the tests it contains. You can have as many TestCase classes as you want, so there's no need to skimp on them when you're deciding which tests to group together.

Notice how simple the tearDown method we used was. That's usually a good sign: when the changes that need to be undone in the tearDown method are simple to describe, it often means that you can be sure of doing this perfectly. Since any imperfection of the tearDown method makes it possible for the tests to leave behind stray data that might alter how other tests behave, getting it right is important. In this case, all of our changes were confined inside the database, so getting rid of the database does the trick.

We could have used a mock object for the database connection, instead. There's nothing wrong with that approach, except that, in this case, it would have been more effort for us. Sometimes mock objects are the perfect tool for the job, sometimes test fixtures save effort; sometimes you need both to get the job done easily.