Hi all,
I'm having a little bit of trouble with the boundary between the ORM
and SQL expression language. Perhaps someone can set me aright.
I'm building up a query on a model with a hybrid_property using the
ORM. The hybrid_property.expression is a func.case statement.
Because 1) I only need a subset of the columns from the model, 2) the
query is guaranteed to return upwards of 1000 results, and 3) the
results have to be processed individually before rendering the
associated template, I was hoping to take the query I'd built up and,
using the its statement attribute, limit the columns returned by it to
the subset I need and execute it as a SQL expression language query.
This works just fine with the vanilla columns, but I'm getting a
ProgrammingError when I try to include the hybrid_property. Am I
misunderstanding how to accomplish this, or simply misusing the tools
available. Here's a simplified version of what I'm trying to do:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy import Column, Integer, Boolean, func
from sqlalchemy.orm import relationship
Base = declarative_base()
class Subject(Base):
__table__ = 'subjects'
id = Column(Integer, primary_key=True)
project = Column(Integer, nullable=False)
class Sample(Base):
__table__ = 'samples'
id = Column(Integer, primary_key=True)
subject_id = Column(Integer, ForeignKey('subjects.id'),
nullable=False)
condition_1 = Column(Boolean)
condition_2 = Column(Boolean)
# then a bunch of columns that I want to ignore for the sake of
the query in question
@hybrid_property
def condition(self):
if self.condition_1:
return 1
elif self.condition_2:
return 2
else:
return 3
@condition.expression
def condition(cls):
return func.case([
(cls.condition_1, 1),
(cls.condition_2, 2),
], else_=3)
subject = relationship(Subject)
orm_query = Session.query(Sample).join(Subject)
ok_query = orm_query.statement.with_only_columns([
Subject.id,
Subject.project,
Sample.id,
])
This works just fine, but:
fail_query = orm_query.statement.with_only_columns([
Subject.id,
Subject.project,
Sample.id,
Sample.condition.label('condition')
])
executing the fail_query results in the following:
ProgrammingError: can't adapt type 'InstrumentedAttribute' 'SELECT
subjects.id, subjects.project, samples.id, case(%(case_1)s) AS
condition FROM samples JOIN subjects ON subjects.id =
samples.subject_id' {'case_1':
[(<sqlalchemy.orm.attributes.InstrumentedAttribute object>, 1),
(<sqlalchemy.orm.attributes.InstrumentedAttribute object>, 2)]}
I'm using Postgres and psycopg2 with SQLAlchemy version 0.7.0.
Thanks in advance,
Ben
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
