Python instance-use pymysql to query last month's data and make excel to send to mailbox

The script is as follows:

#! / user/bin/python

#-*-coding: utf-8-*-

# 1. Log in to the database to query data 2. Generate the queried data into excel file 3.xls file and send it by mail

# Import module: pymysql module is used to log in to the database and other related operations (python3). If mysqldb module is used in python2, you need to install: pip3 install pymysql.

# xlwt is used to generate excel files. You need to install: pip3 istall xlwt

# smtplib and email are python built-in modules for sending mail

Import datetime

Import pymysql

Import xlwt

Import smtplib

From email.mime.multipart import MIMEMultipart

From email.mime.text import MIMEText

From email.mime.application import MIMEApplication

# define the date of data query

# datetime.timedelta time addition and subtraction

# datetime.date.today () 2019-10-25

# tmf_day first day of this month 2019-10-01

# lme_day on the last day of last month 2019-09-30

# lmf_day first day of last month 2019-09-01

# datetime.datetime.now () 2019-10-25 11:03:52

# today_zero today's 00:00 2019-10-25 00:00:00

# tmf_zero first 00:00 of this month 2019-10-01 00:00:00

# lme_minute last minute last month 2019-09-30 23:59:59

Today = datetime.date.today ()

Tmf_day = today.replace (day=1)

Lme_day = tmf_day-datetime.timedelta (days=1)

Lmf_day = lme_day.replace (day=1)

Now = datetime.datetime.now ()

Today_zero = now-datetime.timedelta (hours=now.hour, minutes=now.minute, seconds=now.second)

Tmf_zero = today_zero.replace (day=1)

Lme_minute = tmf_zero-datetime.timedelta (hours=0, minutes=0, seconds=1)

# 1. Log into the database

# myconn defines a connection database statement

# pymysql.cursors.DictCursor outputs a dictionary format. By default, the cursor method of pymysql.cursors.Cursor returns an object of type BaseCursor. The result of each record of an object of type BaseCursor is represented as a list after the query is executed.

# query_sql define query statement

# mycursor.fetchmany (x) receives x data results, and mycursor.fetchall () receives all data results.

# mycursor.close () query ends

# myconn.close () disconnect

# pass a single parameter: field ='-'

# sql_talk= "UPDATE cnp.Test set aversion% s'" where baked goods% s'"

# cursor.execute (sql_talk% field)

# pass multiple parameters: use {0} placeholder

# field ='-'

# a = 'code'

# sql_talk= "UPDATE cnp.Test set {0} =''where business_registration_code=' {1}'" .format (afield)

# cursor.execute (sql_talk)

# or

# cur.execute ("select score from class_info where sex='%s' and age=%d"% ('XX','XX'))% s for characters and% d for integers

Myconn = pymysql.connect (host='192.168.42.128',user='XXXX',passwd='xxxx',database='test',charset= "utf8")

Mycursor = myconn.cursor (cursor=pymysql.cursors.Cursor)

Query_sql = ""

SELECT conference_number,conference_title,conference_name,ccs_login_name,start_time,amount FROM `cost_ 08` where end_time between'% s' and'% s'

"

Mycursor.execute (query_sql% (lmf_day,lme_minute))

Query_result=mycursor.fetchall ()

Mycursor.close ()

Myconn.close ()

# 2. Generate excel file

The location coordinates of # excel start with 0, the first row, the first column, the first row, the second column, and the second row, the first column, and the first column, respectively.

# the first line of list1, which is used to generate the fields to be queried

# wbk.add_sheet creates worksheet, which is equivalent to sheet1 in excel

# xlwt.XFStyle () initialization style

# datastyle.num_format_str sets the excel cell format

List1 = ('first column', 'second column', 'third column', 'fourth column', 'fifth column', 'sixth column')

Wbk = xlwt.Workbook (encoding='utf-8')

Worksheet = wbk.add_sheet ('My worksheet')

Datastyle = xlwt.XFStyle ()

Datastyle.num_format_str = 'yyyy-mm-dd hh:mm:ss'

# len (list1): the length of the list1, indicating the number of fields in the list1

# the number of data columns of list query results, that is, the number of fields to be queried

# worksheet.write: write elements to the corresponding table

# worksheet.write [list]: enter the corresponding fields of list1 into the first row and the fields to be queried.

# the number of row query results, that is, the total number of records queried. (0d0) data has been populated, so the row+1 line ends with the start of (1).

When # list loops to the fourth column, the excel cell format is set, and the remaining columns are not formatted, and the loop remains unchanged.

# worksheet.write (row+1,list,query_ result [row] [list]) is equivalent to: worksheet.write (row+1,0,query_ result [row] [0])

# if the result of the query is ('1, 2, 2, 2, 3, 3, 4):

# (1) the result is 1 (1) the result is 2 12

# (2) the result is 2 (2) the result is 3 23

# (3) the result is 3 (3) the result is 4 34

# wbk.save saves the file, followed by the file name

For list in range (0jinlen (list1)):

Worksheet.write (0MagneList list1 [list])

For row in range (0jinlen (query_result)):

If list = = 4:

Worksheet.write (row+1,4,query_ result [row] [4], datastyle)

Else:

Worksheet.write (row+1,list,query_ result [row] [list])

Wbk.save ('xx.xls')

# 3. Define a function to send mail

# def send_email (): define a function

# msg = MIMEMultipart () is used to build an instance with attachments

# msg ["Subject"] email subject

# msg ["From"] = user Sender

# msg ["To"] = to recipient

# part1 = MIMEText ("Hello,\ n\ nThe usage data of last month is attached.") Build a plain text file

# msg.attach (part1) attach means fixed and is used to build email content

# part2 = MIMEApplication (open (ritual C:\ Users\ Administrator.USER-20190917HT\ Desktop\ top education .xls', 'rb'). Read ()) build attachment 1

# open (ritual C:\ Users\ Administrator.USER-20190917HT\ Desktop\ top education .xls', 'rb'). Read () opens the xx file in binary read-only mode

# is equivalent to f = open ('test.txt')

# f.read ()

# rb binary read-only mode'r 'read mode,' w' write mode,'a 'append mode,' b 'binary mode,' + 'read / write mode.

# open (ringing C:\) r' is to prevent character escape. If'\ t' appears in the path,\ t will be escaped without adding r, and'\ t' will be able to retain its original appearance after adding'r'.

# part2.add_header ('Content-Disposition','attachment',filename=' top education .xls') header message. Filename can write whatever name is written, and what name is displayed in the email.

# smtplib.SMTP ("smtp.139.com", timeout=30) SMTP server in the sender's mailbox. The default port is 25.

# s.login (user,pwd) login mailbox

# send_email () calls the function

Def send_email ():

User = 'xxxxxx@139.com'

Pwd = 'xxxxxxx'

To = 'xxxxxx@qq.com'

Msg = MIMEMultipart ()

Msg ["Subject"] = 'xxxx usage Statistics last month'

Msg ["From"] = user

Msg ["To"] = to

Part1 = MIMEText ("Hello,\ n\ nThe usage data of xxxx last month is attached.")

Msg.attach (part1)

Part2 = MIMEApplication (open (ritual C:\ Users\ Administrator.USER-20190917HT\ Desktop\ xx.xls','rb') .read ()

Part2.add_header ('Content-Disposition','attachment',filename='xx.xls')

Msg.attach (part2)

S = smtplib.SMTP ("smtp.139.com", timeout=30)

S.login (user,pwd)

S.sendmail (user,to,msg.as_string ())

S.close ()

Send_email ()