In this blog we discuss invalid dates in MySQL (http://www.mysql.com" target="_blank), how to retrieve them using Connector/Python and we raise the question: Should Connector/Python raise an error or just keep returning None on invalid dates?

If you run MySQL without proper SQL Modes, you will be able to update and
read invalid dates such as ’2012-06-00′. If you’ve payed attention the past decade, you’ll know that you can prevent this configuring your MySQL server setting SQL Mode to ‘TRADITIONAL’.

Now, the problem if this is allowed, how do we get invalid dates using MySQL Connector/Python?

Lets look at an example inserting an invalid date and trying to read it again using MySQL Connector/Python:

>>> cur = cnx.cursor()
>>> cur.execute("INSERT INTO t1 VALUES ('2012-06-00')")
>>> cnx.commit()

mysql> SELECT * FROM t1;
+------------+
| date       |
+------------+
| 2012-06-00 |
+------------+

>>> cur.execute("SELECT * FROM t1")
>>> cur.fetchall()
[(None,)]

The date ’2012-06-00′ is converted by Connector/Python to Python’s None. This is because datetime.date does not allow invalid dates.

How to get the invalid dates back in your application?

You can use the raw-option for cursors and Connector/Python will return the date as a string instead of trying to convert to datetime.date.

>>> cur = cnx.cursor(raw=True)
>>> cur.execute("SELECT * FROM t1")
>>> cur.fetchall()
[('2012-06-00',)]

You are then responsible of parsing the text and do something usefull with it.

Question: what should Connector/Python do when it can’t convert the invalid dates? Returning a None is actually not really correct because if you would allow NULL in the MySQL table, you would also get None.

Should an invalid DATE value raise an error instead of returning None? Personally, I consider it a bug and I think it indeed should raise an error.