On Mon, Nov 10, 2008 at 11:10 AM, Michael Bayer
<[EMAIL PROTECTED]> wrote:
> you need an extra tuple on the join, query.join((q1, s.s==q1.c.s))
This gets past the syntax error, but does not produce the right
results. I had to take some time off today to work on other problems,
but am now returning to this query. To better take advantage of all
of your generous time :-) I wrote a free-standing example that
populates a test database. The initial query Simon suggested works
and produces the desired output -- the goal is to replicate this with
a sqlalchemy query. I also include the join we were working on. Now
that the syntax is correct, it runs, but gives the wrong output.
Since someone proposed a bowling example earlier I decided to run with
that since it fits my problem quite well: instead of finding the
number of symbols per strategy where the sum(pnl)<-150000, we are
looking for the number of bowlers per league where the
sum(frames)>200. Example below
Thanks for any additional input!
import datetime
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import func
Base = declarative_base()
class Frame(Base):
__tablename__ = 'frame'
league = sa.Column(sa.String(12), primary_key=True)
bowler = sa.Column(sa.String(12), primary_key=True)
frame = sa.Column(sa.Integer, primary_key=True)
score = sa.Column(sa.Integer)
def __init__(self, league, bowler, frame, score):
self.league = league
self.bowler = bowler
self.frame = frame
self.score = score
def __repr__(self):
return "Game('%s', '%s', '%d', '%d')"%(self.league,
self.bowler, self.frame, self.score)
def populate(session):
'add some random bowling data to the dbase'
import random
for league in 'strikers', 'punters', 'plungers', 'scorers':
for i in range(random.randint(3,10)):
bowler = chr(i+65)
for frame in range(1, 11):
score = random.randint(0,30)
session.add(Frame(league, bowler, frame, score))
session.commit()
if __name__=='__main__':
engine = sa.create_engine("sqlite:///test.db")
Base.metadata.bind = engine
Session = orm.sessionmaker()
session = Session(bind=engine)
Base.metadata.drop_all()
Base.metadata.create_all()
populate(session)
# this is what we are trying to achieve
query = """\
SELECT league, COUNT(*)
FROM (SELECT frame.league AS league,
frame.bowler AS frame_bowler,
sum(frame.score) AS sum_1
FROM frame
GROUP BY frame.league, frame.bowler
HAVING sum(frame.score) < 200) AS frames
GROUP BY league
"""
print 'desired', session.execute(query).fetchall()
# this is what Simon suggested
total_score = func.sum(Frame.score)
q1 = (session.query(Frame.league, Frame.bowler,
total_score).group_by(Frame.league,
Frame.bowler).having(total_score<200)).subquery()
q2 = (session.query(Frame.league, func.count('*')).join((q1,
Frame.league==q1.c.league)).group_by(Frame.league))
print q2
print q2.all()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
