Today we received a bug report saying that we shouldn’t throw an exception but instead return what MySQLdb is returning. Bit research and MySQLdb is actually returning None for illegal dates: good!

There is now a fix (showing up soon) on Launchpad which will return dates as None where they are inserted as '0000-00-00'.

A few lines of Python:

..
data = [
    (datetime.now().date(),datetime.now()),
    ('0000-00-00','0000-00-00 00:00:00'),
    ('1000-00-00','9999-00-00 00:00:00'),
]
for d in data:
    stmt_insert = "INSERT INTO %s (c1,c2) VALUES (%%s,%%s)" % (tbl)
    try:
        cursor.execute(stmt_insert, d)
    except (mysql.connector.errors.InterfaceError, TypeError) as e:
    	print "Failed inserting %s\nError: %s\n" % (d,e)

    if cursor.warnings:
    	print cursor.warnings
..

The script outputs the following data, and notice also the warnings (SQL Mode set to NO_ZERO_IN_DATE,NO_ZERO_DATE):

[(u'Warning', 1265L, u"Data truncated for column 'c1' at row 1"),
 (u'Warning', 1264L, u"Out of range value for column 'c2' at row 1")]
[(u'Warning', 1265L, u"Data truncated for column 'c1' at row 1"),
 (u'Warning', 1264L, u"Out of range value for column 'c2' at row 1")]
(datetime.date(2009, 9, 30), datetime.datetime(2009, 9, 30, 15, 12, 23))
(None, None)
(None, None)

Another change we did today was returning a row as tuple, and rows as list of tuples.

Tip: use STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE SQL modes in MySQL 5.0 and later for new projects to make sure no illegal dates are inserted, or fix your application.