-
使用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") # mailtitle = '客商地推统计' # mailbody='打款失败数据-统计日期:%s' % (exec_time) # mailbody = '客商地推统计_%s' % (yesterday) # 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='password', 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', 'password') 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])