Re: [sqlalchemy] order_by on model property
On Sep 8, 2011, at 5:32 PM, Tim Black wrote:
> Ok, because the totalDue and totalPaid attributes are also SQLAlchemy
> declarative model object properties, I converted them (and all other
> similar property dependencies) to hybrid_properties and created the
> associated @[property].expression methods for every hybrid_property
> (though I think those @[property].expression methods are not needed
> where SQLAlchemy is not converting a Python method like float() into an
> SQL function--I'll test for that later). Now I get this error:
>
> Module projects.model.main:189 in totalDue
>>> return sum([w.totalDue for w in self.workDone]) +
> cast(self.itemsPurchasedTotal, Float)
> TypeError: 'InstrumentedAttribute' object is not iterable
>
> By writing line 189 on several lines, it's apparent self.workDone causes
> the error. self.workDone is a relation:
>
> class Project(DeclarativeBase):
># ...
>workDone = relation('WorkDone') # one-to-many
># ...
>@totalDue.expression
>def totalDue(self):
>'''Allow this property to be accessed at the class level'''
>return sum([w.totalDue for w in self.workDone]) +
> cast(self.itemsPurchasedTotal, Float)
>
> Do I need to convert that relation into a hybrid_property, or do
> something else in order to use it in this order_by query? I'm beginning
> to wonder if it's easier to deal with sorting by @properties by sorting
> in Python after running the query--is that the case?
When you're inside of @expression, everything you're doing is towards the goal
of producing a SQL expression construct that generates a string, which is sent
over the wire to the database where it's evaluated as part of a SQL string.
So you can't use any Python expressions that aren't supportable as a SQL
string, which includes list comprehensions and the sum() function. In this
specific case your query likely needs to join() out to a subquery that
calculates the sum using the SQL SUM function. The doc at
http://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries shows the
general idea.
> I'm beginning
> to wonder if it's easier to deal with sorting by @properties by sorting
> in Python after running the query--is that the case?
it depends very much on the specifics. If these are records that are already
to be loaded in memory, and you're dealing with small collections, then sure.
If its a huge report you're doing across thousands of rows, then maybe not.
>
> Tim
>
> On 09/07/2011 03:19 PM, Michael Bayer wrote:
>> You'd use a hybrid for this case, and due to the usage of float() you'd
>> probably want to produce a separate @expression that doesn't rely on a
>> Python function.
>>
>> Docs and examples for hybrid are at
>> http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html
>>
>> Separate @expression:
>>
>> http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html#defining-expression-behavior-distinct-from-attribute-behavior
>>
>> The "float()" call in SQL would likely be using CAST, so take a look at
>> http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cast#sqlalchemy.sql.expression.cast
>> for that.
>>
>>
>>
>> On Sep 7, 2011, at 2:27 PM, Tim Black wrote:
>>
>>> What is the right way to use .order_by() to order by the values returned by
>>> a model object property? My model object is like this:
>>>
>>> class Project(DeclarativeBase):
>>>__tablename__ = 'project'
>>>id = Column(Integer, primary_key=True)
>>>...
>>>@property
>>>def remainderDue(self):
>>>return self.totalDue - float(self.totalPaid)
>>>
>>> The query I'm trying to run is:
>>>
>>> projects =
>>> DBSession.query(model.Project).order_by(desc(model.Project.remainderDue))
>>>
>>> This returns the following error:
>>>
>>> Module sqlalchemy.sql.expression:1279 in _literal_as_text
>>> ArgumentError: SQL expression object or string expected.
>>>
>>> Tim
>>>
>>>
>>>
>>> --
>>> 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.
>
> --
> 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.
>
--
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.
Re: [sqlalchemy] order_by on model property
Ok, because the totalDue and totalPaid attributes are also SQLAlchemy
declarative model object properties, I converted them (and all other
similar property dependencies) to hybrid_properties and created the
associated @[property].expression methods for every hybrid_property
(though I think those @[property].expression methods are not needed
where SQLAlchemy is not converting a Python method like float() into an
SQL function--I'll test for that later). Now I get this error:
Module projects.model.main:189 in totalDue
>> return sum([w.totalDue for w in self.workDone]) +
cast(self.itemsPurchasedTotal, Float)
TypeError: 'InstrumentedAttribute' object is not iterable
By writing line 189 on several lines, it's apparent self.workDone causes
the error. self.workDone is a relation:
class Project(DeclarativeBase):
# ...
workDone = relation('WorkDone') # one-to-many
# ...
@totalDue.expression
def totalDue(self):
'''Allow this property to be accessed at the class level'''
return sum([w.totalDue for w in self.workDone]) +
cast(self.itemsPurchasedTotal, Float)
Do I need to convert that relation into a hybrid_property, or do
something else in order to use it in this order_by query? I'm beginning
to wonder if it's easier to deal with sorting by @properties by sorting
in Python after running the query--is that the case?
Tim
On 09/07/2011 03:19 PM, Michael Bayer wrote:
> You'd use a hybrid for this case, and due to the usage of float() you'd
> probably want to produce a separate @expression that doesn't rely on a Python
> function.
>
> Docs and examples for hybrid are at
> http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html
>
> Separate @expression:
>
> http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html#defining-expression-behavior-distinct-from-attribute-behavior
>
> The "float()" call in SQL would likely be using CAST, so take a look at
> http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cast#sqlalchemy.sql.expression.cast
> for that.
>
>
>
> On Sep 7, 2011, at 2:27 PM, Tim Black wrote:
>
>> What is the right way to use .order_by() to order by the values returned by
>> a model object property? My model object is like this:
>>
>> class Project(DeclarativeBase):
>> __tablename__ = 'project'
>> id = Column(Integer, primary_key=True)
>> ...
>> @property
>> def remainderDue(self):
>> return self.totalDue - float(self.totalPaid)
>>
>> The query I'm trying to run is:
>>
>> projects =
>> DBSession.query(model.Project).order_by(desc(model.Project.remainderDue))
>>
>> This returns the following error:
>>
>> Module sqlalchemy.sql.expression:1279 in _literal_as_text
>> ArgumentError: SQL expression object or string expected.
>>
>> Tim
>>
>>
>>
>> --
>> 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.
--
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.
Re: [sqlalchemy] order_by on model property
You'd use a hybrid for this case, and due to the usage of float() you'd probably want to produce a separate @expression that doesn't rely on a Python function. Docs and examples for hybrid are at http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html Separate @expression: http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html#defining-expression-behavior-distinct-from-attribute-behavior The "float()" call in SQL would likely be using CAST, so take a look at http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cast#sqlalchemy.sql.expression.cast for that. On Sep 7, 2011, at 2:27 PM, Tim Black wrote: > What is the right way to use .order_by() to order by the values returned by a > model object property? My model object is like this: > > class Project(DeclarativeBase): > __tablename__ = 'project' > id = Column(Integer, primary_key=True) > ... > @property > def remainderDue(self): > return self.totalDue - float(self.totalPaid) > > The query I'm trying to run is: > > projects = > DBSession.query(model.Project).order_by(desc(model.Project.remainderDue)) > > This returns the following error: > > Module sqlalchemy.sql.expression:1279 in _literal_as_text > ArgumentError: SQL expression object or string expected. > > Tim > > > > -- > 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. -- 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.
[sqlalchemy] order_by on model property
What is the right way to use .order_by() to order by the values returned by a model object property? My model object is like this: class Project(DeclarativeBase): __tablename__ = 'project' id = Column(Integer, primary_key=True) ... @property def remainderDue(self): return self.totalDue - float(self.totalPaid) The query I'm trying to run is: projects = DBSession.query(model.Project).order_by(desc(model.Project.remainderDue)) This returns the following error: Module sqlalchemy.sql.expression:1279 in _literal_as_text ArgumentError: SQL expression object or string expected. Tim -- 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.
