Good afternoon,
The following works:
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'" for row in cursor.execute(request): price.append([row[0], row[1]]) priceArray = np.array(price)
Is there a better way to do this? Can fromiter be used?
Could this query result be put into a wider array from the start such that columns 2... could be filled with calcs later?
Thanks in advance,
jab
On 1/31/07, BBands bbands@gmail.com wrote:
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'" for row in cursor.execute(request): price.append([row[0], row[1]]) priceArray = np.array(price)
Is there a better way to do this? Can fromiter be used?
Why not just pull it all at once with cursor.fetchall()? Then use np.asarray on the result.
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')])
On 1/31/07, BBands bbands@gmail.com wrote:
Good afternoon,
The following works:
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'" for row in cursor.execute(request): price.append([row[0], row[1]]) priceArray = np.array(price)
Is there a better way to do this? Can fromiter be used?
Could this query result be put into a wider array from the start such that columns 2... could be filled with calcs later?
Thanks in advance,
jab-- John Bollinger, CFA, CMT www.BollingerBands.com
If you advance far enough, you arrive at the beginning. _______________________________________________ Numpy-discussion mailing list Numpy-discussion@scipy.org http://projects.scipy.org/mailman/listinfo/numpy-discussion
On 1/31/07, Tom Denniston tom.denniston@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?
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@gmail.com wrote:
On 1/31/07, Tom Denniston tom.denniston@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@scipy.org http://projects.scipy.org/mailman/listinfo/numpy-discussion
-
BBands -
Keith Goodman -
Tom Denniston