trapping a MySql warning

In my python script I would like to trap a "Data truncated for column 'xxx'" warning durnig my query using MySql.

I saw some posts suggesting the code below, but it doesn' work.

Do you know if some specific module must be imported or if some option/flag should be called before using this code?

Thanks all

Afeg

import MySQLdb
try:
    cursor.execute(s开发者_JS百科ome_statement)
    # code steps always here: No Warning is trapped
    # by the code below
except MySQLdb.Warning, e:
    # handle warnings, if the cursor you're using raises them
except Warning, e:
    # handle warnings, if the cursor you're using raises them

---

Warnings are just that: warnings. They get reported to (usually) stderr, but nothing else is done. You can't catch them like exceptions because they aren't being raised.

You can, however, configure what to do with warnings, and turn them off or turn them into exceptions, using the warnings module. For instance, warnings.filterwarnings('error', category=MySQLdb.Warning) to turn MySQLdb.Warning warnings into exceptions (in which case they would be caught using your try/except) or 'ignore' to not show them at all. You can (and probably should) have more fine-grained filters than just the category.

---

Raise MySQL Warnings as errors:

import warnings, MySQLdb
warnings.filterwarnings('error', category=MySQLdb.Warning)

To ignore instead of raising an error, replace "error" with "ignore".

Handle them in a try-except block like:

try:
    # a MySQL DB operation that raises a warning
    # for example: a data truncated warning
except Warning as a_warning:
    # do something here

---

I would try first to set the sql_mode. You can do this at the session level. If you execute a:

SET @@sql_mode:=TRADITIONAL;

(you could also set it at the server level, but you need access to the server to do that. and, that setting an still be overridden at the session level, so the bottom line is, always set it at the session level, immediately after establishing the connection)

then many things that are normally warnings become errors. I don't know how those manifest themselves at the python level, but the clear advantage is that the changes are not stored in the database. See: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_traditional

---

If you want to trap it to ignore it see "Temporarily suppressing warnings" in Python's documentation:

https://docs.python.org/2/library/warnings.html#temporarily-suppressing-warnings

import warnings

with warnings.catch_warnings():
    warnings.simplefilter("ignore")
    # Put here your query raising a warning

Else, just read the doc about "warnings" module of Python, you can transform them into exception if you want to catch them later, etc... it's all here: https://docs.python.org/2/library/warnings.html

---

Just to add to Thomas Wouters reply, there is no need to import the warnings module to turn them into errors. Just run your script with "-W error" (or ignore) as flag for Python.

---

Have you tried using MySQL's SHOW WARNINGS command?

---

Stop using MySQLdb. It has such stupid behavior as truncating data and issuing only a warning. Use oursql instead.