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

[Pierre GM]

On Sep 4, 2010, at 4:35 AM, John Salvatier wrote:

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


FAIW,
I have some pieces of code to connect ndarrays, MaskedArays and TimeSeries to sql3 databases (read from and write to).
http://projects.scipy.org/scikits/browser/branches/pierregm/hydroclimpy/scikits/hydroclimpy/io/sqlite.py