namedTuple with conflicting attributes

[Kenny]

Hey all,

I'm quite new to SQLAlchemy and I've been struggling to get the following to work.

When one queries with specific entities, the resulting namedTuples might have overlapping keys.
e.g.

    sample = session.query(
            Sample.id,
            Experiment.id
        ).\
        join(Experiment).\
        filter(Experiment.active==1).\
        distinct().\
        first()
     print sample.id # will print experiment.id

So, how do access conflicting attributes?
I know I can use Sample.id.label('sample_id'), but I would prefer to have this in an automatic way. Much in the same way as the function with_labels() works on the query object, but then bubbling through to the actual result.
Any suggestions would be welcome!

wkr,
Kenny
ps: I am using SQLALchemy 0.7.3

[Simon King]

I think you should be able to do this:

print sample[Sample.id]
print sample[Experiment.id]

Hope that helps,

Simon

[Kenny Billiau]

Nope, that didn't do the trick. Anyone else have any suggestions?

Or is there any way to automatically add a label to all attributes in
the same way as .label() does now?

wkr,
Kenny

[Michael Bayer]

Well, the two columns have the same name "id", you have the option to give them a new name with label(), and the "tablename_colname" scheme you refer to is really more oriented towards getting the SQL statements to be legal for the database and the columns targetable by name as far as SQLAlchemy internals are concerned. It doesn't scale well for explicit user access since as soon as you start using aliases, those are typically anonymously named. The Query is trying to stay away from cases like that and if it were me, I'd just be iterating the two values explicitly, i.e. "sample_id, experiment_id = session.query(...)".

But, here's a recipe that will give you "Sample_id", "Experiment_id", and it uses all public APIs too:

from sqlalchemy.orm.query import Query

class QueryWithLabels(Query):
def with_entity_labels(self):
modified = []
for expr in self.column_descriptions:
if hasattr(expr['expr'], "property"):
cls = expr['expr'].class_
modified.append(expr['expr'].\
label('%s_%s' % (cls.__name__, expr['name'])))
else:
modified.append(expr['expr'])
return self.with_entities(*modified)


from sqlalchemy import Column, Integer, create_engine, ForeignKey
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
__tablename__ = "a"

id = Column(Integer, primary_key=True)
bs = relationship("B")


class B(Base):
__tablename__ = "b"

id = Column(Integer, primary_key=True)

a_id = Column(Integer, ForeignKey('a.id'))

e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)

s = Session(e, query_cls=QueryWithLabels)

s.add_all([
A(bs=[B(), B()]),
A(bs=[B(), B()])
])
s.commit()

results = s.query(A.id, B.id).with_entity_labels().all()

for result in results:
print result.A_id, result.B_id








>
> wkr,
> Kenny
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>