Geert JM Vanderkelen

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.