pyodps写入mysql分区表_dataworks中PYODPS2脚本数据库查询结果乱码处理

#-*- coding: utf-8 -*-

importdatetimeimportsys,timeimportsysfrom odps importoptionsimportsmtplibfrom email.header importHeaderfrom email.mime.text importMIMEText#设置编码(此设置可修复数据库查询结果中文乱码的问题)

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 = '邮件标题'

#发送邮件方法

defsendEmail(content):#内容, 格式, 编码

message = MIMEText(content, 'plain', 'utf-8')

message['From'] = "{}".format(sender)

message['To'] = ",".join(receivers)

message['Subject'] =titletry:#启用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.")exceptsmtplib.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 inreader]#判断结果是否有数据，如无则正常，如有则发送邮件并报错退出

if resdata ==[]:print("数据正常")else:

result= "|".join(resdata[0])print(result)

sendEmail(result)

sys.exit(-1)