Running a comprehensive test suite during urgent situations can be time-consuming and impractical. This is where prioritizing tests based on the number of detected bugs becomes invaluable. By focusing on the most problematic areas, we can catch and address critical issues more efficiently. In this post, we'll explore leveraging bug counts to prioritize tests for smoke testing using PyTest and MySQL, streamlining our QA process, and maximizing test coverage.

Tracking Bugs in Test Cases

Whenever a test case detects a bug, it's crucial to log detailed information about it. This information should include the test case that detected the bug, the severity of the bug, its status (e.g., open, fixed, in progress), and the date it was detected. Consistent and detailed logging helps maintain a clear record of issues, making it easier to prioritize tests based on their bug counts.

To store and manage bug information, we'll create a table in MySQL. Here's an example schema for the BugTracker table:

                        
CREATE TABLE BugTracker (
    BugID INT AUTO_INCREMENT PRIMARY KEY,
    TestCaseID VARCHAR(255) NOT NULL,
    BugSeverity VARCHAR(50),
    BugStatus VARCHAR(50),
    DateDetected DATE,
    Description TEXT
);
                      

Explanation of the Columns:

• BugID: A unique identifier for each bug (auto-incremented).
• TestCaseID: The identifier for the test case that detected the bug. This links the bug to its corresponding test case.
• BugSeverity: The severity of the bug (e.g., critical, major, minor). This helps in assessing the impact of the bug.
• BugStatus: The current status of the bug (e.g., open, fixed, in-progress). This helps track the progress of bug resolution.
• DateDetected: The date when the bug was detected. This helps in understanding the timeline of issues.
• Description: A detailed description of the bug. This provides context and information necessary for debugging and fixing the issue.

Creating the Bug Count Database

First, we'll need to create a new database to store our bug count data. This database will house the BugCount table where we will log the number of bugs detected by each test case.

First, we log in to our MySQL server and create a new database named test_framework:

                        
CREATE DATABASE test_framework;
                      

Next, we need to switch to the newly created test_framework database to create the table within it.

                        
USE test_framework;
                      

Now that we are in the correct database, we can create the BugCount table. This table will store the unique identifier for each test case along with the count of bugs detected.

                        
CREATE TABLE BugCount (
    TestCaseID VARCHAR(255) PRIMARY KEY,
    BugCount INT DEFAULT 0
);
                      

Explanation of the Columns:

• TestCaseID: The unique identifier for each test case. This column serves as the primary key to ensure that each test case has only one corresponding bug count record.
• BugCount: The number of bugs detected by the test case. This column is initialized to zero and will be incremented based on the bugs logged.

Updating Bug Counts

To keep the bug counts up-to-date, we need a Python script that fetches bug data and updates the BugCount table in the MySQL database. The Python script will connect to the MySQL database, fetch the current bug data, and update the BugCount table accordingly.

First, the MySQL connector must be installed in the Python environment. This allows the script to interact with the MySQL database.

                        
pip3 install mysql-connector-python
                      

The script uses the mysql.connector library to connect to the MySQL database. It requires the MySQL credentials and server details to establish the connection.

                        
import mysql.connector

def update_bug_counts(bug_data):
    conn = mysql.connector.connect(
        user='your_user', 
        password='your_password',
        host='localhost', 
        database='test_framework'
    )
    cursor = conn.cursor()
                      

Before updating the bug counts, the script resets all bug counts to zero. This ensures that the counts reflect the current state of bugs.

                        
cursor.execute("UPDATE BugCount SET BugCount = 0")
                      

The script iterates over the bug_data list, which contains the fetched bug information. For each bug, it inserts or updates the bug count in the BugCount table. If the TestCaseID already exists, it increments the BugCount by one; otherwise, it inserts a new record.

                        
for bug in bug_data:
    test_case_id = bug['test_case_id']
    cursor.execute("""
        INSERT INTO BugCount (TestCaseID, BugCount)
        VALUES (%s, 1)
        ON DUPLICATE KEY UPDATE BugCount = BugCount + 1
    """, (test_case_id,))
                      

Finally, the script commits the changes to the database to ensure the updates are saved. It then closes the cursor and the connection to release the resources.

                        
conn.commit()
cursor.close()
conn.close()
                      

Example Bug Data:

Here is an example of the bug data that would be fetched from our bug tracking system. The script then uses this data to update the bug counts in the database.

                        
bug_data = [
    {'test_case_id': 'TC001', 'bug_id': 'BUG001'},
    {'test_case_id': 'TC001', 'bug_id': 'BUG002'},
    {'test_case_id': 'TC002', 'bug_id': 'BUG003'},
]
                            
update_bug_counts(bug_data)
                      

By periodically running this script, the BugCount table will always reflect the latest bug data, enabling efficient prioritization of tests for smoke testing.

Querying for Top Buggy Tests

The following SQL query selects the test cases from the BugCount table, ordered by the BugCount in descending order. This query retrieves the test cases with the highest number of detected bugs.

                        
SELECT TestCaseID, BugCount
FROM BugCount
ORDER BY BugCount DESC
LIMIT 10;
                      

This query fetches the top 10 test cases with the highest bug counts. Adjust the LIMIT value to retrieve a different number of test cases if needed.

Python Function to Execute the Query and Retrieve Top Test Cases:

The function starts by connecting to the MySQL database using the mysql.connector library.

                        
import mysql.connector

def get_top_buggy_tests(limit=10):
    conn = mysql.connector.connect(
        user='youruser', 
        password='yourpassword',
        host='localhost', 
        database='test_framework'
    )
    cursor = conn.cursor()
                      

The function executes the SQL query to select the test cases with the highest bug counts. The LIMIT value is parameterized to allow flexibility in the number of test cases retrieved.

                        
query = """
SELECT TestCaseID, BugCount
FROM BugCount
ORDER BY BugCount DESC
LIMIT %s;
"""
cursor.execute(query, (limit,))
results = cursor.fetchall()
                      

Finally, the function closes the database connection and returns the query results as a list of tuples.

                        
cursor.close()
conn.close()
                        
return results
                      

We can use the get_top_buggy_tests function to retrieve the top test cases with the highest bug counts. Here is an example usage:

                        
top_buggy_tests = get_top_buggy_tests(limit=10)
for test_case in top_buggy_tests:
    print(f"TestCaseID: {test_case[0]}, BugCount: {test_case[1]}")
                      

Integrating with PyTest

To leverage the prioritized test cases in our testing workflow, it's essential to integrate them with PyTest.

Step 1: Fetch the Top Test Cases

First, we use the get_top_buggy_tests function to fetch the top test cases with the highest bug counts. This function returns a list of tuples containing the TestCaseID and BugCount.

                        
top_buggy_tests = get_top_buggy_tests(limit=10)
                      

Step 2: Dynamically Create a PyTest Test Suite

Next, we create a PyTest test suite dynamically based on the fetched test cases. This involves generating a test suite that includes only the prioritized test cases.

First, the load_tests_from_ids function takes a list of test case IDs and retrieves the corresponding test functions. It assumes that test functions are named according to a specific convention (e.g., test_<TestCaseID>).

                        
def load_tests_from_ids(test_ids):
    test_functions = []
    for test_id in test_ids:
        test_function_name = f"test_{test_id}"
        test_function = globals().get(test_function_name)
        if test_function:
            test_functions.append(test_function)
    return test_functions
                      

Next, the test_dynamic function fetches the top test cases, retrieves their IDs, and loads the corresponding test functions. It then dynamically executes these test functions within the PyTest framework.

                        
def test_dynamic():
    top_buggy_tests = get_top_buggy_tests(limit=10)
    test_ids = [test_case[0] for test_case in top_buggy_tests]
    test_functions = load_tests_from_ids(test_ids)
                            
    for test_function in test_functions:
        test_function()
                      

Step 3: Execute the Dynamically Created Test Suite

To execute the dynamically created test suite, we simply run PyTest as usual. The test_dynamic function will be included in our test suite, and it will run the prioritized tests based on the bug counts.

                        
pytest path_to_your_test_file.py
                      

Automating the Process

To maintain an efficient and up-to-date testing workflow, automating the entire process is crucial. This includes regularly updating bug counts and running prioritized tests. There are several ways to achieve this automation, depending on your environment and preferences.

• Scheduled Scripts: Use operating system tools to schedule the execution of your Python scripts. For Unix-like systems, tools such as cron jobs can be used, while Windows Task Scheduler serves a similar purpose on Windows systems.
• CI/CD Pipelines: Integrate the process into your Continuous Integration/Continuous Deployment (CI/CD) pipelines. Tools like Jenkins, GitLab CI and GitHub Actions can automate the running of these scripts as part of your build and deployment workflow.
• Task Schedulers: Utilize task scheduler libraries within Python, such as schedule or APScheduler, to programmatically define and manage your automated tasks.
• Cloud-based Automation: Leverage cloud-based services like AWS Lambda, Google Cloud Functions, or Azure Functions to run your scripts on a schedule without needing to manage infrastructure.

While specific implementation details depend on the chosen method, the general structure of the automation script remains consistent:

• Fetch and Update Bug Counts
• Run Prioritized Tests

Conclusion

In this post, we've explored how to build a mechanism to count bugs in test cases and prioritize them for urgent smoke testing using PyTest and MySQL. By systematically logging and tracking bugs, creating a database to store bug counts, updating the counts dynamically, and integrating these prioritized tests into our PyTest workflow, we can significantly enhance the efficiency and effectiveness of our testing process.

Prioritizing tests based on bug counts is a powerful strategy for effective smoke testing. It ensures that our testing efforts are focused on the areas of our application that are most prone to issues, thereby increasing the likelihood of catching critical bugs early. This approach not only improves the quality of our software but also optimizes resource allocation, making our testing process more efficient.

Check out our GitHub for detailed code examples. Don't hesitate to try them out.