Python 怎么捕获 MySQLdb.Warning ？

[iyaozhen]

Python 初学者，我在写一个基础类，有个导入文件进 MySQL 的方法，导入时不免会有一些 warning ，我想把 warning 捕获。然后返回给上层业务代码。

xxx.py:247: Warning: Data truncated for column 'cost' at row 2004
  cursor.execute(load_data_sql)
xxx.py:247: Warning: Row 2004 was truncated; it contained more data than there were input columns
  cursor.execute(load_data_sql)
xxx.py:247: Warning: Data truncated for column 'cost' at row 3519
  cursor.execute(load_data_sql)
xxx.py:247: Warning: Row 3519 was truncated; it contained more data than there were input columns
  cursor.execute(load_data_sql)
xxx.py:247: Warning: Data truncated for column 'cost' at row 5280
  cursor.execute(load_data_sql)
xxx.py:247: Warning: Row 5280 was truncated; it contained more data than there were input columns
  cursor.execute(load_data_sql)
xxx.py:247: Warning: Data truncated for column 'cost' at row 7034
  cursor.execute(load_data_sql)
xxx.py:247: Warning: Row 7034 was truncated; it contained more data than there were input columns
  cursor.execute(load_data_sql)
xxx.py:247: Warning: Data truncated for column 'cost' at row 8801
  cursor.execute(load_data_sql)
xxx.py:247: Warning: Row 8801 was truncated; it contained more data than there were input columns
  cursor.execute(load_data_sql)
……

我尝试了一下：

cursor = db.cursor()
load_data_sql = "LOAD DATA INFILE \'%s\' IGNORE INTO TABLE %s" \
                % (cleaned_file_path, table)
# 捕获 warn
warnings.filterwarnings('error')
warn = ''
try:
    cursor.execute(load_data_sql)
    db.commit()
except MySQLdb.Error as e:
    db.rollback()
    return {
        'status': -1,
        'message': "load data file sql \"%s\" error: %s" % (load_data_sql, e)
    }
except MySQLdb.Warning as e:
    return {
        'status': -2,
        'message': "load data file sql \"%s\" warning: %s" % (load_data_sql, e)
    }
else:
    return {
        'status': 0,
        'message': 'success'
    }
finally:
    db.close()

上层业务会打日志：

warning: Data truncated for column 'cost' at row 2004

貌似还行，除了忽略的行，其它数据都导入成功了。但总感觉代码有点 hack （ filterwarnings ），而且只能捕获到一条 Warning ，应该会有很多条的。

好像是 MySQLdb 库的原因，就只抛出一个 warn

[billgreen1]

遇到这种情况，推荐你修改 cost 字段的类型，使之能够处理更大范围的数据，而不是捕获异常。

[billgreen1]

如果你想捕获异常，试试用 logging 记录下来，而不是 return ，这样或许能出现多个 warning

[iyaozhen]

@billgreen1 嗯，这个我知道。我这个只是基础方法，目的就是暴露这些问题，上层业务好做修改。

使用 logging 记录好像不行，还是只会捕获到一条，就执行到 finally 那里了。

[pynix]

没有 warn 开关？

[gkiwi]

楼主后面不是 append 一个截图么，可以直接修改下源码看看能不能达到效果

[iyaozhen]

@gkiwi 到时上线不方便修改库的源码。本地是可以的。只能这样了，抛出一条也行。

[toontong]

sys.stderror