腾讯机器人转发数据库查询结果

最新推荐文章于 2024-07-23 18:35:18 发布
时间城-最光阴
最新推荐文章于 2024-07-23 18:35:18 发布
阅读量122
点赞数
文章标签：机器人数据库 python
本文链接：https://blog.csdn.net/qq_34247077/article/details/130510521
版权
# coding=UTF-8
# Doris数据监控任务
import MySQLdb
import sys
import os
import logging 
import datetime
import requests
import xlwt

reload(sys)
sys.setdefaultencoding('utf-8')

# from prettytable import PrettyTable


'''
host：主机名
port：端口名
user：用户
passwd：密码
webhook：企微机器人
'''

static_config = {
    'host': '###.ads.aliyuncs.com',
    'port': 3306,
    'user': '##',
    'passwd': '###',
    'webhook': 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=####',
}

today = datetime.datetime.now().date()
today_str=str(today)
print(today_str)

dirpath = {
    'dirpath':'/tmp/jiankong/zzq',
    'filepath':'/tmp/jiankong/zzq/{0}_shujucy.xls'.format(today_str)
}

print('文件路径为:',dirpath['filepath'])
filename=dirpath['filepath'].split('/')[-1]
print('文件名称:',filename)

class MySQLConnect:
    def __init__(self, host, port, user, passwd):
        self.host = host
        self.port = port
        self.user = user
        self.passwd = passwd

    def get_open_connect(self):
        '''
        获取doris数据连接
        :return: con连接对象
        '''
        con = MySQLdb.connect(host=self.host, port=int(self.port), user=self.user, passwd=self.passwd,
                              read_timeout=3600, charset='utf8')
        return con

    def get_close_connect(self, con, cur):
        '''
        关闭数据库连接对象
        :param con: 数据库连接
        :param cur: 游标
        :return:
        '''
        try:
            cur.close()
            con.close()
            logging.info(">>>>>>>>>>>>>> 连接关闭成功")
        except Exception as e:
            logging.error(">>>>>>>>>>>>>> 连接关闭失败")
            raise e

if __name__ == '__main__':

         # 创建连接对象
         mysqlConnect=MySQLConnect(host=static_config['host'],port=static_config['port'],user=static_config['user'],passwd=static_config['passwd'])
         print('创建连接成功')

         con=mysqlConnect.get_open_connect()

         cur = con.cursor()

         # 定义查询开始日期
         begin_date=sys.argv[1]

         # 定义查询结束日期
         end_date =sys.argv[2]

         select_sql='''
            select
             business_date  -- '营业日期'
            ,store_id       -- '门店编码'
            ,store_name     -- '门店名称'
            ,ctype          -- '差异类型:(订单差异:01日结差异:02)'
            ,order_id       -- '差异订单号'
            ,sales_amt      -- 'BI订单金额'
            ,settle_amt     -- '业务订单金额'
            ,online_flag    -- '线上线下标识(线上:Y 线下:N)'
            ,reason         -- '异常原因'
            ,is_repair      -- '是否修复(是:Y 否:N)'
            ,tj_time        -- '最后更新时间'
            ,sale_cy        -- '差异金额'
            ,responsible    -- '处理方'
            from rpt_db.store_abnormal_order_analysis
            where business_date>='{begin_date}' and business_date<='{end_date}'  and coalesce(is_repair,'N') = 'N'  order by business_date asc 
         '''
         update_sql1='''
            UPDATE rpt_db.store_abnormal_order_analysis SET reason='需重新核对,is_repair='N',responsible='业务'
            WHERE business_date>='{begin_date}' and business_date<='{end_date}' and order_id='-' and  ctype='02' AND ABS(sale_cy)>=0.5 AND LENGTH(coalesce(reason,''))<1;  
         '''
         update_sql3='''
            UPDATE rpt_db.store_abnormal_order_analysis SET reason='小数点' ,is_repair='Y',responsible='无需处理'
            WHERE business_date>='{begin_date}' and business_date<='{end_date}' and order_id='-' and  ctype='02' AND ABS(sale_cy)<0.5  AND LENGTH(coalesce(reason,''))<1;
         '''

        # 预先修改 原表中状态 格式化sql
         update_sql3=update_sql3.format(begin_date=begin_date,end_date=end_date)

         print(update_sql3)

         try:
             # 修改 表状态
             cur.execute(update_sql3)
             con.commit()
         except Exception as e:
             print('小数点原因初步判断更改失败！')
             raise e

         # 预先修改 原表中状态 格式化sql
         update_sql1=update_sql1.format(begin_date=begin_date,end_date=end_date)

         print(update_sql1)

         try:
             # 修改 表状态
             cur.execute(update_sql1)
             con.commit()
         except Exception as e:
             print('日结原因初步判断更改失败！')
             raise e


         # sql格式化
         select_sql=select_sql.format(begin_date=begin_date,end_date=end_date)
         print(select_sql)

         cur.execute(select_sql)

         # 创建对象 封装 结果值
         # resultSQL=ResultSQL()

         resultSQLlist=[]
         core_table = cur.fetchall()

         for core_row in core_table:
                business_date=str(core_row[0])
                store_id=str(core_row[1])
                store_name=str(core_row[2])
                ctype=str(core_row[3])
                order_id=str(core_row[4])
                sales_amt=str(core_row[5])
                settle_amt=str(core_row[6])
                online_flag=str(core_row[7])
                reason=str(core_row[8])
                is_repair=str(core_row[9])
                tj_time=str(core_row[10])
                sale_cy=str(core_row[11])
                responsible=str(core_row[12])
                content=business_date+','+store_id+','+store_name+','+ctype+','+order_id+','+sales_amt+','+settle_amt+','+reason+','+sale_cy+','+responsible
                # 蒋结果存放到 对象列表
                resultSQLlist.append(content)
         mysqlConnect.get_close_connect(con=con,cur=cur)
         print('查询结果集如下:')
         print(resultSQLlist)

         len=resultSQLlist.__len__()
         wide=resultSQLlist[0].__sizeof__()
         print('结果集长度'+str(len))

         # 创建 excel 对象
         book = xlwt.Workbook(encoding='utf-8',style_compression=0)
         # 设置表单名称
         sheet = book.add_sheet('日结差异表',cell_overwrite_ok=True)
          # 设置列名
         col=['营业日期', '门店编码', '门店名称', '差异类型','差异订单号','BI日结金额','中台日结金额','差异原因','差异金额','责任方']
         for i in range(0,len):
               data=resultSQLlist[i].split(',')
               for j in range(0,10):
                    print(data[j])
                    # 写入表头数据 行 列 列值
                    sheet.write(0,j,col[j])
                    # 写入查询的其余数据 从第二行开始写入
                    sheet.write(i+1,j,data[j])
      
         try:
             os.makedirs(dirpath['dirpath'])
         except Exception:
             print('文件已存在')
         try:
             os.remove(dirpath['filepath'])
             print('删除文件成功')
         except Exception:
             print('文件不存在')
         savepath=dirpath['filepath']
         filename=filename
         book.save(savepath)
         oFileUploadUrl = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/upload_media?key=###&type=file'
         oWX_URL = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/upload_media?key=###'
         payload={'Content-Disposition': 'form-data','name': 'media','filename': filename}
         files=[('boundary', ('%s' % filename, open('%s' % savepath, 'rb'), 'application/xls'))]
         headers={}
         try:
            response = requests.request("POST", oFileUploadUrl, headers=headers, data=payload, files=files,timeout=5)
         except Exception as e:
               print(e.args[0])
         json_res = response.json()
         print('返回的结果为',json_res)
         media_id = json_res['media_id']
         if media_id != "":
             data = {"msgtype": "file",
                     "file": {
                         "media_id": media_id
                     }
                     }
             headers = {'Content-Type': 'application/json'}  # 指定提交的是json
             try:
                 response = requests.post(
                     url=oWX_URL, json=data, headers=headers, timeout=5)
             except Exception as e:
                 print(e.args[0])

         mBody = {
             "msgtype": "file",
             "file": {
                 "media_id":media_id
             }
         }
         mHeader = {'Content-Type': 'application/json; charset=UTF-8'}
         requests.post(url=static_config['webhook'], json=mBody, headers=mHeader)