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
_______________________________________________
NumPy-Discussion mailing list
[email protected]
http://mail.scipy.org/mailman/listinfo/numpy-discussion
