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. In
this case it's not so bad, but I've seen names such as '%(1238248
foo)spam_baz_eggs' when using a MSSQL database with tables and
schemata, how can I override this?
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.
Thanks in advance,
-Berteun
--
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.
