[Numpy-discussion] query to array

[Tom Denniston]

Ok, I don't have the pyodbc so I can't test this example but it would
look something like this assuming the date was a 10 char string (numpy
doesn't support mx.DateTimes natively, much to my chagrin) and the
Close was a float:

> import pyodbc
> import numpy as np
>
> connection = pyodbc.connect('DSN=DSNname')
> cursor = connection.cursor()
> symbol = 'ibm'
> request = "select to_days(Date), Close from price where symbol = '" +
> symbol + "' and date > '2006-01-01'"
> dtype = numpy.dtype([('Date', '|S10'), ('Close', float)])
> resultIter = cursor.execute(request)
> arr = numpy.fromiter(resultIter, dtype)

As I said I cant test this because I don't have the lib, but it should
work fine.

Finally, the '|S10' is the string encoding for 10 character wide
string.  Others might know of a more elegant way to express this.

--Tom



On 1/31/07, Keith Goodman <kwgoodman at gmail.com> wrote:
> On 1/31/07, Tom Denniston <tom.denniston at alum.dartmouth.org> wrote:
> > i would do something like the following.  I don't have your odbc
> > library so I mocked it up with a fake iterator called "it".  This
> > example would be for a two column result where the first is an int and
> > the second a string.  Note it creates a recarray which you can have
> > match you database column names if you want to generalize the
> > function.  All you need to provide is the dtype equivalent to your
> > query result layout.
> >
> > In [2]: import numpy
> >
> > In [3]: it = iter([(1,'String1'), (2, 'String2')])
> >
> > In [4]: dtype = numpy.dtype([('intvalue', int), ('stringcolumn', '|S20')])
> >
> > In [5]: numpy.fromiter(it, dtype)
> > Out[5]:
> > array([(1, 'String1'), (2, 'String2')],
> >       dtype=[('intvalue', '<i4'), ('stringcolumn', '|S20')])
>
> All this fromiter and recarray is confusing to me. But I'd like to learn.
>
> How would I hook all this up to a query?
> _______________________________________________
> Numpy-discussion mailing list
> Numpy-discussion at scipy.org
> http://projects.scipy.org/mailman/listinfo/numpy-discussion
>