python2导出MySQL数据发邮件脚本
-
使用python2,本机默认Python版本2.7.16
-
# 打印当前Python版本 python --version
-
更改数据库链接、修改邮箱smtp信息(脚本里面的信息)。修改SQL语句。执行脚本(脚本可以带SQL字符串参数执行)
python KSDT_Stat.py
-
提示没有安装MySQLdb模块
-
安装pip
# 打印pip版本 python -m pip --version # 更新pip版本 python -m ensurepip --upgrade
-
安装MySQLdb/pymysql/mysqlclick
sudo pip install mailer sudo pip install pymysql sudo pip install xlsxwriter #使用阿里云镜像 安装邮箱库 pip install mailer -i https://mirrors.aliyun.com/pypi/simple/ #pymysql 默认版本安装不成功,使用指定版本 安装Python MySQL客户端库 pip install pymysql==0.8.1 -i https://mirrors.aliyun.com/pypi/simple/ #安装 xls表格库 pip install xlsxwriter -i https://mirrors.aliyun.com/pypi/simple/
-
执行脚本
#数据文件默认为脚本相对目录以时间结尾的文件 bogon:Desktop ryx$ python ex_data.py "SELECT * FROM hulu_activity_merchant LIMIT 100" 脚本输出文件目录/Users/ryx/Desktop 输出文件hulu_sql_data_2022-08-08_15-24.xlsx 参数个数2 参数SQLSELECT COUNT(1) from merchant_api_audit_202208 最终SQLSELECT COUNT(1) from merchant_api_audit_202208 文件生成完毕2022-08-08 15:24:10 邮件发送完毕2022-08-08 15:24:10 bogon:Desktop ryx$
-
相关命令整理
# 打印当前Python版本
python --version
# 打印pip版本
python -m pip --version
#升级
pip sudo pip install --upgrade pip
#降低pip版本,指定pip19.2
sudo python -m pip install pip==19.2
python -m pip install pip==19.2 -i https://mirrors.aliyun.com/pypi/simple/
#指定下载源
pip install ping3 -i https://mirrors.aliyun.com/pypi/simple/
#pip 安装列表
pip list
#手动下载安装方式
wget https://files.pythonhosted.org/packages/44/39/6bcb83cae0095a31b6be4511707fdf2009d3e29903a55a0494d3a9a2fac0/PyMySQL-0.8.1.tar.gz
tar -zxvf PyMySQL-0.8.1.tar.gz
#解压后进入目录
cd /home/app/PyMySQL-0.8.1
python setup.py install
#使用pip安装第三方库
pip install openpyxl
#指定版本安装方法:在上面的基础上,在库名后附加版本信息,可以安装指定版本的第三方库。例如
pip install openpyxl==2.3.4
#同理,更新方法也是一样的用法:
#基础更新方法:使用 可以将这个库更新到最新版本。
pip install --upgrade openpyxl
#指定版本更新方法:使用 可以将这个库更新到指定的版本。
pip install --upgrade openpyxl==2.3.4
- 脚本传递参数
#传递参数
python test.py arg1 arg2 arg3
#在python中取参数
sys.argv[0] #取出来的是脚本名
sys.argv[1] #取到第一个参数
len(sys.argv) #计算命令行参数个数。
- 脚本如下:
#!/usr/bin/env python
# coding:utf-8
# liao@hulupos.com
import os
import sys
import pymysql
import xlsxwriter
import datetime
import zipfile
from mailer import Mailer, Message
now_datetime = str(datetime.datetime.today())[:19]
now_date = now_datetime[:10]
now_time = now_datetime[11:]
exec_time = now_datetime[:16].replace(' ', '_').replace(':', '-')
date_time = datetime.datetime.strftime(datetime.datetime.now() - datetime.timedelta(days=1), "%Y_%m_%d")
yesterday = datetime.datetime.strftime(datetime.datetime.now() - datetime.timedelta(days=1), "%Y%m")
# yesterday = datetime.datetime.strftime(datetime.datetime.now()- datetime.timedelta(days=1),"%Y-%m-%d")
today = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d")
yesterday_time = "%s 00:00:00" % (yesterday)
today_time = "%s 00:00:00" % (today)
MailTo=['liao@hulupos.com']
FileDir=os.path.abspath('.')
#FileDir = '/home/app/ex_data_log'
print('脚本输出文件目录' + FileDir)
if not FileDir:
FileDir = '/Users/ryx/Desktop/'
ExcelFile1 = 'hulu_sql_data_%s.xlsx' % (exec_time)
print('输出文件' + ExcelFile1)
print('参数个数' + sys.argv.__len__().__str__())
if sys.argv.__len__() < 2:
# 如果不在脚本里面传SQL参数,则修改这个默认参数
sql1 = """
SELECT VERSION();
"""
else:
# 第一个参数 用""双引号定义
sql1 = sys.argv[1]
print('参数SQL' + sql1)
print('最终SQL' + sql1)
if not os.path.isdir(FileDir):
os.mkdir(FileDir)
def sql2xls(sql, esheet='Sheet1'):
myconv = {
pymysql.FIELD_TYPE.BIT: unicode,
pymysql.FIELD_TYPE.CHAR: unicode,
pymysql.FIELD_TYPE.DATETIME: unicode,
pymysql.FIELD_TYPE.DATE: unicode,
pymysql.FIELD_TYPE.ENUM: unicode,
pymysql.FIELD_TYPE.GEOMETRY: unicode,
pymysql.FIELD_TYPE.INTERVAL: unicode,
pymysql.FIELD_TYPE.NEWDATE: unicode,
pymysql.FIELD_TYPE.NEWDECIMAL: unicode,
pymysql.FIELD_TYPE.NULL: unicode,
pymysql.FIELD_TYPE.SET: unicode,
pymysql.FIELD_TYPE.STRING: unicode,
pymysql.FIELD_TYPE.TIMESTAMP: unicode,
pymysql.FIELD_TYPE.TIME: unicode,
pymysql.FIELD_TYPE.VARCHAR: unicode,
pymysql.FIELD_TYPE.YEAR: unicode,
}
myconn = pymysql.connect(host='127.3.35.30', port=3306, user='root', passwd='列子密码', db='test',
conv=myconv)
# myconn.set_character_set('UTF8')
mycur = myconn.cursor()
mycur.execute(sql)
general_format = w.add_format()
general_format.set_border()
general_format.set_font_size(10)
general_format.set_font_name('Arial')
title_format = w.add_format()
title_format.set_bold()
title_format.set_bg_color('yellow')
title_format.set_border()
title_format.set_font_size(10)
title_format.set_font_name('Arial')
ws = w.add_worksheet(esheet)
column_num = 0
for line in mycur.description:
ws.write(0, column_num, line[0].decode('utf-8'), title_format)
column_num = column_num + 1
row_num = 1
column_num = 0
for lines in mycur.fetchall():
for line in lines:
if not line:
ws.write(row_num, column_num, 'NULL', general_format)
else:
ws.write(row_num, column_num, line.decode('utf-8'), general_format)
column_num = column_num + 1
row_num = row_num + 1
column_num = 0
myconn.close()
def xls2zip(fs):
fslist = []
fslist.extend(fs) if isinstance(fs, list) else fslist.append(fs)
zf = zipfile.ZipFile(zipfile, 'w', zipfile.ZIP_DEFLATED)
for f in fslist:
zf.write(f)
zf.close()
def SendMail(recipients, messSub='测试', messBody='测试', attachments=[]):
recs = []
atts = []
recs.extend(recipients) if type(recipients) is list else recs.append(recipients)
atts.extend(attachments) if type(attachments) is list else atts.append(attachments)
message = Message(From='hljliao1992@163.com')
message.To = recs
message.charset = 'utf-8'
message.Subject = messSub
message.Body = messBody
if bool(atts):
for i in atts:
message.attach(i)
sender = Mailer('smtp.163.com')
sender.login('hljliao1992@163.com', '123')
sender.send(message)
os.chdir(FileDir)
w = xlsxwriter.Workbook(ExcelFile1)
sql2xls(sql1)
print('文件生成完毕'+str(datetime.datetime.today())[:19])
w.close()
SendMail(MailTo, ExcelFile1, ExcelFile1, ExcelFile1)
print('邮件发送完毕'+str(datetime.datetime.today())[:19])