I have read over
http://www.sqlalchemy.org/docs/05/ormtutorial.html#using-subqueries
and http://www.mail-archive.com/[email protected]/msg11439.html,
but I'm having trouble putting the pieces together.
In the demo() below, I want to find the row in the database with the
max for every unique combination of Route(target,startpoint,ts). The
code I have there *works*, but doesn't seem to use any subquery magic
at all, nothing from 'correlated' subqueries. What might I be
missing?
Thanks!
Gregg L.
----------
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import MetaData
from sqlalchemy import Table, Column, Integer, String
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func
Base = declarative_base()
class Route(Base):
__tablename__ = 'route'
target = Column(String, nullable=False, primary_key=True)
hop_id = Column(Integer, nullable=False, primary_key=True)
ts = Column(Integer, nullable=False, primary_key=True)
startpoint = Column(String, nullable=False, primary_key=True)
# a bunch of other fields
#data = Column(String, nullable=True, primary_key=False)
#...
def __repr__(self):
return "%s %s %s %s" %(self.target, self.hop_id, self.ts,
self.startpoint)
connstring='sqlite:///:memory:'
engine = create_engine(connstring, echo=False)
session = sessionmaker(bind=engine, autoflush=False, autocommit=False)()
Base.metadata.bind = engine
Base.metadata.create_all()
samples = [
('T1',1,1000,'S1'),
('T1',2,1000,'S1'),
('T1',3,1000,'S1'),
('T1',1,1000,'S2'),
('T1',2,1000,'S2'),
('T2',1,1000,'S1'),
('T2',2,1000,'S1'),
('T2',3,1000,'S1'),
('T2',4,1000,'S1'),
('T2',1,1500,'S1')]
def demo():
for t,h,ts,s in samples :
session.add(Route(target=t,hop_id=h,ts=ts,startpoint=s))
session.flush()
session.commit()
# row in the database with the max_hop for every unique
combination of Route(target,startpoint,ts)
sq = session.query(Route,func.min(Route.hop_id).label('max_hop'))
sq = sq.group_by(Route.ts,Route.startpoint,Route.target).subquery()
q = session.query(Route,sq.c.max_hop)
q = q.filter(Route.target==sq.c.target)
q = q.filter(Route.startpoint == sq.c.startpoint)
q = q.filter(Route.hop_id == sq.c.hop_id)
q.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
-~----------~----~----~----~------~----~------~--~---
