current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
2

Starting with anwser of this:

Using Numpy to create Yahoo finance price table

import numpy as np
import pylab as pl
import urllib
url = "http://ichart.yahoo.com/table.csv?a=2&c=2011&b=30&e=7&d=7&g=d&f=2011&s=msft&ignore=.csv"
f = urllib.urlopen(url)
title = f.readline().strip().split(",")
data = np.loadtxt(f, dtype=np.float, delimiter=",", converters={0: pl.datestr2num})

I would like to insert return rows into db. data looks like below:

 [[734233.0 25.98 26.31 25.86 26.15 65581400 25.98]
 [734232.0 25.82 26.18 25.74 25.78 73694500 25.61]
 [734231.0 25.45 25.66 25.41 25.55 35433700 25.38]
 [734228.0 25.53 25.53 25.31 25.48 63114200 25.31]
 [734227.0 25.60 25.68 25.34 25.39 63233700 25.22]
 [734226.0 25.60 25.72 25.50 25.61 41999300 25.44]]

How would I parse this numpy array to a list or table so I can insert into database. Notice that all row are not separated, but rather one line. The db part works.

data.tolist() does not parse single rows

looking for output like 

    [[734233.0 ,25.98 ,26.31 ,25.86 ,26.15, 65581400, 25.98]  
     [734232.0, 25.82, 26.18, 25.74, 25.78, 73694500, 25.61]
     [734231.0, 25.45 ,25.66, 25.41, 25.55, 35433700, 25.38]
     [734228.0, 25.53, 25.53, 25.31, 25.48, 63114200, 25.31]
     [734227.0, 25.60 ,25.68, 25.34, 25.39, 63233700, 25.22]
     [734226.0, 25.60, 25.72, 25.50, 25.61, 41999300, 25.44]]

Would replace " " with "," work?

share|improve this question
2  
It's not clear here what exactly you want to do. What do you mean by "Notice that all row are not separated, but rather one line."? I am unable to parse your question. –  Chinmay Kanchi Aug 12 '11 at 16:27
    
Why don't you just save the data that came from the yahoo directly to the database? Then when you want to do something with it you convert it to a numpy array. –  Alex Plugaru Aug 12 '11 at 16:29
    
@CK [734226.0 25.60 25.72 25.50 25.61 41999300 25.44] needs to be parsed. somelike [734226.0 ,25.60, 25.72, 25.50, 25.61, 41999300, 25.44] so the db will data in row a seven elements vs 1 element –  Merlin Aug 12 '11 at 16:31
    
@AP because work is done on array but not shown is above code. –  Merlin Aug 12 '11 at 16:33
    
I've attempted to answer, but I honestly don't get what you're on about. On my system, the code above produces a numpy.ndarray of the right number of dimensions. –  Chinmay Kanchi Aug 12 '11 at 16:38

2 Answers 2

up vote 1 down vote 
>>> import sqlalchemy as sa
>>> import numpy as np
>>> import time, datetime
>>> import urllib

conversions to and from date formats.

>>> datestr2timestamp = lambda d: time.mktime(time.strptime(d,"%Y-%m-%d"))
>>> def npvector_to_sadict(vector):
...     row = dict(zip(("open", "high", "low", "close", "volume", "adj_close"),
...                        vector[1:]))
...     row['date'] = datetime.date.fromtimestamp(vector[0])
...     return row
...

Load the data from the net resource:

>>> url = "http://ichart.yahoo.com/table.csv?a=2&c=2011&b=30&e=7&d=7&g=d&f=2011&s=msft&ignore=.csv"
>>> f = urllib.urlopen(url)
>>> title = f.readline().strip().split(",")
>>> data = np.loadtxt(f, dtype=np.float, delimiter=",", converters={0: datestr2timestamp})

define what the database table looks like

>>> metadata = sa.MetaData()
>>> stockdata = sa.Table('stockdata', metadata,
...                      sa.Column('date', sa.Date),
...                      sa.Column('open', sa.Float),
...                      sa.Column('high', sa.Float),
...                      sa.Column('low', sa.Float),
...                      sa.Column('close', sa.Float),
...                      sa.Column('volume', sa.Float),
...                      sa.Column('adj_close', sa.Float))

connect to the database. you can change this to mysql://user:password@host/ for mysql databases

>>> engine = sa.create_engine("sqlite:///:memory:")

only for demonstration, skip this if you already have the table created.

>>> metadata.create_all(engine)

insert the data into the database:

>>> engine.execute(stockdata.insert(), [npvector_to_sadict(datum) for datum in data])
<sqlalchemy.engine.base.ResultProxy object at 0x23ea150>

verify that it was inserted

>>> print data.shape[0], engine.execute(sa.select([sa.func.count(stockdata.c.close)])).scalar()
90 90
>>>
share|improve this answer
    
thanks, looking at what you did. –  Merlin Aug 12 '11 at 20:50
    
So for every numpy array, I would use sqlalchemy to insert into database. I will work on understand above. But, there has to be a simpler way to transform a np array into list or tuple to insert into a db. I will have a lot use case of inserting np arrays into db. –  Merlin Aug 12 '11 at 21:06
    
You might be able to convert numpy arrays into a binary format and use a database specific bulk insert operation, but that kind of approach will always be fragile and non-portable. Using SQLAlchemy in this way gives you portability, and makes it so you can work with database primitives as easily ans numpy allows you to work with in memory arrays. –  SingleNegationElimination Aug 12 '11 at 22:14
    
Even if its fragile, I could use it. not looking at portablity. what would the code look like ? The other poster (ck) suggests it simple, but I cant figure it out. –  Merlin Aug 14 '11 at 14:36
up vote 1 down vote

So you have a row that's a string that looks like "[734226.0 25.60 25.72 25.50 25.61 41999300 25.44]" and you want to convert that into a list with the individual values? Something like this should do the trick:

my_string = "[734226.0 25.60 25.72 25.50 25.61 41999300 25.44]"
my_list = [float(s) for s in my_string[1:-1].split(' ')]

Why can't you just do:

for row in data:
    print row #do whatever you want with row here.
share|improve this answer
    
Yes, that work for a one line. How would I do that for whole array. –  Merlin Aug 12 '11 at 16:46
2  
Umm... use a for loop? –  Chinmay Kanchi Aug 12 '11 at 16:53
    
It's perfectly straightforward. You shouldn't even need a for loop though, since the array already exists... –  Chinmay Kanchi Aug 13 '11 at 20:02
1  
If you not going to answer the question, you can stop post comments. –  Merlin Aug 14 '11 at 14:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.