On Aug 23, 2:11 am, OKB <[EMAIL PROTECTED]> wrote:
> ... let's say I have a table with columns Firstname, Lastname,
> and Income. What I'd like is to get query results where the columns
> are Firstname, Lastname, Income, "sum of incomes of everyone with this
> Firstname", "sum of incomes of everyone with this Lastname".
>
> Does SQLAlchemy have the smarts to allow me to somehow specify "group
> by" information on a per-column basis, and then under the hood
> generate the joins necessary to retrieve the aggregates I want? If
> so, how can I do this?
Yes, you can do the joins in SQLAlchemy. Here is one way using
column_property. I'm sure you can make it simpler than this, and you
can also 'automate' if you want to make it simpler for complex cases.
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine("sqlite://", echo=True)
metadata = MetaData(bind=engine)
sm = sessionmaker(autoflush=True, bind=engine)
session = scoped_session(sm)
mapper = session.mapper
tabl = Table('testa', metadata,
Column('first',String(12)),
Column('last',String(12)),
Column('pay',Integer),
PrimaryKeyConstraint('first','last',name='fullname'))
class Tabl(object):
pass
tabfirst = tabl.alias('tabfirst')
selfirst = select([func.sum(tabfirst.c.pay)],
tabl.c.first==tabfirst.c.first).as_scalar().label('sumfirst')
tablast = tabl.alias('tablast')
sellast = select([func.sum(tablast.c.pay)],
tabl.c.last==tablast.c.last).as_scalar().label('sumlast')
mapper(Tabl, tabl, properties=dict(
sumfirst=column_property(selfirst),
sumlast=column_property(sellast)
))
tabl.create()
u1 = Tabl(first='George',last='Washington',pay=100)
u2 = Tabl(first='George',last='Hamilton',pay=200)
u3 = Tabl(first='Martha',last='Washington',pay=80)
session.commit()
q = session.query( Tabl )
for obj in q:
print "%-12s%-12s%4d All %-12s=%4d All %-12s=%4d" % \
(obj.first, obj.last, obj.pay, \
obj.first,obj.sumfirst, obj.last,obj.sumlast)
Output:
George Washington 100 All George = 300 All Washington = 180
George Hamilton 200 All George = 300 All Hamilton = 200
Martha Washington 80 All Martha = 80 All Washington = 180
The query:
SELECT
(SELECT sum(tabfirst.pay) AS sum_1 FROM testa AS tabfirst
WHERE testa.first = tabfirst.first) AS sumfirst,
(SELECT sum(tablast.pay) AS sum_2 FROM testa AS tablast
WHERE testa.last = tablast.last) AS sumlast,
testa.first AS testa_first, testa.last AS testa_last, testa.pay AS
testa_pay
FROM testa
-- Kip Bryan
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
