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

Question

I'm reading in various datatypes from a mySQL database. The fifth column has type 'DATETIME' in the database. I use that as the 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 in the mySQL server:

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

The code functions as expected unless the time in the database is midnight, like so:

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

In that case, and that case only, I get this error when comparing the record's entry_date.date() to 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 record.entry_date confirms that the time attribute is gone for this case:

'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 datetime, but I'm wondering if there is a better fix than this.

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

Thanks for your help!

Answer 1

I would ensure that you have a datetime object as soon as you extract it from the database. Then you don't have to do any checks in the future. So you could say:

entryDate = ensure_datetime(result[5])

which is only a little extra code and also has the advantage that if your query changes and you don't correctly update the code after it'll catch type errors immediately. Here's 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 sense you don't want to have to do this, so I'll sweeten it as follows:

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 remember to do anything.

Answer 2

I just had a similar problem recently.

I it was fixed by doing db_cursor = db_con.cursor(raw=True), according to the MySQL Python connector reference docs.

But the results were returned as a list/lists with elements as bytearray(b'data'), thus to bring the data back to usable friendly format, I had to do:

db_cursor.execute(some_sql)

query_result = db_cursor.fetchall()

...

for data_row in query_result:
    tempList = [x.decode('utf-8') if x != None else x for x in data_row]

then do something with tempList, but guaranteed that the datetime data type from the database came as I expected, for example, in my case I had two columns with expected values '2020-10-30', '03:09:03', 'AM'.

Further reading on the reference docs site reveals why there is an option between switching the data type conversion on or off.

I hope this helps open doors to more feasible solutions.

Answer 3

I met same problem and created a PR for it https://github.com/mysql/mysql-connector-python/pull/83

Not sure if it will be accepted, hope this can help.