[DB-SIG] Dealing with bogus date in MySql

[Andy Dustman]

Follow-up off-list or on the mysql-python sourceforge Help forum.

On 5/5/05, Mark Rages <markrages at gmail.com> wrote:
> Hi,
> 
> I hope this list can help me.
> 
> I have a MySQL table with some dates, like this:
> mysql> select MIN(change_date),MAX(change_date) from status_history;
> +---------------------+---------------------+
> | MIN(change_date)    | MAX(change_date)    |
> +---------------------+---------------------+
> | 2000-11-14 74:17:72 | 2005-05-05 17:17:43 |
> +---------------------+---------------------+
> 1 row in set (0.34 sec)
> 
> Notice that first date is kind of funky; most days don't have 72 hours.
> 
> I'm trying to get the information out with MySQLdb:
> self.conn=MySQLdb.connect(db=db,user=dbuser,host=dbhost,passwd=dbpass)
> self.cur=self.conn.cursor()
> self.cur.execute("select MIN(change_date),MAX(change_date) from status_history")
> print self.cur.fetchone()
> which gives:
> 
> (None, <DateTime object for '2005-05-05 17:17:43.00' at b7f90c98>)
> 
> Is there some way I can get the date with MySQLdb?  "None" just isn't
> helpful for my application.

MySQLdb is using Python datetime objects, and apparently they are
rejecting the bad date.

If you look at MySQLdb.pytimes, you'll see a DateTime_or_None()
function. This is the default converter for DATETIME columns. What you
can do is create a new function, DateTime_or_str(), which returns the
original string value instead of None, and then put this function in
the converter dictionary. MySQLdb.converters.conversions is where this
dictionary is built; study that for an example.

You can modify conversions directly, or modify a copy of it, and pass
it to connect() via the conv parameter.

Of course you have a new problem now: How to deal with the case where
your date comes back as a string instead of a datetime object.
-- 
Computer interfaces should never be made of meat.
http://www.terrybisson.com/meat.html