Output the report as PDF from DB with Python and automatically attach it to an email and send it

Part-san checks the operating status of communication equipment on a dedicated screen every day, creates a report, and sends it by e-mail. I think that it is quite common for any company to do similar work, but from an engineer's point of view, such a routine is impossible, so I wrote a little tool.

Way of thinking

We know that all the underlying information is in the database. Therefore, you can implement a program ** that attaches a PDF file generated from the database to an email and sends it ** on the server side, and register it in cron as well.

Execution environment

• Ubuntu 16.04
• MySQL 5.7
• Python 3.5

How to generate a form

I thought about outputting to an Excel file with Python's ** OpenPyXL ** module, [Create resume format PDF with python + reportlab] Inspired by this article, I decided to output a PDF file with the ** ReportLab ** module.

Installation

pip3 install reportlab

Form sample

Code sample

There are various ways to connect from Python to MySQL, but I happened to have ** MySQLdb ** on the server, so I used it. I will post it without much modification, but I think the scheme is reasonably versatile, so please refer to it.

Python

"""
Generate a report in PDF format from the database and send it as an attachment to an email
"""
__author__  = "MindWood"
__version__ = "1.00"
__date__    = "31 Oct 2019"

import MySQLdb
from reportlab.pdfgen import canvas
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.cidfonts import UnicodeCIDFont
from reportlab.lib import colors
from reportlab.lib.pagesizes import A4, portrait
from reportlab.lib.units import mm
from reportlab.platypus import Table, TableStyle
import smtplib
from email import encoders
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
import os
import datetime

def setup_page():
    """Register the font and set the header and footer"""
    pdfmetrics.registerFont(UnicodeCIDFont(font_name))  #Font registration
    #Header drawing
    c.setFont(font_name, 18)
    c.drawString(10*mm, height - 15*mm, "Communication equipment operation status report")
    c.setFont(font_name, 9)
    c.drawString(width - 58*mm, height - 10*mm, header_date)
    #Footer drawing
    c.drawString(10*mm, 16*mm, "The terminal ID that has received data within the last 8 hours is displayed in green.")
    global page_count
    c.drawString(width - 15*mm, 5*mm, "{}page".format(page_count))  #Draw page number
    page_count += 1

def control_break():
    """Control break with customer name"""
    if ctrl_break_key == "": return
    c.showPage()
    setup_page()

def page_break(n):
    """Page break processing"""
    n += 1
    if n < 28: return n
    c.showPage()
    setup_page()
    return 0

#Setting the base date and time
dt = datetime.datetime.now()
header_date = "{:%Y year%-m month%-d day%-H o'clock%-M minutes now}".format(dt)
safe_date = "{:%Y/%m/%d %H:%M}".format(dt + datetime.timedelta(hours=-8))  #8 hours ago

#PDF file initialization
pdf_filename = "report{:%y%m}.pdf".format(dt)
c = canvas.Canvas(pdf_filename, pagesize=portrait(A4))  #PDF file name and paper size
width, height = A4  #Get paper size
c.setAuthor("MindWood")
c.setTitle("IoT gateway Working report")
c.setSubject("")

font_name = "HeiseiKakuGo-W5"  #Font name
page_count = customer_no = 1
setup_page()
ctrl_break_key = ""

#Connect to MySQL
conn = MySQLdb.connect(host="localhost", user="xxxxxx", passwd="yyyyyy", db="zzzzzz", charset="utf8")
cursor = conn.cursor(MySQLdb.cursors.DictCursor)

#Acquisition of master information of communication equipment
cursor.execute('''
SELECT c.Name as CustName, d.Name as DeptName, a.Code, a.Area, hex(a.MacAddress) as MacAddress
FROM table0001 a
LEFT JOIN table0002 c ON a.CustomerID = c.CustomerID
LEFT JOIN table0003 d ON a.CustomerID = d.CustomerID AND a.DeptID = d.DeptID
ORDER BY c.CustomerID, d.DeptID, MacAddress;
''')

gws = cursor.fetchall()
for row_gw in gws:
    #Page break when customer name changes
    if ctrl_break_key != row_gw["CustName"]:
        control_break()
        ctrl_break_key = row_gw["CustName"]
        c.setFont(font_name, 15)
        c.drawString(10*mm, height - 36*mm, "{}. {}".format(customer_no, ctrl_break_key))
        customer_no += 1

        data = [ [ "Department name", "Management code", "Installation area", "MAC address" ] ]  #Communication equipment heading
        table = Table(data, colWidths=(70*mm, 40*mm, 40*mm, 40*mm), rowHeights=8*mm)  #Creating a table
        table.setStyle(TableStyle([
            ("FONT", (0, 0), (-1, -1), font_name, 11),            #font
            ("BOX", (0, 0), (-1, -1), 1, colors.black),           #Outer ruled line
            ("INNERGRID", (0, 0), (-1, -1), 0.25, colors.black),  #Inner ruled line
            ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),               #Align characters vertically in the center
            ("BACKGROUND", (0, 0), (-1, -1), colors.lightgrey),   #Fill with gray
        ]))
        table.wrapOn(c, 10*mm, height - 50*mm)  #Table position
        table.drawOn(c, 10*mm, height - 50*mm)  #Table position
        line_count = 1

    styles = [
        ("FONT", (0, 0), (-1, -1), font_name, 11),
        ("BOX", (0, 0), (-1, -1), 1, colors.black),
        ("INNERGRID", (0, 0), (-1, -1), 0.25, colors.black),
        ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
    ]
    MacAddress = row_gw["MacAddress"]
    if os.uname()[1] == "ip-172-35-10-XX":  #For a specific server
        MacAddress = "XXXXXXXXXXXX"
        styles.append(("BACKGROUND", (3, 0), (3, 0), colors.yellow))  #Fill with yellow

    data = [ [ row_gw["DeptName"], row_gw["Code"], row_gw["Area"], MacAddress ] ]  #Detailed data of communication equipment
    table = Table(data, colWidths=(70*mm, 40*mm, 40*mm, 40*mm), rowHeights=8*mm)
    table.setStyle(TableStyle(styles))
    table.wrapOn(c, 10*mm, height - 50*mm - 8*mm * line_count)
    table.drawOn(c, 10*mm, height - 50*mm - 8*mm * line_count)
    line_count = page_break(line_count)

    #Acquisition of data reception date and time
    cursor.execute('''
    SELECT hex(TermID) as TermID, from_unixtime(min(Time),"%Y/%m/%d %H:%i:%S") as from_date, from_unixtime(max(Time),"%Y/%m/%d %H:%i:%S") as to_date FROM table0005
    WHERE MacAddress=0x{} GROUP BY TermID ORDER BY to_date;
    '''.format(MacAddress))

    terms = cursor.fetchall()
    for row_term in terms:
        data = [ [ "period", row_term["from_date"] + " ～ " + row_term["to_date"], "Terminal ID", row_term["TermID"] ] ]  #Detailed data of the terminal
        table = Table(data, colWidths=(25*mm, 100*mm, 25*mm, 40*mm), rowHeights=8*mm)
        styles = [
            ("FONT", (0, 0), (-1, -1), font_name, 11),
            ("BOX", (0, 0), (-1, -1), 1, colors.black),
            ("INNERGRID", (0, 0), (-1, -1), 0.25, colors.black),
            ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
            ("BACKGROUND", (0, 0), (0, 0), colors.lightgrey),
            ("BACKGROUND", (2, 0), (2, 0), colors.lightgrey),
        ]
        if row_term["to_date"] > safe_date:  #If the last reception date and time is within the last 8 hours
            styles.append(("BACKGROUND", (3, 0), (3, 0), colors.palegreen))
        table.setStyle(TableStyle(styles))
        table.wrapOn(c, 10*mm, height - 50*mm - 8*mm*line_count)
        table.drawOn(c, 10*mm, height - 50*mm - 8*mm*line_count)
        line_count = page_break(line_count)

#MySQL disconnect
cursor.close()
conn.close()

#Save to PDF file
c.showPage()
c.save()

#Send PDF file by email
from_addr = "[email protected]"
to_addr   = "[email protected]"
bcc_addr  = "[email protected]"  #Multiple can be specified separated by commas
rcpt = bcc_addr.split(",") + [to_addr]

msg = MIMEMultipart()
msg["Subject"] = "Communication equipment operation status report{:%Y year%-m month}".format(dt)
msg["From"] = from_addr
msg["To"] = to_addr

msg.attach(MIMEText("""
This email is automatically sent from the system.
The system operation status of the XXXX server is attached.
""".strip()))

attachment = MIMEBase("application", "pdf")
file = open(pdf_filename, "rb+")
attachment.set_payload(file.read())
file.close()
encoders.encode_base64(attachment)
attachment.add_header("Content-Disposition", "attachment", filename=pdf_filename)
msg.attach(attachment)

smtp = smtplib.SMTP("smtp.xxxxxx.com", 587)  #SMTP server
smtp.starttls()
smtp.login(from_addr, "PASSWORD")  #password
smtp.sendmail(from_addr, rcpt, msg.as_string())
smtp.close()