[Numpy-discussion] Add function for creating recarray from database query?

[John Salvatier]

Hello,

I recently had to get data from a mysql database into a recarray. The result
was not very long but nontrivial to figure out:

def recarray_from_db(db, command):
    """ executes a command and turns the results into a numpy recarray
(record array)"""
    cursor = db.cursor()
    cursor.execute(command)

    column_dtypes = [(col[0], _convert(col[1])) for col in
cursor.description]

    return np.fromiter((tuple (row) for row in cursor), dtype=column_dtypes,
count = cursor.rowcount)

_type_conversions = {decimal.Decimal : float}
def _convert(type):
    try :
        return _type_conversions[type]
    except:
        return type


It uses only the Python database API. Would something like this be a useful
addition to numpy (maybe have it take a cursor object instead of a
connection object)?

I also found the following function useful for results with timedate
columns, since numpy does not yet have a datetime dtype.

def time_column(date, column_name):
    """ makes a string for calculating a time as (decimal) number of days
since a date for a MySQL column. This is because the numpy.datetime datatype
is not well developed."""
    return "TIME_TO_SEC(timediff(" + column_name + ",'" +
str(datetime.strptime(date, date_format)) + "'))/(60*60*24) as " +
column_name
date_format = '%Y-%m-%d'


Best Regards,
John
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/numpy-discussion/attachments/20100903/3c02f77b/attachment.html>