how to convert from MySQL datetime to numpy datetime64?

Question

I am loading a cursor into a numpy array as a precursor to using pyplot.

I have not been successful in converting the datetime field from Mysql into the datetime64 that numpy uses.

Python code

@login_required
def resource(request, resource_id):
    cnxn = pyodbc.connect(SOLAR_SECURED_CONNECTION)
    cursor = cnxn.cursor()

    sqlstr = """select r.name as resource_name, rm.mobile_id_id, fd.value, pd.update_time, rt.capacity
                    from asset_monitor_resource r, asset_monitor_formulateddata fd, asset_monitor_resourcetype rt,
                         asset_monitor_parseddata pd, asset_monitor_resourcemapping rm
                    where r.id = rm.resource_id_id and
                          r.id = fd.resource_id_id and
                          fd.rawdata_id_Id = pd.rawdata_id_id and
                          r.resource_type_id_id = rt.id and
                          r.id = ?
                    order by pd.update_time desc"""

    cursor.execute(sqlstr, resource_id)

    data_type = [('resource_name', np.str, 100),
                          ('mobile_id_id', np.int8),
                          ('value', np.float),
                          ('update_time', np.datetime64),
                          ('capacity', np.int8)]

    r_rows = cursor.fetchall()
    for r in r_rows:
        dt = r.update_time
        r.update_time = np.datetime64(dt).astype(datetime)

    narray = np.fromiter((tuple(row) for row in r_rows), dtype=data_type)

    print 'narray='+str(narray)

    dateplot(narray, resource_id)
    image_file = "tempfig"+str(resource_id)+".png"

    return render_to_response('resource.html', {'data': r_rows, 'resource_id': resource_id, 'image_file': image_file}, context_instance=RequestContext(request))

traceback

[Tue Dec 03 10:56:45 2013] [error] Internal Server Error: /user/resource/113/
[Tue Dec 03 10:56:45 2013] [error] Traceback (most recent call last):
[Tue Dec 03 10:56:45 2013] [error]   File "C:\\Python27\\Lib\\site-packages\\django\\core\\handlers\\base.py", line 115, in get_response
[Tue Dec 03 10:56:45 2013] [error]     response = callback(request, *callback_args, **callback_kwargs)
[Tue Dec 03 10:56:45 2013] [error]   File "C:\\Python27\\Lib\\site-packages\\django\\contrib\\auth\\decorators.py", line 25, in _wrapped_view
[Tue Dec 03 10:56:45 2013] [error]     return view_func(request, *args, **kwargs)
[Tue Dec 03 10:56:45 2013] [error]   File "C:\\dev\\solar_secured\\asset_monitor\\views.py", line 319, in resource
[Tue Dec 03 10:56:45 2013] [error]     narray = np.fromiter((tuple(row) for row in r_rows), dtype=data_type)
[Tue Dec 03 10:56:45 2013] [error] TypeError: Cannot cast datetime.datetime object from metadata [us] to  according to the rule 'same_kind'

I think it was answered in [this post](http://stackoverflow.com/questions/13703720/converting-between-datetime-timestamp-and-datetime64) — Razvan Tudorica, Dec 03 '13 at 15:49
ok. I added code to convert the last_update field as specified in that post, but I am still getting the same error. I have updated the code and traceback — Adam, Dec 03 '13 at 16:00

score 2 · Accepted Answer · answered Dec 03 '13 at 16:34

You can use explicit unit specification in datetime data type:

import datetime

row = ['resource', 1, 1.1, datetime.now(), 10]
r_rows = [row]

data_type = [('resource_name', np.str, 100),
             ('mobile_id_id', np.int8),
             ('value', np.float),
             ('update_time', '<M8[us]'),
             ('capacity', np.int8)]

narray = np.fromiter((tuple(row) for row in r_rows), dtype=data_type)

results in

>>> narray['update_time'][0]
numpy.datetime64('2013-12-03T16:27:05.456766+0000')

how to convert from MySQL datetime to numpy datetime64?

1 Answers1