[DB-SIG] Dealing with bogus date in MySql

[M.-A. Lemburg]

Andy Dustman wrote:
> 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.

FWIW, the above repr() output indicates that it is using
mxDateTime objects and, yes, mxDateTime raises an exception for
bad dates or times.

> 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.

Wouldn't it be more appropriate to *correct* the wrong
date/time value in the database ?!

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 06 2005)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::