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
-~----------~----~----~----~------~----~------~--~---
