dataworks中PYODPS2脚本数据库查询结果乱码处理

 

# -*- coding: utf-8 -*-
import datetime
import sys,time
import sys
from odps import options
import smtplib
from email.header import Header
from email.mime.text import MIMEText

# 设置编码(此设置可修复数据库查询结果中文乱码的问题)
reload(sys)
sys.setdefaultencoding('utf-8')

# 第三方 SMTP 服务
# SMTP服务器
mail_host = "smtp.126.com"      
# 用户名
mail_user = "123@126.com"    
# 授权密码，非登录密码
mail_pass = "123456"         
# 发件人邮箱(最好写全, 不然会失败)
sender = '123@126.com'       
# 接收邮件，可设置为你的QQ邮箱或者其他邮箱
receivers = ['abc@qq.com','bcd@qq.com']   
# 邮件主题
title = '邮件标题'  
# 发送邮件方法
def sendEmail(content):
    # 内容, 格式, 编码
    message = MIMEText(content, 'plain', 'utf-8')  
    message['From'] = "{}".format(sender)
    message['To'] = ",".join(receivers)
    message['Subject'] = title
    try:
        # 启用SSL发信, 端口一般是465
        smtpObj = smtplib.SMTP_SSL(mail_host, 465)  
        # 登录验证
        smtpObj.login(mail_user, mail_pass)  
        # 发送
        smtpObj.sendmail(sender, receivers, message.as_string())  
        print("mail has been send successfully.")
    except smtplib.SMTPException as e:
        print(e)

# 设置ODPS参数
options.sql.settings = {'odps.sql.allow.fullscan': 'true'}
qcc_sql = """
SELECT DISTINCT * FROM 
(
    SELECT 
    case WHEN flag1=1 then '正常'
        ELSE concat(db_name,'中文乱码测试') end as result
        FROM 
    (select 
        t2.db_name,t2.code,COUNT(1) as flag1
    from ods_com_t_organization t2 
    where t2.is_company=1 
    and t2.is_deleted=0
    AND nvl(t2.code,'')!=''
    group by t2.db_name,t2.code
    )t1 
)t3
WHERE result != '正常'
;
"""
# 查询SQL获取结果
resdata = []
with o.execute_sql(qcc_sql).open_reader() as reader:
    resdata = [record.values for record in reader]
# 判断结果是否有数据，如无则正常，如有则发送邮件并报错退出
if resdata == []:
    print("数据正常")
else:
    result = "|".join(resdata[0])
    print(result)
    sendEmail(result)
    sys.exit(-1)