Re: SQLSoup results and zip

Hi Jonathan

I'm using the SQLAlchemy-0.3.1-py2.4.egg
not the one in Spyce
I was thinking it was the list comprehension or the zipping so i took  
those out
I also forgot the "order by" clause in the SQL string
Unfortunately it did not make much of a difference!!
========
from sqlalchemy.ext.sqlsoup import SqlSoup
from sqlalchemy import or_, and_,desc
import time

mydb=SqlSoup('postgres://cclarke:trinidad@localhost/carnival')

start = time.time()
sql= """select * from intraday where symbol ='RBTT' order by  
trade_date desc"""
rp = mydb.engine.execute(sql)
#x=zip(*rp.fetchall())
x=rp.fetchall()

timer = time.time() - start
print "Took %0.4f seconds using direct sql with engine"  % (timer)

start = time.time()
res=mydb.intraday.select(mydb.intraday.c.symbol=='RBTT',
                             order_by=[desc 
(mydb.intraday.c.trade_date)])
#dates = [row.trade_date for row in res]

timer = time.time() - start
print "Took %0.4f seconds using  mapper"  % (timer)
========
Results:
Evaluating sqlsoup.py
Took 0.3629 seconds using direct sql with engine
Took 1.1108 seconds using  mapper
 >>>

Regards
Chris

On 6 Feb 2007, at 12:40, Jonathan Ellis wrote:

> Wow, that's quite a hit.
>
> First thing I would suggest is, if you are using the SA that is  
> packaged
> with spyce, replace spyce/sqlalchemy with lib/sqlalchemy from the most
> recent SQLAlchemy distribution.  The one Spyce 2.1 includes doesn't  
> have
> a bunch of speed improvments from the latest 0.3 series.
>
> If it's still more than say 20% slower I would post to the SA list and
> see what Mike suggests.  (SqlSoup is a pretty thin wrapper, I'm 90%  
> sure
> that any speed problems are just reflecting the SA layer underneath.)
>
> On Tue, 6 Feb 2007 10:54:35 -0400, "Christopher Clarke"
> <cclarke-wYfHrJ5eQDBWk0Htik3J/[email protected]> said:
>> Hi Jonathan
>>
>> Yes this is what i meant instead of using the engine and going back
>> to writing SQL
>> Here is an example:
>> ------------------------------------------------------
>>      mydb=SqlSoup('postgres://cclarke:pasword@localhost/a_db')
>>
>>      start = time.time()
>>      sql= """select trade_date,high,low,open,close from intraday
>> where symbol ='RBTT'"""
>>      rp = mydb.engine.execute(sql)
>>      x=zip(*rp.fetchall())
>>      dates=x[0]
>>      timer = time.time() - start
>>      print "Took %0.4f seconds using direct sql with engine"  %  
>> (timer)
>>
>>      start = time.time()
>>      res=mydb.intraday.select(mydb.intraday.c.symbol=='RBTT',
>>                                  order_by=[desc
>> (mydb.intraday.c.trade_date)])
>>      dates = [row.trade_date for row in res]
>>
>>      timer = time.time() - start
>>      print "Took %0.4f seconds using  mapper"  % (timer)
>>
>>     results
>>     Took 0.3234 seconds using direct sql with engine
>>     Took 1.4522 seconds using  mapper
>> ----------------------------------------------
>> As expected there was a bit of performance hit in the second instance
>> But the map has other cool properties that i can use
>> Regards
>> Chris
>>
>>
>>
>> On 5 Feb 2007, at 18:15, Jonathan Ellis wrote:
>>
>>> On Mon, 5 Feb 2007 15:21:34 -0400, "Christopher Clarke"
>>> <cclarke-wYfHrJ5eQDBWk0Htik3J/[email protected]> said:
>>>> Hi All
>>>> I've taken the plunge and have started porting my database stuff to
>>>> sqlsoup
>>>> A problem i've encountered is that sometimes i need to get a  
>>>> "column"
>>>> from a list or results
>>>> so i do  a res=zip(*mycur.fetchall()) and res[0] is the first  
>>>> column
>>>> etc.
>>>> How do i replicate this in SQLSoup?
>>>> when i try   for example
>>>> res=mydb.intraday.select(mydb.intraday.c.symbol=='RBTT',
>>>>                                       order_by=[desc
>>>> (mydb.intraday.c.trade_date)])
>>>> zip(*res)  i get
>>>> TypeError: zip argument #1 must support iteration
>>>
>>> Is something like
>>>
>>> [row.columnname for row in res]
>>>
>>> what you are looking for?
>>


-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642