使用 shell / python 进行sql的excel报表导出

　　如果要求你进行一个表数据的导出，如果使用shell的话，很容易做到，即执行一下 select 语句就可以拿到返回结果了！

如下：

/usr/bin/mysql -u"${username}" -p"${password}" --host=${host} -D"${database}" <  ${sql_script_path} > ${export_data_full_path1};

　　如上执行完成之后，数据就被导出到 export_data_full_path1 指定的文件位置去了。

　　如果想要使用 excel 格式来打开，有一个很简单的方法，即把后缀名命名为： .xls 就可以了。唯一的缺点是，此时你可能看到一个提示，即：文件名后缀与具体的格式不匹配等等！但是你仍然可以正常打开！

　　但是对于有中文一类的导出操作，则又是，另一番景象了！不过我们可以通过一个简单的编码转换来解决这个问题！

iconv -futf8 -tgb2312 -o ${export_data_full_path1} ${export_data_full_path1};

　　转换之后，即使有中文也能正常查看了！

　　至于使用 shell 进行发送邮件，则也是简单的一比！

echo "yesterday report infomation, FYI ." | mail -s "$yesterday_format report" -a ${export_data_full_path1} ${mail_receivers} 

完整的汇总数据sql 加 发送邮件 shell ， 区区几行代码如下：

#!/bin/bash

sql_script_path="/scripts/report/report.sql";

# data sync target configs
host="127.0.0.1";
username="test";
password='123456';
database='test';

export_data_path_prefix="/data/report/export_datas";
mail_receivers="test@163.com";

yesterday_format=`date '+%Y-%m-%d' -d '-1 day'`;
export_data_full_path1="${export_data_path_prefix}/${yesterday_format}-Report.xls";

/usr/bin/mysql -u"${username}" -p"${password}" --host=${host} -D"${database}" <  ${sql_script_path} > ${export_data_full_path1};

iconv -futf8 -tgb2312 -o ${export_data_full_path1} ${export_data_full_path1};


# todo: send mail begin
echo '$yesterday_format report infomation, FYI .' | mail -s "$yesterday_format report" -a ${export_data_full_path1} ${mail_receivers} 

echo `date '+%Y-%m-%d %H:%I:%S'` "over!"

 

如上，完成了使用 shell 进行汇总数据，并导出 excel 发送邮件的功能了！优缺点如下：

　　优点： 1. 简单！

　　缺点： 1. 不能支持复杂格式的指定！ 2. 需要安装 mysql-client 包！

 

所以，针对上面问题，尤其是不支持复杂格式的指定问题，注定我们需要一个更完善的方式来实现！即使用 python 等一类强大语言支持！

　　同样，我们对这类操作有两大要求： 1. 简单； 2. 支持复杂操作； 很明显，python 在这种场景下很得心应手！

 　　我们先来看一下完整实现：

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# need install components: openpyxl, pymysql
from openpyxl import Workbook
import pymysql
import time
import sys
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.utils import formataddr
import smtplib
import random

class MysqlClient:
    # create connection to mysql
    def __init__(self, db_host, user, password, database, port=10000, authMechanism="PLAIN"):
        self.conn = pymysql.connect(host=db_host,
                                  port=port,
                                  user=user,
                                  password=password,
                                  database=database,
                                  )
    def query(self, sql):
        with self.conn.cursor() as cursor:
            cursor.execute(sql)
            return cursor.fetchall()
    def close(self):
        self.conn.close()


class MailClient:
    # initial account info
    def __init__(self, sender_account, sender_pass, report_date):
        self.sender_account = sender_account
        self.sender_pass = sender_pass
        self.report_date = report_date
    def send_mail(self, receivers, subject, msg_content, xls_file_path):
        try:
          msg = MIMEMultipart()
          attach = MIMEApplication(open(xls_file_path, 'rb').read())
          attach.add_header('Content-Disposition', 'attachment', filename=self.report_date + '-Reprot.xlsx')
          msg.attach(attach)
          body = MIMEText(msg_content, 'html', 'utf-8')
          msg.attach(body)
          msg['From'] = self.sender_account
          msg['To'] = ','.join(receivers)
          msg['Subject'] = Header(subject, 'utf-8')
          server = smtplib.SMTP_SSL("smtp.exmail.qq.com", 465)
          server.login(mail_sender, sender_pass)
          server.sendmail(mail_sender, receivers, msg.as_string())
          server.quit()
          print(self.report_date + '-----success')
        except Exception as e:
          print(self.report_date + '-----failed')
          print(e)

def create_xsls(info_report, xls_file_path):
  mysql_client = MysqlClient(db_host='127.0.0.1', port=3306, user='test', password='test', database='testdb')
  wb = Workbook()
  inx_rep = 0
  for report in info_report: 
    result = mysql_client.query(report.get('report_sql'))
    if inx_rep == 0:
      ws = wb.worksheets[0]
      ws.title = report.get('rep_name')
    else:
      ws = wb.create_sheet(title=report.get('rep_name'))
    titleidx = 0
    titlenames = report.get('rep_title')
    for titlename in titlenames.split(","):
      if chr(ord('A') + titleidx) <= 'Z':
        title_name = chr(ord('A') + titleidx)
      else:
        title_name = "A" + chr(ord('A') + titleidx - 26)

      ws[title_name + "1"] = titlename
      titleidx = titleidx + 1
    row_num = 2
    for line in result:
      titleidx = 0
      for titlename in titlenames.split(","):
        if chr(ord('A') + titleidx) <= 'Z':
          title_name = chr(ord('A') + titleidx)
        else:
          title_name = "A" + chr(ord('A') + titleidx - 26)

        ws[title_name + str(row_num)] = line[titleidx]
        titleidx = titleidx + 1
      row_num = row_num + 1
    inx_rep = inx_rep + 1  
  wb.save(xls_file_path)


def main(argv):
    if(argv[1] == 'user_reg'):
      test_sql = '''SELECT DATE(create_time) AS 日期, user_flag AS 用户类型,
        FROM t_user s
        WHERE create_time>='2019-01-01' AND repay_time<=DATE_ADD(NOW(),INTERVAL 1 DAY)
        GROUP BY DATE(create_time),user_flag
        LIMIT 1000;
        '''
      info_report = [{"rep_name":"测试工单","rep_title":"日期,用户类型,注册来源","report_sql":test_sql}]
      receivers = ['test2@163.com']
    else:
      pass;
    
    report_date = time.strftime('%Y%m%d', time.localtime())
    rep_rand = random.randint(100, 999)
    xls_file_path = '/mnt/data/report/data/' + argv[1] + '-' + report_date + '-Reprot' + str(rep_rand) + '.xlsx';
    create_xsls(info_report, xls_file_path)
    mail_client = MailClient(sender_account='test@163.cn', sender_pass='1234567', report_date=report_date)
    mail_client.send_mail(receivers, subject='测试主题', msg_content='测试body', xls_file_path=xls_file_path)

if __name__ == "__main__":
    main(sys.argv)

 

 如上代码，代码本身很短，实际逻辑也很简单！

　　1. 定义好各种参数，如 文件名，数据库连接参数信息，邮件主题等信息；

　　2. 调用mysql模块进行数据查询；

　　3. 调用 workbook excel 模块进行 excel 文件内容填充；

　　4. 调用邮件发送模块进行邮件发送，包括附件；

 

最后，对于报表类，处理，我们一般要求每天进行汇总，对于这类请求，我们可以简单地使用 crontab 进行定时调度实现即可！

 

其中，稍微有几点要注意下的就是：

　　1. 必须先安装 openpyxl, pymysql 两个基础模块的依赖；

pip install openpyxl pymysql

　　2. 进行表单数据填充时，注意标题与数据的对位问题；

　　3. smtp 默认是25端口进行邮件发送，但是在某些情况无法使用 25 端口，如阿里云服务器环境，所以可以使用465端口进行发送；

　　4. 当需要进行附件的发送时，需要使用  MIMEMultipart 进行封装；

 

虽然也有些事项注意起来让我们感觉很烦，但是已经很简单了，这也 python 会这么流行的原因吧！

　　可作参考！