Unit testing is an essential process in software development as it helps ensure the quality and reliability of your code. While unittesting provides valuable insights into the functionality of your application, it can be beneficial to store and manage these test results in a database. This allows for better tracking, analysis, and reporting of test outcomes.

In this blog post, we will explore how to store unittest test results in a database using Python. We will make use of the unittest module for writing and executing tests, and the sqlite3 module for interacting with an SQLite database.

Setting up the Database

First, let’s set up our database. We will use SQLite, a lightweight and easy-to-use database engine.

import sqlite3

# Connect to the database
conn = sqlite3.connect('test_results.db')

# Create a cursor to execute the SQL queries
cursor = conn.cursor()

# Create a table for storing test results
cursor.execute('''
    CREATE TABLE IF NOT EXISTS test_results (
        test_name TEXT,
        result TEXT
    )
''')

# Commit the transaction and close the connection
conn.commit()
conn.close()

Here, we connect to the database using the sqlite3.connect() method and create a cursor object to execute SQL queries. We create a table named test_results with two columns: test_name to store the name of the test, and result to store the test outcome.

Storing Test Results in the Database

Now that our database is set up, let’s modify our unit tests to store their results in the database.

import unittest
import sqlite3

class MyTestCase(unittest.TestCase):
    def setUp(self):
        # Connect to the database
        self.conn = sqlite3.connect('test_results.db')
        self.cursor = self.conn.cursor()

    def tearDown(self):
        # Close the database connection
        self.conn.close()

    def test_example(self):
        # Run the test case
        result = 2 + 2

        # Store the test result in the database
        self.cursor.execute('''
            INSERT INTO test_results (test_name, result)
            VALUES (?, ?)
        ''', (self._testMethodName, str(result)))

        # Commit the transaction
        self.conn.commit()

if __name__ == '__main__':
    unittest.main()

Here, we have a simple example test case that calculates the sum of 2 and 2. We open a connection to the database in the setUp() method and create a cursor object to execute SQL queries. After running the test, we use self._testMethodName to get the name of the test and store it along with the test result in the test_results table using an SQL INSERT statement. Finally, we commit the transaction and close the database connection in the tearDown() method.

By modifying your unittest test cases in a similar fashion, you can store test results for all your tests in the database.

Retrieving Test Results from the Database

To retrieve test results from the database, you can use SQL SELECT statements. Here’s an example:

import sqlite3

# Connect to the database
conn = sqlite3.connect('test_results.db')
cursor = conn.cursor()

# Execute a SELECT statement to retrieve all test results
cursor.execute('SELECT * FROM test_results')

# Fetch all rows
rows = cursor.fetchall()

# Print the test results
for row in rows:
    print(f"Test name: {row[0]}, Result: {row[1]}")

# Close the database connection
conn.close()

In this example, we connect to the database and execute a SELECT statement to fetch all rows from the test_results table. We then iterate over the rows and print the test name and result.

By using similar SQL queries, you can retrieve and analyze your test results in various ways.

Conclusion

Storing unittest test results in a database can be a useful approach for managing and analyzing test outcomes. In this blog post, we explored how to store test results in an SQLite database using Python. We also saw how to retrieve and print test results from the database.

By leveraging the power of databases, you can enhance your testing process and gain valuable insights into the quality of your codebase.