Export MySQL data, generate Excel documents with pandas, and send mail

First you have to install a variety of libraries ....

Like Mysql,pandas,numpy or something like that.

I am using the pandas version of Pandas (0.16.2)

Where Openpyxls version is OPENPYXL (1.8.6)

In fact, everywhere MySQL query results export, of course, you can use a client such as Sqllog,navicat direct export, simple and fast, the following code is only in a time-bound and in some format periodically send SQL query results in an environment only exists.

Note: Further pandas of course can also be combined with matplotlib to generate a beautiful pie chart or bar chart, but I do not have this requirement, so there is no writing to create a picture of the part

Release code:

#!/usr/bin/env python# -*- coding: utf-8 -*-import pandasimport pandas  as pdimport MySQLdbimport MySQLdb.cursorsimport osimport datetimefrom  Email.mime.text import mimetextfrom email.mime.multipart import mimemultipartimport  smtplib# functions that return SQL results def retsql (SQL):     db_user = mysqldb.connect (' IP ', ' username ', ' password ', ' J ' database name (can not be specified) ', cursorclass=mysqldb.cursors.dictcursor (set return result in dictionary format)     cursor  = db_user.cursor ()     cursor.execute ("set names utf8;" (Setting the character set to Utf-8, otherwise garbled in the returned results, even if the database encoding setting is Utf-8))      cursor.execute (SQL)     ret = cursor.fetchall ()     db_user.close ()     return ret# the function Def retxls (ret) that generated the XLS file, DT):     file_name = datetime.datetime.now (). Strftime ("/path/to/store/%Y-%m-%d-%h:%m ")  + dt + ". Sql.xlsx "    DRET = PD. Dataframe.from_records (ret)     dret.to_excel (filename, "Sheet1", engine= "OPENPYXL") # # # Z Note OPENPYXL This library may be in the generation of XLS error, pip install openpyxls==1.8.6, the other version seems to pandas a bit of conflict, install 1.8.6      print  "OK!!!  the file in ", file_name    return filename# send Message function # #传入主题, display name, Target mailbox, Attachment name DEF SENDM (sub,cttstr,to_list,file):     msg = mimemultipart ()      att = mimetext (open (file, ' RB '). Read (), "base64", "Utf-8")     att[" Content-type "] = " Application/octet-stream "    att[" Content-Disposition "] =   ' attachment; filename= ' SQL query results. xlsx "'     msg[' from '] =  ' sender address '      msg[' Subject '] = sub    ctt = mimetext (CTTSTR, ' plain ', ' utf-8 ')     msg.attach (ATT)     msg.attach (CTT)     try:         server = smtplib. SMTP ()          #server. Set_debuglevel (1)   ## #如果问题可打开此选项以便调试         server.connect ("mail.example.com", "the")          server.starttls ()    ## #如果开启了ssl或者tls加密, turn on encryption          server.login ("Available mailbox user name", "password")          Server.sendmail (msg[' from '],to_list,msg.as_string ())          Server.quit ()         print  ' OK!!! '     except exception,e:        print str (e) # # #想要查询的sql语句sql = "" "SQL Statement" "" #接收邮件的用户列表to_list  = [' [email protected] ',  ' [email  ProteCTED] '] #执行sql并将结果传递给retret  = retsql (SQL) #将结果文件路径结果传给retfileretfile  = retxls (ret, "1") #发送邮件 # Send SQL statement content SENDM (SUB1,SUB1,TO_LIST,RETFILE1)

Although the above code has a lot of room to change, but it can be used and used, but not very important part, it will not continue to improve.

This article is from the "Ear Notebook" blog, please make sure to keep this source http://youerning.blog.51cto.com/10513771/1708941

Export MySQL data, generate Excel documents with pandas, and send mail