How can I stop python from converting mySQL DATETIME to datetime.date when the time is 00:00:00?

Question

I read in different types of data from mySQL database. The fifth column is of type DATETIME in the database. I use this as an entry_date for a BloodTraitRecord object.

import mysql.connector from datetime import timedelta from datetime import datetime show_DB = """select RUID, test_sname, test_value, units, ref_range, entry_date from %s where RUID=%%s and test_sname=%%s order by RUID, test_sname, entry_date Limit 5;""" % (tableToUse,) cursor.execute(show_DB, (ruid, traitPair[0])) resultsForOneTrait = cursor.fetchall() for result in resultsForOneTrait: ruid = result[0] s_name = result[1].decode("UTF-8") value = result[2] units = result[3].decode("UTF-8") ref_range = result[4].decode("UTF-8") # Need assistance here entryDate = result[5] record = BloodTraitRecord(ruid, s_name, value, units, ref_range, entryDate) 

BloodTraitRecord Class:

 class BloodTraitRecord: def __init__(self, ruid, test_sname, test_value, units, ref_range, entry_date): self.RUID = ruid self.test_sname = test_sname self.test_value = test_value self.units = units self.ref_range = ref_range self.entry_date = entry_date 

DATETIME objects from the database look like this on the mySQL server:

 '2008-11-14 13:28:00' 

The code functions as expected if the time in the database is not midnight:

 '2014-05-18 00:00:00' 

In this case, and only in this case, I get this error when comparing the entry_date.date () record with another datetime.date later in the code:

 # 'cancerCutoff' is consistently a datetime.date cancerCutoff = firstCancerAnemiaCodeDate[ruidkey] - timedelta(days=180) if cancerCutoff < record.entry_date.date(): AttributeError: 'datetime.date' object has no attribute 'date' 

Printing the .entry_date record confirms that for this case the time attribute is missing:

 '2014-05-18' 

I have a way to fix this by checking the type of the object and only calling the date attribute if the object is a date-time, but I am wondering if there is a better fix than this.

I also don't understand why python immediately converts MySQL DATETIME to datetime.date when the DATETIME time is 00:00:00.

Thank you for your help!

Answer 1

I would make sure you have a datetime object as soon as you retrieve it from the database. Then you do not need to do any checks in the future. Therefore you can say:

 entryDate = ensure_datetime(result[5]) 

which is just a little extra code, and also has the advantage that if your request changes and you incorrectly update the code after it immediately catches type errors. Here is an example implementation:

 from datetime import datetime, date # Thanks to http://stackoverflow.com/a/1937636/2482744 def date_to_datetime(d): return datetime.combine(d, datetime.min.time()) def ensure_datetime(d): if isinstance(d, datetime): return d elif isinstance(d, date): return date_to_datetime(d) else: raise TypeError('%s is neither a date nor a datetime' % d) 

Demo:

 for x in [date(2016, 5, 12), datetime(2016, 5, 12, 9, 32, 57, 345), 'a string']: print(ensure_datetime(x)) 

Output:

 2016-05-12 00:00:00 2016-05-12 09:32:57.000345 Traceback (most recent call last): File "/Users/alexhall/Dropbox/python/sandbox/sandbox.py", line 14, in <module> print(ensure_datetime(x)) File "/Users/alexhall/Dropbox/python/sandbox/sandbox.py", line 9, in ensure_datetime raise TypeError('%s is neither a date nor a datetime' % d) TypeError: a string is neither a date nor a datetime 

But I feel that you do not want to do this, so I will sweeten it like this:

 def clean_types(row): new_row = [] for item in row: if isinstance(item, date) and not isinstance(item, datetime): item = date_to_datetime(item) elif isinstance(item, str): item = item.decode("UTF-8") new_row.append(item) return new_row # Demo print(clean_types([3, 'abc', u'def', date.today(), datetime.now()])) # [3, u'abc', u'def', datetime.datetime(2016, 5, 12, 0, 0), datetime.datetime(2016, 5, 12, 17, 22, 7, 519604)] 

Now your code can be shortened to:

 for result in resultsForOneTrait: record = BloodTraitRecord(*clean_types(result)) 

and you don’t have to do anything.