Join 34,000+ subscribers and receive articles from our
blog about software quality, testing, QA and security.

Do a mass modification of tests statuses through TestRail's DB


#1

Hello Gurock team,

We would like to change the status of many tests at once. Doing it through the GUI would take ages, so we would like to use an SQL query to do this.

This is not something we intend to do on a regular basis, as we just want to fix erroneous tests statuses set by some of our users. They are now aware of the correct status they must use so we shouldn’t have to fix this again in the near future.

Here is what we plan to issue on the DB, “60” being the id of our project in the “projects” table:

UPDATE tests t
INNER JOIN runs r
ON r.id=t.run_id
INNER JOIN projects p
ON p.id=r.project_id
SET t.status_id=4
WHERE (p.id=60 AND r.name like “Our_Run_Name”);

We’ve already done this on our TEST instance, and it seems to work well.

Do you think we can do this safely on PROD ?

Regards,

Bernard


#2

Hi Bernard,

Thank you for your post. We would not normally recommend querying the database directly when possible. Instead we would recommend using the TestRail API as this would ensure that any other processes that need to be carried out during the update are in fact carried out by TestRail. You can find more information on our API here:

http://docs.gurock.com/testrail-api2/start


#3

Hi Marty,

Thanks for your reply. As per your suggestion, I’ve made some researches regarding the API, and wrote this script.
Can you please tell me if it’s a proper way to update the statuses of all the tests related to the projects my account can open ?

#!/usr/bin/env python3

#"""
#This script sets the desired result on all tests related to the selected project(s)
#"""

from testrail import *
import argparse
import json
import sys


parser = argparse.ArgumentParser()
parser.add_argument("--project","-p",help="TestRail project id")
args = parser.parse_args()

client = APIClient('https://OurTestRailInstance')
client.user = 'my admin user'
client.password = 'my password'

def showtests( project ):
  project_id=json.dumps(project["id"])
  runs = client.send_get('get_runs/{}'.format(project_id))
  for run in runs:
    run_id=json.dumps(run["id"])
    tests = client.send_get('get_tests/{}'.format(run_id))
    for test in tests:
      test_id=json.dumps(test["id"])
      if json.dumps(test["status_id"])=='9':
        print ("----------------To Move-------------------")
        resultmove=client.send_post('add_result/{}'.format(test_id),{ 'status_id': 2 })
        print(resultmove)
  return;

  if args.project is None:
  projects = client.send_get('get_projects/')
  for project in projects:
    showtests( project )
else:
  project = client.send_get('get_project/{}'.format(args.project))
  showtests( project )

I’m planning to run this script towards all the projects for which my account has been granted an access on.

Also, as suggested in other topics, it would be great for application admins like me to have the possibility to easily grant or remove full access to all projects to their account, using another way than going on each project manually though the UI.

Regards,

Bernard


#4

Hi Gurock team and the Community,

Could you provide me with some feedback regarding the above script ? :relaxed:

I hope not harming anything in the DB using it.

Regards,

Bernard


#5

Hi Bernard ,

Thank you for the post. Being that you are running this via API, you should not have any issues with changing data. I would of course recommend running backup of TestRail before running this just in case you end up with unwanted results.