On Jan 21, 2013, at 7:33 AM, Kenny Billiau wrote:
> On Fri Jan 18 15:40:25 2013, Simon King wrote:
>> On 18 Jan 2013, at 12:15, Kenny <[email protected]> wrote:
>>
>>> 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
>>>
>>
>> I think you should be able to do this:
>>
>> print sample[Sample.id]
>> print sample[Experiment.id]
>>
>> Hope that helps,
>>
>> Simon
>
> 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?
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 [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.
>
--
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.
