Python Mock Unittest Asserting Mysql Resultsets

Question

I am having a really hard time wrapping my head around using Mock to do non-trivial unit tests in Python. I do not want to use the production databases for my test suits and want to create a testing-only database.

Let's say I want to create a mock database with these 3 tables containing this data:

FRUIT
id      Name
1       Apple
2       Pear
3       Orange

 COLOR
 id     Name
 1      Red
 2      Yellow
 3      Orange

 FRUIT_COLOR
 id     fruit_id    color_id
 1      1            1
 2      1            2
 3      2            2
 4      3            3

Now let's say I have the following function:

def assess_fruit(self):
    query = """
        SELECT 
        f.name AS FRUIT,
        c.name AS COLOR
        FROM fruit_color AS fc
        JOIN fruit AS f ON f.id = fc.fruit_id
        JOIN color AS c ON c.id = fc.color_id
        WHERE color.name = 'Red';
     """
    cursor.execute(self._query)
    results = cursor.fetchall()
    return results

How do I got about using Mock to make this database and test that the results should be something like this list of tuples?

 [(Apple, Red), (Apple, Yellow)]

Thank you in advance.

Answer 1

Python unit test TestCase class has 2 methods setUp and tearDown which are called when your test cases starts and ends. You can add code to CREATE the tables and INSERT data into the tables in these 2 methods. See example below:

from unittest import TestCase


class TestMyData(TestCase):

    def setUp(self):
        query = """
        CREATE TABLE FRUIT (
            id INT,
            Name VARCHAR(255)
        );
        INSERT INTO FRUIT (id, Name)
        VALUES (1, 'Apple'),
               (2, 'Pear'),
               (3, 'Orange');
        """
        cursor.execute(query)

        # Add CREATE & INSERT table statements for COLOR and FRUIT_COLOR tables

    def assess_fruit(self):
        query = """
            SELECT
            f.name AS FRUIT,
            c.name AS COLOR
            FROM fruit_color AS fc
            JOIN fruit AS f ON f.id = fc.fruit_id
            JOIN color AS c ON c.id = fc.color_id
            WHERE color.name = 'Red';
         """
        cursor.execute(query)
        results = cursor.fetchall()
        return results

    def tearDown(self):
        query = """
        DROP TABLE FRUIT;
        DROP TABLE COLOR;
        DROP TABLE FRUIT_COLOR;
        """
        cursor.execute(query)

If you would like to learn how to actually mock your calls to the database then you can take a look at this blog post I wrote not too long ago.