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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了使用 shell / python 进行sql的excel报表导出，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6628字，纯文字阅读大概需要10分钟。

内容图文

　　如上执行完成之后，数据就被导出到 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)

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;
‘‘‘

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‘):
      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 端口，如阿里云服务器环境；

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

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

　　可作参考！

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

标签：sys   ==   exp   服务   pts   attach   python   cat   后缀   

本文系统来源：https://www.cnblogs.com/yougewe/p/10623309.html

内容总结

以上是互联网集市为您收集整理的使用 shell / python 进行sql的excel报表导出全部内容，希望文章能够帮你解决使用 shell / python 进行sql的excel报表导出所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端