Python 怎么捕获 MySQLdb.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 ，应该会有很多条的。

第 1 条附言 · 2015-12-11 01:11:39 +08:00

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

7 条回复 • 2016-03-04 14:48:30 +08:00

billgreen1

2015-12-10 23:48:08 +08:00

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

billgreen1

2015-12-10 23:49:58 +08:00

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

iyaozhen

2015-12-10 23:54:29 +08:00

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

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

pynix

2015-12-11 05:54:34 +08:00

没有 warn 开关？

gkiwi

2015-12-11 09:10:37 +08:00

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

iyaozhen

2015-12-11 12:08:14 +08:00

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

toontong

2016-03-04 14:48:30 +08:00

sys.stderror