使用 shell / python 进行sql的excel报表导出 -云博客

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

# 数据库 2024-05-05 13:59 0 30 来源：云博客

　　如果要求你进行一个表数据的导出，如果使用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/bashsql_script_path="/scripts/report/report.sql";# data sync target configshost="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 beginecho ‘$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, pymysqlfrom openpyxl import Workbookimport pymysqlimport timeimport sysfrom email.mime.application import MIMEApplicationfrom email.mime.multipart import MIMEMultipartfrom email.header import Headerfrom email.utils import formataddrimport smtplibimport randomclass 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 sWHERE create_time>=‘2019-01-01‘ AND repay_time<=DATE_ADD(NOW(),INTERVAL 1 DAY)GROUP BY DATE(create_time),user_flagLIMIT 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)