python实用脚本-通过jenkins界面化导出数据

1、jenkins 配置

2、jenkins 脚本

ansible-playbook /opt/test.yaml --extra-vars "loanno=${loanno}" --extra-vars "type=${type}"

中转机器脚本

test.yaml
---
- name: 开始导出合同
hosts: test
tasks:
- name: 执行导出脚本
shell: echo {{ loanno }}
shell: echo {{ type }}
shell: echo loanno
shell: python /app/crontab/python/autocontract/contract_supplement.py {{ loanno }} {{ type }}
register: result
- name: 打印输出结果
debug: msg="{{ result }}"

3、远程机器下载脚本

python /python/autocontract/contract_supplement.py {{ loanno }} {{ type }}

4、python 脚本

# coding:utf8
import sys
import xlwt
import mysql.connector
import datetime
import send_mail
import os
import time
import file_tar


nowtime = datetime.datetime.now().strftime('%Y%m%d')
oddir="/app/crontab/python"
#filename = "%s/cma_registerinfo%s.xls"%(oddir,nowtime)
file_count = 0
end_date=time.strftime("%Y-%m-%d", time.localtime())

tmp_dir='/app/crontab/python/autocontract'
tmp_file_list='tmp.txt'
sorce_dir='/data/nfs/yyapp/yylending/usercontract'
ods_dir='/app/crontab/python/autocontract/tmp'
filename=ods_dir+'/test.tar.gz'
#var_loan=''
#var_type=''

#sql='select path  from  test   where  loadNo  in (%s) and  cid  in(%s);' %(var_loan,var_type)



config = {
    'user': '******',
    'password': ''******',',
    'host': ''******',',
    'port': ''******',',
    'database': ''******',',
    'charset': 'utf8'
}


mail_list = {
    'sender':'xiajq@PH-LOTH-V159.localdomain', #发件人
    'receivers':'test@qq.com', #收件人
    'title':'*****', #邮件主题
    'content':'*****', #邮件内容
    'attfilename':'',#附件内容
    'server':'localhost' #发送邮件服务器。默认为本机
}



def get_conn():
    conn = mysql.connector.connect(**config)
    return conn


def query_all(cur, sql, args):
    '''
    #参数说明
    :param cur:
    :param sql: 执行的sql
    :param args: sql中的桉树
    :return:
    '''
    print(sql)
    print(args)
    cur.execute(sql, args)
    return cur.fetchall()

def export_excle(filename, sql, outputpath):
    '''
    # 参数说明
    :param filename: 文件名称
    :param sql: 执行脚本
    :param outputpath: 输出路径
    :return:
    '''
    file_count = 0
    try:
        conn = get_conn()
        cur = conn.cursor()
        # 查询结果 tar_file
        results = query_all(cur=cur, sql=sql, args=None)
        print(type(results))
        if len(results):
            file_tar.copy_file(sorce_dir, ods_dir, *results)
            file_tar.tar_file(ods_dir, 'test.tar.gz')
            file_count = file_count + 1
        else:
            print("返回结果为空")


    finally:
        if conn:
            conn.close()
    return file_count


# 结果测试
if __name__ == "__main__":
   # sql = input("请输入要查询的sql:")
    #filename = input("请输入excle的名字")
    var_loan="'"+sys.argv[1].replace(',',"','")+"'"   #接受jeknins传递的值
    var_type="'"+sys.argv[2].replace(',',"','")+"'"    #接受jeknins传递的值
    arg=[var_loan,var_type]
    print(var_loan+var_type)
    sql='select contract_path  from   contract   where  loadNo  in (%s) and  cid  in(%s);' %(var_loan,var_type)
    print(sql)
    file_count =  export_excle(filename, sql, oddir)  #执行导出结果
    if os.path.exists(filename) and os.path.getsize(filename)>0:
        if file_count >=1:
           print("邮件已经发送 %s"%(mail_list['attfilename']))
           mail_list['attfilename'] = filename
           print("邮件已经发送 %s"%(mail_list['attfilename']))
           send_mail.sendmail(**mail_list)
        else :
           print("文件大小为空，请检查")
    else :
        print("文件不存在，请联系管理员检查脚本是否有异常"

#!/user/bin/python
#-*- coding: utf-8 -*-
import sys
import os
import shutil
import tarfile
import zipfile
import io
# coding:utf8
# author: xiajq
# 实现批量输入借据号和uid导出合同并且重名自动打包发送到邮箱需要人的邮箱
#1、在jenkins 界面输入借据号和uid 2.调用Python脚本实现将邮件自动拷贝到临时目录，打包发送到对应收件人的邮箱

reload(sys)
sys.setdefaultencoding('utf-8')#添加该方法声明编码

tmp_dir='/test/autocontract'
tmp_file_list='tmp.txt'
sorce_dir='/test/usercontract'
ods_dir='test/tmp'
def copy_file(sorce_dir,ods_dir,*file_list):
    '''
    拷贝文件
    :param tmp_file_list:   记录文件名字
    :param sorce_dir:  源文件
    :param ods_dir:  目标文件
    :return:
    '''
    for line in list(file_list):
        line = ''.join(line)
        print(line)
        print(type(line))
        new_name = line.replace('/', '_')
        print("开始拷贝")
        print("拷贝后的名称为 %s" % new_name)
        #         ods_file="%s/%s"%(ods_dir,new_name)
        ods_file = ods_dir + '/' + new_name
        print(type(line))
        #            filename=line.strip().encode('utf-8') 
        soure_file = "%s/%s" % (sorce_dir, line)
        print(soure_file)
        print(ods_file)
        shutil.copy(soure_file, ods_file)
        print("拷贝成功")


def  tar_file(dir,tarname):
    '''
    打包某个目录下的所有文件
    :param dir: 需要打包的目录
    :param tarname: 打包的名字
    :return:
    '''
    os.chdir(ods_dir)
    tf = tarfile.open(tarname, "w:gz")
    for fname in os.listdir(ods_dir):
        print(fname)
        if fname.endswith(".pdf"):
            tf.add(fname)
    print(tf.members)
    print(tf.name)
    tf.close()

3、发送邮件

#coding=utf-8
import smtplib
from email.header import Header
from email.mime.text import MIMEText
from email.utils import formataddr
from email.utils import parseaddr, formataddr
from email.mime.multipart import MIMEMultipart



mail_list = {
    'sender':'admin@qq.com', #发件人
    'receivers':'test@qq.com', #收件人
    'title':'云测推荐失败数据监控', #邮件主题
    'content':'云测推荐失败数据见附件。请及时查看原因', #邮件内容
    'filename':'',#附件内容
    'server':'localhost' #发送邮件服务器。默认为本机
}


def _format_addr(s):
    name, addr = parseaddr(s)
    return formataddr((Header(name, 'utf-8').encode(), addr))  #

def sendmail(**kwargs):
    #sender,receivers,title,content,filename,server = 'localhost'
    '''
    :param sender:  发件人 my_sender = 'test1@qq.com,test2@qq.com'
    :param receivers:  收件人
    :param title:  邮件标题
    :param content: 邮件内容
    :param attfilename: 附件名称
    :param server: 服务器的名字默认为localhost,本机
    :return:
    '''
    message = MIMEMultipart()
    message['From'] = _format_addr(kwargs['sender'])
    message['Subject'] = Header(kwargs['title'], 'utf-8')
#    message['To'] = _format_addr(kwargs['receivers']) #收件人只显示自己
#    print(kwargs['receivers'])
    message['To'] = kwargs['receivers']# 可以看得见多个收件人


    #
    message.attach(MIMEText(kwargs['content'], 'plain', 'utf-8'))
    att1 = MIMEText(open(kwargs['attfilename'], 'rb').read(), 'base64', 'utf-8')
    att1["Content-Type"] = 'application/octet-stream'
    att1["Content-Disposition"] = 'attachment; filename=%s'%(kwargs['attfilename'])
    message.attach(att1)

    try:
        smtpObj = smtplib.SMTP(kwargs['server'])
        smtpObj.sendmail(kwargs['sender'], kwargs['receivers'].split(','), message.as_string())
        print(kwargs['receivers'].split(','))
    except smtplib.SMTPException:
        print("Error: 无法发送邮件")