That's the problem, if I have use_labels=True, and I include "as
somecolumn" in the text clause, I still get a generated label. The
resulting sql =
"..... as somecolumn as
somelonglabelthatisashortenedversionofthetext". that creates an sql
error.
On Feb 27, 3:51 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> yeah with text just say "as sometable_somecolumn".
>
> On Feb 27, 2007, at 4:16 PM, Dennis wrote:
>
>
>
> > Well, columnname isn't a simple column in the case of a case
> > statement... the label is turning out like this:
>
> > "casewhenhas_testtrueandscoreisnullandgender1then1whenscoreisnullthen2
> > elsescoreend"
>
> > I haven't found a way to manually assign a label to a text clause yet,
> > but before I tried use_labels=True, I has appended "as score" to the
> > case clause and that worked.
>
> > On Feb 27, 2:44 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> >> 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
-~----------~----~----~----~------~----~------~--~---
