【python】mysql查询错误告警的处理

在用户进行操作数据异常，特别是在执行用例时响应值或者其他字段数据比较大或者其他异常时，写入数据库失败导致功能出现未知的异常，但是未能捕获该这些异常信息，是否可以写入数据库的异常信息的时候获取异常信息并给予相应的提醒

现在的mysql处理代码代码如下

import pymysql
class DBUtil(object):

    def __init__(self,config):
        self.connection = pymysql.connect(**config)
        self.cursor = self.connection.cursor()
    
    def operate(self,sql,params):
        """
        数据库操作
        :param sql:
        :param params:
        :return:
        """
        with self.connection.cursor() as cursor:
                cursor.execute(sql, params)
                self.connection.commit()

数据库配置config信息

conf = {
            "host": 'swapi-dev.sr.xxx.cn',  # 数据库服务器地址
            "port": 33333,
            "user": "swapi",
            "password": '123456',
            "db": 'swapi',  # 数据库名
            "charset": 'utf8',  # 数据库字符集
            "cursorclass": pymysql.cursors.DictCursor  # 返回值会变成字典格式
        }

如下这样的sql语句

_db_.operate('INSERT INTO environment_host222 (host,hostName) VALUES (%s,%s)', (
        'xxx',
        'abcd...省略10000个字符'))

出现写入失败，未有任意的提示信息
如果使用 try ... except 捕获异常呢？

import pymysql
import traceback
class DBUtil(object):

    def __init__(self,config):
        self.connection = pymysql.connect(**config)
        self.cursor = self.connection.cursor()
    
    def operate(self,sql,params):
        """
        数据库操作
        :param sql:
        :param params:
        :return:
        """
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params)
                self.connection.commit()
        except Exception as e:
            traceback.print_exc(e)

但是并未正常捕获到该异常，异常信息仅在控制台中打印

/Users/lluozh/work/git/SWQA_API/venv/lib/python3.7/site-packages/pymysql/cursors.py:323: Warning: (1265, "Data truncated for column 'hostName' at row 1")
  self._do_get_result()

如果捕获并处理异常信息呢？

import pymysql
from warnings import filterwarnings
filterwarnings("error",category=pymysql.Warning) #指定过滤告警的类别为pymysql.Warning类
# 动作为"error",该动作可以抛错，也可以使用try ... except 捕获
class DBUtil(object):

    def __init__(self,config):
        self.connection = pymysql.connect(**config)
        self.cursor = self.connection.cursor()
   
       def operate(self,sql,params):
        """
        数据库操作
        :param sql:
        :param params:
        :return:
        """
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(sql, params)
                self.connection.commit()
        except pymysql.Warning as e:
            abort(50009, str({'error':e,'sql':sql}))

即可捕获到异常信息

Traceback (most recent call last):
  File "/Users/lluozh/work/git/swapi/xyz/dbt.py", line 38, in <module>
    'xxx',
  File "/Users/lluozh/work/git/swapi/util/DBTool/dbUtil.py", line 73, in operate
    abort(505, str({'error':e,'sql':sql}))
  File "/Users/lluozh/work/git/swapi/venv/lib/python3.7/site-packages/werkzeug/exceptions.py", line 707, in abort
    return _aborter(status, *args, **kwargs)
  File "/Users/lluozh/work/git/swapi/venv/lib/python3.7/site-packages/werkzeug/exceptions.py", line 687, in __call__
    raise self.mapping[code](*args, **kwargs)
werkzeug.exceptions.HTTPVersionNotSupported: 505 HTTP Version Not Supported: {'error': Warning(1265, "Data truncated for column 'hostName' at row 1"), 'sql': 'INSERT INTO environment_host222 (host,hostName) VALUES (%s,%s)'}

ok。搞定，将异常信息捕获并微信通知到相关方即可