Hello Alchemy Land!
If I have a simple test-case with Bowler objects and City objects, and
I want to use func.max and group_by in order to find the highest
scorers in each city... I might do something like this:
max_score = func.max(Bowler.highscore).label('highest_score')
results = session.query(Bowler,
max_score).group_by(Bowler.city_id).all()
So this works as I'd expect, and 'results' now contains
(Bowler,max_score) tuples... but what I really want is to have a
query that just returns Bowler objects, and not these tuples. Is
there a way to get rid of that 'max_score' column from the result
set? I've been at this for hours, I bet it's really simple but I just
can't find it.
please help!!!
Full example
----------------------
# STANDARD BOILERPLATE
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///:memory:', echo=True,
strategy='threadlocal')
Session = scoped_session(sessionmaker(autoflush=False,
autocommit=False))
session = Session(bind=engine)
metadata = ThreadLocalMetaData()
metadata.bind = engine
# DEFINE TABLES
bowlers_table = Table('bowler', metadata,
Column('bowler_id', Integer, primary_key=True),
Column('name', String(50)),
Column('highscore', Integer, default=0),
Column('city_id', None, ForeignKey('city.city_id'))
)
cities_table = Table('city', metadata,
Column('city_id', Integer, primary_key=True),
Column('name', String(50))
)
metadata.create_all()
# DEFINE CLASSES
class Base(object):
def __init__(self, **kwargs):
for key, value in kwargs.iteritems():
setattr(self, key, value)
class Bowler(Base):
pass
class City(Base):
pass
# MAP CLASSES
mapper(City, cities_table)
mapper(Bowler, bowlers_table, properties={
'city': relation(City)
})
# CREATE SAMPLE DATA
nyc = City(name="New York City")
michael_bayer = Bowler(name="Michael Bayer", highscore=299, city=nyc)
big_lebowski = Bowler(name="Jeffrey Lebowsky", highscore=170,
city=nyc)
cle = City(name="Cleveland")
ian_charnas = Bowler(name="Ian Charnas", highscore=220, city=cle)
the_jesus = Bowler(name="Antonio DeJesus", highscore=130, city=cle)
session.add_all([michael_bayer, big_lebowski, ian_charnas, the_jesus])
session.flush()
# GET HIGH SCORERS BY CITY
max_score = func.max(Bowler.highscore).label('highest_score')
results = session.query(Bowler,
max_score).group_by(Bowler.city_id).all()
# Results contain (Bowler, max_score) tuples
[(<__main__.Bowler object at 0x139b590>, 170),
(<__main__.Bowler object at 0x13b20d0>, 130)]
# But I want results to just contain Bowler objects
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
