the label is always "tablename_columnname". youd have to show me where you need that to be "programmatic".
On Feb 27, 2007, at 2:29 PM, Dennis wrote: > > Thanks for taking a peek. > > Interesting, it does indeed fix the issue to use labels. Now I have > another issue though, I have a case statement in my select which I was > specifying like this: > > select ( ['case when .... yada yada yada end as something' ] ...... > > If use_labels = True, then the query breaks because the generated sql > has two as label parts two it. > > if I delete the "as something" part, I think don't know > programatically what the label is though. I need to know that because > I order by it. > > Isn't there a way to find out a column label from a query? > > -Dennis > > On Feb 27, 12:47 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: >> if you run it with full blown logging on, i.e.: >> >> import logging >> logging.basicConfig() >> logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG) >> logging.getLogger('sqlalchemy.orm').setLevel(logging.DEBUG) >> >> the issue can be detected when you look at the mapper creating >> instance keys for "T" (although this is clearly not a novice issue): >> >> DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key >> (<class '__main__.T'>, (1,), None) not in session[] >> DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key >> (<class '__main__.T'>, (None,), None) not in session[] >> DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key >> (<class '__main__.T'>, (3,), None) not in session[] >> DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key >> (<class '__main__.T'>, (None,), None) not in session[] >> DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key >> (<class '__main__.T'>, (5,), None) not in session[] >> >> so its not getting an identity key for every other row, which >> indicates its looking at the wrong column in the result set. (on >> each of those "None"s, its going to skip that entity) looking at the >> query: >> >> SELECT ts.id, ts.dat, other.ts_id, other.other_dat >> FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id >> >> we can see that "other" has a column called "ts_id", which looks >> exactly like the label that would be made for "id" in table "ts". so >> thats whats happening here. so throwing on a "use_labels=True" to >> the query (or changing the name of "ts_id") produces the query: >> >> SELECT ts.id AS ts_id, ts.dat AS ts_dat, other.ts_id AS other_ts_id, >> other.other_dat AS other_other_dat >> FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id >> >> that gives the correct results. >> >> not sure what SA can really do here to make this kind of issue easier >> to catch, since the resultproxy itself is where its looking for "col >> label, col name, ", etc. the generated labels are generally more >> accurate. i tried playing around with ResultProxy to make it detect >> an ambiguity of this nature, but i think it might not be possible >> unless more flags/switches get passed from the statement to the >> result (which id rather not do since it further marginalizes straight >> textual queries), since if the select statement uses table/col labels >> for each column, there still could be conflicts which dont matter, >> such as the column names the normal eager loader generates: >> >> 'ts_id', 'ts_dat', 'other_4966_ts_id', 'other_4966_other_dat', >> >> that result is from column "ts_id" attached to an Alias >> "other_4966". if we said "dont allow any Column to be found twice in >> the row", then that breaks (since it will match other_4966_ts_id on >> its _label, ts_id on its name). >> >> On Feb 27, 2007, at 12:09 PM, Dennis Muhlestein wrote: >> >>> from sqlalchemy import * >> >>> e=create_engine('sqlite://memory') >>> ts=Table('ts',e, >>> Column ( 'id',Integer,primary_key=True), >>> Column ( 'dat',Integer,nullable=False)) >>> ts.create() >> >>> to_oneornone=Table('other',e, >>> Column ( 'ts_id', Integer,ForeignKey('ts.id'), primary_key=True, >>> nullable=False ), >>> Column ( 'other_dat', Integer, nullable=False ) ) >>> to_oneornone.create() >> >>> class T(object): pass >>> T.mapper=mapper(T,ts) >> >>> class To(object):pass >>> To.mapper=mapper(To,to_oneornone,properties={'ts':relation >>> (T,backref=backref('other',uselist=False))}) >> >>> s=create_session() >>> for x in range(10): >>> t=T() >>> t.dat=x >>> s.save(t) >> >>> if x % 2 == 0: # test every other T has an optional data >>> o=To() >>> o.other_dat=x >>> t.other=o >> >>> s.save(t) >>> s.flush() >> >>> s.clear() >> >>> somedata=s.query(T).options(eagerload('other')).select() >>> print 'Number results should be 10: ', len(somedata) >> >>> s.clear() >> >>> sel=select([ts,to_oneornone], >>> from_obj=[ts.outerjoin(to_oneornone)]) >> >>> print "Raw select also is 10: " , len(sel.execute().fetchall() ) >> >>> print "Instances should also be 10: ", len(s.query(T).options >>> (contains_eager('other')).instances(sel.execute())) > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---