[Numpy-discussion] loading database data into numpy arrays

John Salvatier jsalvati@u.washington....
Thu Aug 19 15:58:09 CDT 2010


I found the cause of the original problem. pyodbc.cursor.next() returns a
pyodbc.Row object, which numpy does not seem to know how to deal with but is
essentially an extended tuple. Therefore in order to use the fromiter method
to make a numpy structured array from a mysql or other db query, you must
change the iterator slightly. I did the following:

The generator expression simply casts the Row object to a tuple.

cursor = conn.cursor()
cursor.execute('SELECT a, b FROM demo')

result = np.fromiter((tuple (row) for row in cursor), dtype=[('a',float),
('b', float)])

On Thu, Aug 19, 2010 at 10:23 AM, John Salvatier
<jsalvati@u.washington.edu>wrote:

> That worked! Thanks!
>
>
> On Thu, Aug 19, 2010 at 10:18 AM, Keith Goodman <kwgoodman@gmail.com>wrote:
>
>> On Thu, Aug 19, 2010 at 10:00 AM, John Salvatier
>> <jsalvati@u.washington.edu> wrote:
>> > Hello,
>> >
>> > I am trying to load some time series data into numpy arrays from a MySQL
>> > database using pyodbc, but I am not sure what the standard way to do
>> this
>> > is. I found the following:
>> > http://www.aidanfindlater.com/python-db-api-to-numpy but when I tried
>> after
>> > doing a select (I made sure the cursor had rows).
>> >
>> > np.fromiter(cursor, dtype=[('a',float), ('b', float)])
>> >
>> > I got
>> >
>> > Traceback (most recent call last):
>> >   File "<console>", line 1, in <module>
>> >   File "C:\Users\jsalvatier\workspace\rpxdata\src\rpxdata\mysql.py",
>> line
>> > 45, in run_statistics_15
>> >     return np.fromiter(cursor, dtype=[('a',float), ('b', float)])
>> > TypeError: expected a readable buffer object
>> >
>> > Does anyone have advice on this?
>>
>> How about np.asarray(cursor.fetchall())?
>> _______________________________________________
>> NumPy-Discussion mailing list
>> NumPy-Discussion@scipy.org
>> http://mail.scipy.org/mailman/listinfo/numpy-discussion
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.scipy.org/pipermail/numpy-discussion/attachments/20100819/a42b487c/attachment-0001.html 


More information about the NumPy-Discussion mailing list