将mysql数据库的数据导出做成excl表格通过邮件发送附件发给指定人

mysql数据库的数据导出成excl表

方法一：

mysql -uroot -p123456 -e  “select *.* from 表  into outfile ‘文件路径.xls’
into outfile 将输出的数据导入到指定文件里以.xls结尾。需要开启secure-file-priv  默认只能将文件导入到/var/lib/mysql-files目录下。
此种方式导出的纯数据，没有表结构

方法二：

mysql -uroot -p123456 -e "select *。* from 表" > /bash/mysqlsql/sql.csv 2>/dev/null
将输出的数据重定向到一个文件里，以.csv结尾
awk '{print $1","$2","$3","$4","$5","$6 > "/bash/mysqlsql/sql1.csv"}' /bash/mysqlsql/sql.csv
将表的分隔符换成“，” （因为csv表格的分割符是逗号）

转换字符编码  linux使用的字符集是utf-8  windows使用的是GBK

iconv -f UTF-8 -t GBK 转换后的文件 -o 源文件
        -f  -t 将UTF-8格式的 转换成GBK的

将文件定义成附件发送

使用python邮件

import smtplib
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication

if __name__ == '__main__':
        fromaddr = '发件人邮箱'
        password = '密码'
        toaddrs = ['收件人邮箱'，'可定义多人']

        content = '邮件内容'
        textApart = MIMEText(content)
　　　　　　#发送图片附件 
        imageFile = '图片路径'        
        imageApart = MIMEImage(open(imageFile, 'rb').read(), imageFile.split('.')[-1])
        imageApart.add_header('Content-Disposition', 'attachment', filename=imageFile)
          #发送文本附件
        pdfFile = '文本路径'
        pdfApart = MIMEApplication(open(pdfFile, 'rb').read())
        pdfApart.add_header('Content-Disposition', 'attachment', filename=pdfFile)

          #发送压缩包附件
        zipFile = '压缩包路径'        zipApart = MIMEApplication(open(zipFile, 'rb').read())        zipApart.add_header('Content-Disposition', 'attachment', filename=zipFile)

        m = MIMEMultipart()
        m.attach(textApart)
        m.attach(imageApart) #发送图片附件，没有图片需要注释
        m.attach(pdfApart)   #发送文本附件，没有文本需要注释
        m.attach(zipApart)   #发送压缩包附件，没有压缩包需要注释
        m['Subject'] = '邮件标题'

        try:
            server = smtplib.SMTP_SSL('smtp.exmail.qq.com')
            server.login(fromaddr,password)
            server.sendmail(fromaddr, toaddrs, m.as_string())
            print('success')
            server.quit()
        except smtplib.SMTPException as e:
            print('error:',e) #打印错误

import smtplib
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication

if __name__ == '__main__':
        fromaddr = 'wangkang@innovationai.cn'
        password = 'Wk17908452'
        toaddrs = ['wangkang@innovationai.cn','yupanlong1@qq.com']

        content = '邮件内容'
        textApart = MIMEText(content)

        imageFile = '图片路径'
        imageApart = MIMEImage(open(imageFile, 'rb').read(), imageFile.split('.')[-1])
        imageApart.add_header('Content-Disposition', 'attachment', filename=imageFile)

        pdfFile = '文件路径'
        pdfApart = MIMEApplication(open(pdfFile, 'rb').read())
        pdfApart.add_header('Content-Disposition', 'attachment', filename=pdfFile)


       zipFile = '压缩包路径'
       zipApart = MIMEApplication(open(zipFile, 'rb').read())
       zipApart.add_header('Content-Disposition', 'attachment', filename=zipFile)

        m = MIMEMultipart()
        m.attach(textApart)
        m.attach(imageApart)
        m.attach(pdfApart)
        m.attach(zipApart)
        m['Subject'] = '邮件标题'

        try:
            server = smtplib.SMTP_SSL('smtp.exmail.qq.com')
            server.login(fromaddr,password)
            server.sendmail(fromaddr, toaddrs, m.as_string())
            print('success')
            server.quit()
        except smtplib.SMTPException as e:
            print('error:',e) #打印错误