On Oct 9, 2010, at 7:02 AM, Berteun wrote:
> Hello,
>
> I've started using SQLAlchemy recently, and am using Python 2.6 and
> SQLAlchemy 0.6.4.
>
> First of all: I'm unable to figure out how I can specify the names of
> the results of a union, if I have this example:
>
> from sqlalchemy import Column, Integer, String, create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker
> engine = create_engine('sqlite://')
>
> Base = declarative_base()
> session = sessionmaker(bind=engine)()
>
> class Foo(Base):
> __tablename__ = 'foo'
> id = Column(Integer, primary_key=True)
> spam = Column(String)
>
> def __init__(self, spam):
> self.spam = spam
>
> class Bar(Base):
> __tablename__ = 'bar'
> id = Column(Integer, primary_key=True)
> spam = Column(String)
>
> def __init__(self, spam):
> self.spam = spam
>
> Base.metadata.create_all(engine)
>
> session.add(Foo('abc'))
> session.add(Bar('def'))
> session.commit()
>
> qa = session.query(Foo.spam)
> qb = session.query(Bar.spam)
>
> print qa.all()[0].keys()
> print qa.union(qb).all()[0].keys()
>
> The first printout gives me 'spam', which I would expect and which is
> convenient to program with, the second one gives me a mangled name.
I will start off by saying that the labeling applied to the NamedTuple returned
by Query was not originally intended to be a general purpose method of
targeting columns in the result. That approach doesn't work for non trivial
cases, as the ORM is put into many positions where the simple behavior of
"non-name-mangling" produces ambiguous results:
print session.query(Foo.spam, Bar.spam).all()[0].keys()
['spam', 'spam']
It is intended that if you really need certain labels, you'd apply them:
print session.query(Foo.spam.label('fs'),
Bar.spam.label('bs')).all()[0].keys()
The "auto" labeling case is currently inconsistent:
print session.query(Foo.spam, Bar.spam).from_self().all()[0].keys()
SELECT anon_1.foo_spam AS anon_1_foo_spam, anon_1.bar_spam AS
anon_1_bar_spam
FROM (SELECT foo.spam AS foo_spam, bar.spam AS bar_spam
FROM foo, bar) AS anon_1
[u'foo_spam', u'bar_spam']
Because, if the simple ambiguous case gives you ambiguous labels, 'spam',
'spam', we're not gaining anything by using the "unambiguous" labels in the
subquery case. In the case of an anonymous alias can produce the
non-targetetable names you've seen:
qa = session.query(aliased(Foo).spam)
print qa.from_self().all()[0].keys()
SELECT anon_1.foo_1_spam AS anon_1_foo_1_spam
FROM (SELECT foo_1.spam AS foo_1_spam
FROM foo AS foo_1) AS anon_1
[u'%(19451760 foo)s_spam']
The "non-targetable" names, when used in the expression language, are intended
to be targeted by Column objects in the result row, not the string name which
is externally meaningless when anonymous aliases are used. But the Query's
NamedTuple doesn't provide column-based lookups and it probably shouldn't,
since the ORM is supposed to be presenting an interface that is above the level
of result-set concepts.
Ticket #1942 is added to propagate the simple names outwards in any "select
from self" type of situation, which includes UNION. It can't go in 0.6, has
to wait until 0.7, since it produces backwards incompatible results (its very
likely people are targeting columns in a query tuple using "foo_spam",
"bar_spam", since those are the names they're getting).
UNION will work if you add labels to the first selectable:
qa = session.query(Foo.spam.label('spam'))
qb = session.query(Bar.spam)
print qa.union(qb).all()[0].keys()
>
> Secondly: if this example is converted to MSSQL on a database using
> schemata, the union statement fails. For a table with schema X and
> table Y the select statements created with automatically alias the
> table, i.e. it will generate a query like 'SELECT X_Y.SPAM FROM X.Y AS
> X_Y' when selecting a single column. However, after applying the
> union, this alias will be gone, and it'll try to do something like the
> following:
>
> SELECT anon_1.x FROM (SELECT X_Y.SPAM FROM X.Y UNION 'SELECT Z_Y.SPAM
> FROM Z.Y) AS anon_1.
>
> Because the table aliases have disappeared, the query fails. A work
> around is to introduce an alias yourself, if the above example
> something like Foo_a = aliased(Foo); and use Foo_a instead. Those
> aliases are remembered. To me it looks like a bug that it isn't
> working without an explicit alias, but perhaps I'm wrong there.
That's a bug and is addressed by ticket #1943 which includes a patch, and it
would be extremely helpful if you could run this patch through your own tests
before we commit it. http://www.sqlalchemy.org/trac/ticket/1943
--
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.
