#coding =utf-8
Import Sys
Import XLWT
Import Pymysql as MySQLdb #这里是python3 If you are python2.x, import mysqldb
Import datetime
Import time
Import Smtplib
From Email.mime.multipart import Mimemultipart
From Email.mime.text import Mimetext
From email.mime.application import mimeapplication
Import Os.path
Host = ' XXXXX '
user = ' xxxx '
PWD = ' xxxxx '
Port = 3306
db = ' dbname '
Sheet_name = ' report ' + time.strftime ("%y-%m-%d")
filename = ' Report_ ' + time.strftime ("%y-%m-%d") + '. xls '
Out_path = '/home/report/report_ ' + time.strftime ("%y-%m-%d") + '. xls '
Print (Out_path)
sql = "SELECT * from XXX"
def export ():
conn = MySQLdb.connect (host,user,pwd,db,charset= ' UTF8 ')
cursor = Conn.cursor ()
Count = Cursor.execute (SQL)
Print ("Query out" + STR (count) + "record")
#来重置游标的位置
Cursor.scroll (0,mode= ' absolute ')
#搜取所有结果
Results = Cursor.fetchall ()
# Get the data field name inside MySQL
Fields = Cursor.description
Workbook = xlwt. Workbook () # Workbook is the carrier of sheet survival.
Sheet = Workbook.add_sheet (sheet_name,cell_overwrite_ok=true)
# Write the field information
For field in range (0,len):
Sheet.write (0,field,fields[field][0])
# Get and write data segment information
row = 1
Col = 0
For row in range (1,len (results) +1):
For Col in Range (0,len):
Sheet.write (row,col,u '%s '%results[row-1][col])
Workbook.save (Out_path)
_user = "[Email protected]"
_PWD = "******"
Areceiver = "[email protected],[email protected]"
ACC = "[email protected],[email protected]"
#如名字所示Multipart就是分多个部分
msg = Mimemultipart ()
msg["Subject"] =u ' "Data statistics _ ' + time.strftime ("%y-%m-%d ") + U '" Rehabilitation 1.0 System _ Operational Data "
Msg["from"] = _user
Msg["to"] = Areceiver
msg["Cc"] = ACC
Def send_email ():
#---This is the text part---
Content = "' Deal all,
The attachment is the recovery 1.0 system Operation data, please check! ‘‘‘
Part = Mimetext (content, ' plain ', ' utf-8 ')
Msg.attach (part)
#---This is the attachment section---
#xls类型附件
file_name = '/home/report/' + filename
Part = Mimetext (open (file_name, ' RB '). Read (), ' base64 ', ' gb2312 ')
part["Content-type"] = ' application/octet-stream '
basename = Os.path.basename (file_name)
part["content-disposition"] = ' attachment; filename=%s '% basename.encode (' gb2312 ')
Msg.attach (part)
s = smtplib. SMTP ("smtp.qq.com", timeout=30) #连接smtp邮件服务器, port default is 25
S.login (_user, _pwd) #登陆服务器
S.sendmail (_user, Areceiver.split (', ') + acc.split (', '), msg.as_string ()) #发送邮件
Print ("Eamil send Successfully")
S.close ()
#结果测试
If __name__== "__main__":
Export ()
Send_email ()
Python implements query SQL after exporting to Excel and sending mail