Use Python to generate automated reports (Excel) for mail delivery

Data analysts must be bombarded every day with reports of their bosses, operations, products, etc. Since most reports are repetitive, this article will help you learn how to automate reporting in Python, freeing up your labor and freeing up your time to do more interesting things.

Let’s start with the Python library to implement automatic reporting:

· Pymysql is a library that can connect to MySQL instances and implement add, delete, change and check functions

· DateTime A time library built into the Python standard library

· OpenPyXL a library that can read and write Excel documents after version 07 (.xlsx format is also supported)

· SMTPLIB SMTP stands for simple Mail Transfer Protocol. Python is simply packaged as a library

· Email A library for processing email messages

Why use the OpenPyXL library for Excel? Because it supports 100W+ lines per sheet, it also supports files in XLSX format. If you accept XLS files with less than 6W lines per sheet, you can also use the XLWT library, which reads larger files faster than OpenPyXL.

Now we’re going to go into the field, and actually implement the process. I divided the whole implementation process into several functions to achieve the way, so that there will be a sense of structure.

First, import all the libraries to be used

# encoding=utf-8
import pymysql as pms
import openpyxl
import datetime
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
import smtplib
Copy the code

Get_datas (SQL); get_datas(SQL);

def get_datas(sql): Conn = PMS. Connect (host=' id ', user=' id ', passwd=' id ', database=' id ', port=3306, Cursor (); char ="utf8"); char ="utf8"; char ="utf8"; char ="utf8"; char ="utf8"; char ="utf8"; char ="utf8"; char ="utf8"; char ="utf8"; char ="utf8"; char ="utf8"; char ="utf8"; char ="utf8"; char ="utf8" Fetchall () # cur.close() # return datasCopy the code

SQL > create a function get_datas(SQL) that returns the name of the column in which the data is stored.

def get_fields(sql): Conn = PMS. Connect (host=' id ', user=' id ', passwd=' id ', database= 'id ', port=3306, Charset ="utf8") cur = conn.cursor() cur.execute(SQL) # Fields = cur.description cur.close() return fieldsCopy the code

Et_excel (data, field, file); et_excel(data, field, file)

def get_excel(data, field, file): # Create a new Workbook object new = OpenPyxl.workbook () # Activate a new sheet sheet = new.active # Rename sheet sheet.title = 'Data display' For col in range(len(field)); for col in range(len(field)); #row represents the number of rows, column represents the number of columns, and value represents the value entered by the cell. The number of rows and columns starts at 1, _ = sheet.cell(row=1, column=col+1, Value =u'%s' % field[col][0]) for row in range(len(data)): _ = sheet.cell(row=row+2, column=col + 1, value=u'%s' % data[row][col]) This step is essential newworkbook = new.save(file) # return the generated Excel Return NewworkbookCopy the code

GetYesterday (); getYesterday(); getYesterday();

def getYesterday(): Oneday = datetime.timedelta(days=1) # yesterday = today minus oneday Yesterdaystr = yesterday.strftime('%Y-%m-%d') # yesterdaystr = yesterday.strftime('%Y-%m-%d'Copy the code

Create_email (email_from, email_to, email_Subject, email_text, annex_path, annex_name)

def create_email(email_from, email_to, email_Subject, email_text, annex_path, annex_name): # Enter sender's nickname, recipient's nickname, subject, body, Message = MIMEMultipart() # Attach (MIMEText(email_text,)) Message ['From'] = Header(email_from, Message ['To'] = Header(email_to, Message ['Subject'] = Header(email_Subject, 'UTF-8 ') att1 = MIMEText(open(Annex_path, 'rb').read(), 'base64', Att1 [" content-type "] = 'application/octet-stream' att1[" content-disposition "] = 'attachment; Filename =' + annex_name # attach(att1) # return messageCopy the code

Send_email (sender, password, receiver, MSG)

Def send_email(sender, password, receiver, MSG): send_email(sender, password, receiver, MSG) # find the server address from which you sent your email. Send encrypted form server = smtplib.SMTP_SSL("smtp.mxhichina.com", 465) # SMTP server server.ehlo() # Sendmail (sender, receiver, sender) Msg.as_string ()) # print(" email sent successfully ") server.quit() # close connection except Exception: Print (traceback.print_exc()) print(" Failed to send email ")Copy the code

Create a main function, enter all the custom content, and execute the main function

def main(): Print (datetime.datetime.now()) my_sql = SQL = "SELECT a.id ',\ a.gmtcreate ',\ af. LastLoginTime ',\ af Af. TotalBuyCount 'total paid ', af. PaidmountUSD' total paid ', af. LastPayTime 'last paid time '\ FROM table a\ LEFT JOIN tableb af ON a.id= af.accountId ;" Yesterdaystr = getYesterday() # File name yesterdaystr = getYesterday() # file name My_file_name = 'user attribute' + yesterdaystr + '.xlsx' # file_path = 'D:/work/report/' + my_file_name # Generates Excel get_excel(my_data, my_field, File_path) my_email_from = 'BI department automatic reporting robot 'my_email_to =' operations' # mail title my_email_Subject = 'user' + yesterdaystr # mail body My_email_text = "Dear all,\n\t the attachment is weekly data, please check! \n\nBI team "# Attachment address my_annex_path = file_path # Attachment name my_annex_name = my_file_name # Generate message my_msg = create_email(my_email_from, my_email_to, my_email_Subject, my_email_text, my_annex_path, My_annex_name) my_sender = 'Ali cloud mailbox' my_password = 'my password' my_receiver = [[email protected]']# Receiver email list # Send mail send_email(my_sender, my_password, my_receiver, my_msg) print(datetime.datetime.now()) if __name__ == "__main__": main();Copy the code

At this point, the content of automatic email is all over. I have marked every line with comments. If you still have some questions about a library, you can Google or Baidu it. Private messages are also welcome.

Everybody see officer if like, that point praise bai!!