Python Script to Query Jira (On-Prem) and send reminder email(s)
May 15, 2020 Leave a comment
This script connects to an On-Prem Jira instance, executes a Query to retrieve QA-Ready Tickets and sends an email to the Assignee. There are 2 parts to this script. The first section has some plumbing code to wrap the results into a HTML type document that can be sent as an email. The subsequent code has the code to connect to Jira.
- Code section below Queries Jira to get a list of defects (see condition type = “Defect”)
- Some custom columns in the script below may prevent the code from executing as-is
- Use print (json.dumps(data, indent = 4)) to understand the JSON response and modify the rest of the code as needed
- Review and modify the Variables, SMTP Server details, Queries and columns to match your enviornment
- Warning :: Modify the sAssignedToEmail to a test email account in the call to SendEmail(sFrom, sAssignedToEmail, subject, body) to avoid sending emails to your user base
Review Jira documentation here
#run the following command in a Terminal window to install jira
#pip install atlassian-python-api
from atlassian import Jira
import json
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
#Call this function to wrap the Columne Header value into a HTML Table Header
def GetHtmlColumnHeader(value):
val = f"""\
<th>{value}</th>"""
return val
#Call this function to wrap the cell value into a HTML Table Cell
def GetHtmlCell(value):
val = f"""\
<td>{value}</td>"""
return val
#use double curly braces to escape style sheets.
#Variable within single curly braces will Substituted
def GetHtmlEmail(Headers, Rows):
val = f"""\
<html>
<head>
<style type='text/css'>
@font-face
{{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}}
table
{{
border: 0px;
width: 98%;
}}
th
{{
vertical-align: top;
font-family:'Calibri',sans-serif;
border:solid #4472C4 1.0pt;
border-right:none;
background:#4472C4;
padding:5.4pt 5.4pt 5.4pt 5.4pt;
color:white;
}}
th {{width: 75px;}}
th+th {{width: 300px;}}
th+th+th {{width: 120px;}}
td
{{vertical-align: top;
font-family:'Calibri',sans-serif;
color:windowtext;
font-size:11.0pt;
border:solid #8EAADB 1.0pt;padding:0in 5.4pt 0in 5.4pt;
}}
p, li, div
{{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:'Calibri',sans-serif;}}
span
{{mso-style-type:personal-compose;
font-family:'Calibri',sans-serif;
color:windowtext;
font-size:11.0pt;}}
.MsoChpDefault
{{mso-style-type:export-only;
font-size:12.0pt;
font-family:'Calibri',sans-serif;}}
@page WordSection1
{{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}}
div
{{page:WordSection1;}}
</style>
</head>
<body>
<div>
<p>
<span>
Hi,
</span>
</=
p>
<p>
<span>
<o:p> </o:p>
</span=
>
</p>
<p>
<span>
The following Tickets are in QA Ready Status. Please retest and close the Ticket, if the requested functionality is working or return it back to Development with details (description of the issue and attachments if any)
<o:p></o:p>
</span>
</p>
<p>
<span>
<o:p> </o:p>
</span>
</p>
<table>
<thead>
<tr>
{Headers}
</tr>
</thead>
<tbody>
{Rows}
</table>
<p>
<span>
<o:p> </o:p>
</span>
</p>
<p>
<span>
Thank you,
<o:p></o:p>
</span>
</p>
<p>
<span>
<o:p> </o:p>
</span>
</p>
<p>
<span>
Project Management Team
</span>
</p>
</div>
</body>
</html>"""
return val
def SendEmail(sFrom, sTo, subject, body):
#port = 2525
smtp_server = "smtp.domain.com"
message = MIMEMultipart("alternative")
message["Subject"] = subject
message["From"] = sFrom
message["To"] = sTo
shtml = MIMEText(body, "html")
message.attach(shtml)
# send your email
with smtplib.SMTP(smtp_server) as server:
server.sendmail(sFrom, sTo, message.as_string())
print('Sent')
sFrom = "sender@gmail.com"
jira = Jira(
url='https://onprem.jira.com',
username='jira.user.name',
password='jira.password')
query = r'project = "Project Name" and type = "Defect" AND status in ("In Test", "QA Ready") ORDER BY assignee'
data = jira.jql(query)
#print (json.dumps(data, indent = 4))
sHeader = getHtmlHeader("Key", "Summary", "Assigned To", "Creator", "Priority", "Status", "Comments")
sRows = ""
sLastTo = ""
for issue in data["issues"]:
if (issue["fields"]["issuetype"]["name"] == "Enhancement"):
sKey = GetHtmlColumn(issue["key"])
sCreator = issue["fields"]["creator"]["name"]
print (issue["fields"]["creator"]["emailAddress"])
print (issue["fields"]["summary"])
print (issue["fields"]["description"])
print (issue["fields"]["priority"]["name"])
print (issue["fields"]["status"]["name"])
print (issue["fields"]["created"])
if (issue["fields"]["issuetype"]["name"] == "Defect"):
sKey = issue["key"]
sCreator = issue["fields"]["creator"]["name"]
sAssignedToName = issue["fields"]["assignee"]["name"]
sAssignedToEmail = issue["fields"]["assignee"]["emailAddress"]
sSummary = issue["fields"]["summary"]
sDescription = issue["fields"]["description"]
sPriority = issue["fields"]["priority"]["name"]
sStatus = issue["fields"]["status"]["name"]
sCreationDate = issue["fields"]["created"]
intCommentCount = issue["fields"]["comment"]["total"]
sLastComment = ""
if (intCommentCount > 0):
#print (issue["fields"]["comment"]["comments"][intCommentCount - 1]["author"]["displayName"])
#print (issue["fields"]["comment"]["comments"][intCommentCount - 1]["author"]["emailAddress"])
sLastComment = issue["fields"]["comment"]["comments"][intCommentCount - 1]["body"]
#print (issue["fields"]["comment"]["comments"][intCommentCount - 1]["updated"])
print ("{},{}".format(sLastTo, sAssignedToEmail))
if (sLastTo is ""):
sLastTo = sAssignedToEmail
if (sLastTo != sAssignedToEmail):
if (sRows != ""):
subject = "ACTION REQUIRED :: Project Name - QA Ready Jira Ticket(s)"
body = GetHtmlEmail(sHeader, sRows)
#print (body)
SendEmail(sFrom, sLastTo, subject, body)
sLastTo = sAssignedToEmail
sRows = ""
sRows = sRows + getHtmlRow(sKey, sSummary, sAssignedToName, sCreator, sPriority, sStatus, sLastComment)
if (sRows is not ""):
subject = "ACTION REQUIRED :: Project Name - QA Ready Jira Ticket(s)"
body = GetHtmlEmail(sHeader, sRows)
#print (body)
SendEmail(sFrom, sAssignedToEmail, subject, body)
print ("Finished")
Trevor Benedict's Blog 