Shulou(Shulou.com)06/02 Report--

How to use Python to automatically generate reports to send by mail, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

Data analysts must be obsessed with all kinds of data reports every day, boss's, operational, product, and so on. And most reports are repetitive work, this article is to help you how to use Python to achieve automatic report sending, liberate your labor force, so that you have time to do more interesting things.

First of all, let's introduce the Python libraries to be used to implement automatic reports:

Pymysql a library that can connect to MySQL instances and implement the function of adding, deleting, modifying and querying.

A library of time included in the datetime Python standard library

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

Smtplib SMTP is simple Mail transfer Protocol, and Python is simply encapsulated into a library.

Email, a library for processing mail messages.

Why use the openpyxl library to handle Excel? Because it supports 100 lines per sheet, it is also a file that supports xlsx format. If you accept xls files and the number of lines per sheet is less than 6W, you can also use the xlwt library, which reads large files faster than openpyxl.

Next, we will enter the actual combat part to formally realize this process. I divide the whole implementation process into several functions to implement, so it will look more structured.

First, import all the libraries you want to use

# 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

Write a function get_datas (sql) that returns data as soon as it is passed into sql

Def get_datas (sql): # A function passed in sql to export data # establish a connection with the database conn = pms.connect (host=' instance address', user=' user', passwd=' password', database=' library name', port=3306 Charset= "utf8") # create a cursor object cursor cur = conn.cursor () using the cursor () method # execute SQL cur.execute (sql) using the execute () method to get the required data datas = cur.fetchall () # close the connection cur.close () # return the required data return datas

3. Write a function get_datas (sql) that returns the field name of the data as soon as it is passed into sql. Since a function can only return one value, two functions are used to return the data and the field name (that is, the header in excel).

Def get_fields (sql): # A function conn = pms.connect (host='rm-rj91p2yhl9dm2xmbixo.mysql.rds.aliyuncs.com', user='bi-analyzer', passwd='pcNzcKPnn', database='kikuu', port=3306, charset= "utf8") cur = conn.cursor () cur.execute (sql) # to get the required field name fields = cur.description cur.close () return fields

4. Write a function et_excel (data, field, file) that returns an excel with incoming data, field name and storage address.

Def get_excel (data, field, file): # function to write data and field names to excel # create a new workbook object new = openpyxl.Workbook () # activate a new sheet sheet = new.active # name sheet sheet.title = 'data presentation' # write field names in a loop to the excel*** line because the field format list contains the list The * element of each list is the field name for col in range (len (field)): # row represents the number of rows, column represents the number of columns, value represents the value entered by the cell, and the number of rows and columns starts at 1 Unlike python, note that _ = sheet.cell (row=1, column=col+1, value=u'%s'% field [col] [0]) # writes the data loop into each cell of the excel for row in range (len (data)): for col in range (len (field)): # because the * line writes the field name So to write _ = sheet.cell (row=row+2, column=col + 1, value=u'%s'% data [row] [col]) # from the second line to save the generated excel, it is necessary to newworkbook = new.save (file) # to return the generated excel return newworkbook

Write a function getYesterday () that automatically gets the format of yesterday's date string.

Def getYesterday (): # function to get the string format of yesterday's date # get today's date today = datetime.date.today () # get one day's date format data oneday = datetime.timedelta (days=1) # yesterday equals today's one day yesterday = today-oneday # get yesterday's date format string yesterdaystr = yesterday.strftime ('% Ymurf% MMI% d') # returns yesterday's string return yesterdaystr

Write a function create_email (email_from, email_to, email_Subject, email_text, annex_path, annex_name) to generate mail.

Def create_email (email_from, email_to, email_Subject, email_text, annex_path, annex_name): # enter sender nickname, recipient nickname, subject, body Attachment address, attachment name generate an email # generate an empty email instance with attachments message = MIMEMultipart () # insert the body into the message in the form of text (MIMEText (email_text, 'plain',' utf-8')) # generate the sender name (this has nothing to do with the sent message) message ['From'] = Header (email_from 'utf-8') # generate the recipient name (which has nothing to do with the received message) message [' To'] = Header (email_to, 'utf-8') # generate the message subject message [' Subject'] = Header (email_Subject, 'utf-8') # read the contents of the attachment att1 = MIMEText (open (annex_path,' rb'). Read (), 'base64' 'utf-8') att1 ["Content-Type"] =' application/octet-stream' # name of the generated attachment att1 ["Content-Disposition"] = 'attachment Filename=' + annex_name # insert attachment content into message message.attach (att1) # return email return message

7. Generate a function send_email (sender, password, receiver, msg) to send mail

Def send_email (sender, password, receiver, msg): # A function that enters mailbox, password, recipient, and email content to send email try: # find the server address where your mailbox is sent Send server = smtplib.SMTP_SSL ("smtp.mxhichina.com", 465) # SMTP server server.ehlo () # login to your account server.login (sender, password) # corresponding to the sender's mailbox account number, mailbox password # send email server.sendmail (sender, email) in parentheses Msg.as_string () # corresponding in parentheses is the sender's mailbox account, recipient's mailbox account (is a list), message content print ("email sent successfully") server.quit () # close connection except Exception: print (traceback.print_exc ()) print ("email failed")

Set up a main function, input all the custom content, and execute the main function.

Def main (): print (datetime.datetime.now ()) my_sql = sql = "SELECT a.id 'user ID', a.gmtCreate' user registration time', af.lastLoginTime'* login time', af.totalBuyCount 'historical payment order', af.paidmountUSD 'historical payment amount', af.lastPayTime 'user * * payment time' FROM table a LEFT JOIN tableb af ON a.id = af.accountId "# generate data my_data = get_datas (my_sql) # generate field name my_field = get_fields (my_sql) # get yesterday's date yesterdaystr = getYesterday () # file name my_file_name = 'user attribute' + yesterdaystr +' .xlsx'# file path file_path = 'DRAPR yesterdaystr' + my_file_name # generate excel get_excel (my_data, my_field File_path) my_email_from ='BI department automatic reporting robot 'my_email_to =' Operations'# email title my_email_Subject = 'user' + yesterdaystr # email body my_email_text = "Dear all, attached with weekly data Please check! BI team "# attachment address my_annex_path = file_path # attachment name my_annex_name = my_file_name # generate email 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 = [10001roomqq.com'] # recipient mailbox list # send email send_email (my_sender, my_password, my_receiver, my_msg) print (datetime.datetime.now ()) if _ _ name__ = = "_ _ main__": main () Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.