you have to show me what youre doing since from my understanding,
youre using literal text, and nothing should get added to that.
On Feb 28, 2007, at 10:46 AM, Dennis wrote:
>
> 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_testtrueandscoreisnullandgender1then1whenscoreisnullthe
>>> n2
>>> 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 [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
-~----------~----~----~----~------~----~------~--~---
