Executing query results in ProgrammingError: can't adapt type 'InstrumentedAttribute'

[OlduvaiHand]

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

[Michael Bayer]

On Aug 26, 2011, at 1:09 PM, OlduvaiHand wrote:

> 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.

do you mean to be using the case() function , that produces the CASE expression ? It's a standalone function not part of the "func." construct:

http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=case#sqlalchemy.sql.expression.case

The error you're getting is due to func.case() just being a generic function, the incoming tuple is interpreted as "send straight to the DBAPI" and then it all goes wrong.

[OlduvaiHand]

Indeed. I did mean to be using the case() function. Thanks, zzzeek.
Incidentally, this particular case is supposed to return EnumSymbols
of the variety you describe in your EnumRecipe on techspot. Now that
the correct case function is being used, I'm still getting a
"ProgrammingError: can't adapt type EnumSymbol" message. You wouldn't
have any thoughts about that, would you?

[Michael Bayer]

That indicates that an EnumSymbol object is being passed to a query as a literal value, without typing information associated with it so that SQLAlchemy doesn't know to associate your custom TypeDecorator with it.

A phrase such as, x == bindparam("y") , then passing {'y':some_enum_symbol} as the parameters would have that effect. The solution is to make sure "type_=MyEnumType" is present on phrases like bindparam() or literal(), where "MyEnumType" is the TypeDecorator used in the blog post.

If OTOH this is a regular comparison, such as "myclass.my_enum_col == some_enum", that should usually work OK unless my_enum_col doesn't have proper typing information on it.

[OlduvaiHand]

That makes perfect sense. I really appreciate the help. Everything
is working as expected now.