In this blog we discuss invalid dates in MySQL, 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
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,)]
2012-06-00 is converted by Connector/Python to Python’s
None. This is because
datetime.date does not allow invalid dates(http://www.python.org/doc//current/library/datetime.html#date-objects).
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
>>> 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
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.