here is a demo: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Appl(Base):
__tablename__ = 'appl'
id = Column(Integer, primary_key=True)
refid = Column(Integer)
lastname = Column(Unicode(50))
firstname = Column(Unicode(50))
cityid = Column(Integer, ForeignKey('city.id'))
city = relationship('City')
class City(Base):
__tablename__ = 'city'
id = Column(Integer, primary_key=True)
name = Column(Unicode(30), nullable=False)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
DBSession = Session(e)
c1, c2, c3 = City(name='c1'), City(name='c2'), City(name='c3')
DBSession.add_all([
Appl(firstname='b', lastname='a', refid=1, city=c1),
Appl(firstname='b', lastname='a', refid=1, city=c2),
Appl(firstname='b', lastname='a', refid=2, city=c3),
Appl(firstname='q', lastname='z', refid=2, city=c1),
Appl(firstname='b', lastname='a', refid=3, city=c2),
])
qlast, qfirst = 'a', 'b'
d = DBSession.query(Appl).\
distinct(Appl.refid).\
filter(Appl.lastname == qlast).\
filter(Appl.firstname == qfirst).\
group_by(Appl).\
order_by(Appl.refid)
d = d.cte('distinct_query')
q = DBSession.query(Appl).select_entity_from(d).\
join(Appl.city).\
order_by(d.c.lastname, d.c.firstname)
for row in q:
print row.refid, row.firstname, row.lastname, row.city.name
On Sep 24, 2013, at 5:58 PM, Nathan Mailg <nathanma...@gmail.com> wrote:
> On Sep 20, 2013, at 11:59 AM, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
>>
>> Ok now I'm still not following - in this case, "row" is a NamedTuple, or a
>> mapped instance? if its a NamedTuple then you don't have the service of
>> traversing along object relationships available since the NamedTuple isn't a
>> mapped object. You'd need to query for a full object with an identity (
>> query(MyClass) as opposed to query(MyClass.col1, MyClass.col2, ...)
>>
>> if you provide me with *very minimal, but working* mappings and the query
>> we're working on, I can show you how to make it load entities rather than
>> rows.
>>
>
>
> In the debugger the returned row was of type "KeyedTuple", which I think is
> derived from "NamedTuple", so yes, it's a NamedTuple and not a mapped
> instance.
>
> I really appreciate your help with this. Below is as stripped down as I can
> make it while still showing the moving parts:
>
> class Appl(Base):
> __tablename__ = 'appl'
> id = Column(Integer, primary_key=True)
> refid = Column(Integer, Sequence('appl_refid_seq'))
> appldate = Column(Date)
> lastname = Column(Unicode(50))
> firstname = Column(Unicode(50))
> cityid = Column(Integer, ForeignKey('city.id'))
> cityid2 = Column(Integer, ForeignKey('city.id'))
> #
> city = relationship('City', lazy='joined',
> primaryjoin='City.id==Appl.cityid')
> city2 = relationship('City', lazy='joined',
> primaryjoin='City.id==Appl.cityid2')
>
> class City(Base):
> __tablename__ = 'city'
> id = Column(Integer, primary_key=True)
> name = Column(Unicode(30), nullable=False)
> state = Column(Unicode(2), nullable=False)
> zipcode = Column(Unicode(10))
>
> qlast, qfirst = params['query'].split(' ', 1)
> d = DBSession.query(Appl).\
> distinct(Appl.refid).\
> filter(Appl.lastname.ilike(qlast)).\
> filter(Appl.firstname.ilike(qfirst+'%')).\
> group_by(Appl).\
> order_by(Appl.refid, Appl.appldate.desc())
> d = d.cte('distinct_query')
> q = DBSession.query(d).\
> join(City, City.id==d.c.cityid).\
> order_by(d.c.lastname, d.c.firstname)
>
> What I'm trying to get at are the attrs on City after running "q", like:
>
> # row is KeyedTuple instance
> for row in q.all():
> # this works
> print row.lastname
> # below does not work,
> # stuck here trying to get at joined City attrs, e.g. City has a "name"
> attr
> print row.city.name
>
> Thank you!
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
