Python 读取Mysql数据库，发送钉钉机器人消息（笔记）

数据库：mysql
告警信息库：qc_device_alarm
关联设备库：qc_device_devauth
机器人数据库：qc_system_talkclient
特定数据库环境，我自己记录一下，对读者参考性不大

#!/usr/bin/python
# -*- coding: utf-8 -*-
import pymysql
import json 
import time
import logging
import requests
import sys
 
username = "yk003"
verbose = False
mysqlhost="你自己的"
mysqluser="你自己的"
mysqlpassword="你自己的" 
mysqlport=3306
mysqldatabase="qcwulian"
mysqlcharset='utf8'
#发送钉钉消息
def ding_send(taoken,smscontent):
    webhook = "https://oapi.dingtalk.com/robot/send?access_token="+str(taoken)
    headers = {'Content-Type': 'application/json;charset=utf-8'}
    data = {
        "msgtype": "link",
        "link": {
            "text": "内容",
            "title": "标题",
            "picUrl": "图片连接",
            "messageUrl": "你需要发布的连接地址"
        }
    }
    r = requests.post(webhook,data = json.dumps(data),headers=headers)
    return r.text

#读取数据库中告警记录
def mysql_alarm():
    db = pymysql.connect(mysqlhost,mysqluser,mysqlpassword,port=mysqlport,database=mysqldatabase,charset=mysqlcharset)
    cursor = db.cursor(cursor =pymysql.cursors.DictCursor)
    
    jsonpayload=json.loads(payload)
    //读取告警数据库判断是否已经发送
    sql = """SELECT * FROM `qc_device_alarm` LEFT JOIN `qc_device_devauth` AS auth ON `qc_device_alarm`.`clientid` = `auth`.`clientid` WHERE `qc_device_alarm`.`status` =0  and  `qc_device_alarm`.`ding` =0""" 
    try: 
        cursor.execute(sql)
        result = cursor.fetchall()  
        if len(result)>0: 
          for row in result: 
             sql2= """SELECT * FROM `qc_system_talkclient`  WHERE `status` =1  and  `corp_id` =‘"""+str(result['corp_id'])+"""'""" 
             cursor.execute(sql)
             result_send = cursor.fetchall()
             if len(result_send)>0:
                  for row_send in result_send: 
                #UPDATE `qcwulian`.`qc_device_alarm` SET `status`=1 WHERE `id`=4;
                      ding_send(row_send['webhook'],result['describe'])
                  sql2 = """UPDATE qc_device_alarm SET ding=1,update_time="""+str(int(time.time()))+""" WHERE id="""+str(result['id']) 
                  try:
                    cursor.execute(sql2)
                     # 提交到数据库执行
                    db.commit()
                  except:
                     # 发生错误时回滚
                    db.rollback()
                   #print(str(sql2))

    except:
        print("Error: unable to fecth data")
    
    
    cursor.close()
    db.close()
 
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)