On Oct 9, 2008, at 10:01 PM, John Hunter wrote:
> To simplify the problem, I have written code that simply connects the
> "reportdate" of the fundamental data with a give price data point, eg
>
> class PriceData(Base):
> __tablename__ = 'price_data'
>
> ticker = sa.Column(sa.String(12), primary_key=True)
> date = sa.Column(sa.Date, primary_key=True)
> price = sa.Column(sa.FLOAT)
> reportdate = orm.column_property(
> sa.select(
> [FundamentalData.reportdate],
> (ticker==FundamentalData.ticker) &
> (date>=FundamentalData.reportdate)
> ).order_by
> (FundamentalData.reportdate.desc()).limit(1).label('reportdate'))
>
> This works fine, as in the complete example posted below (the code
> identifies a report date less-than-or-equal-to a price date for each
> date). But I am confused by the effect of the "limit" method. My
> intention is to use it to limit the results to one match, the most
> recent reportdate before date. But if I remove the "limit" method
> call, I still get the same result, a single reportdate, though I
> would expect a sequence instead since there are multiple reportdates
> prior to the price date in the example included below. Or if I write
> "limit(4)" I still get a single result for reportdate, though I would
> expect multiple matches.
>
> Any ideas what is going on here? Compete example below....
whats probably happening is that when you access "reportdate", SQLA
uses a lazy loader to fetch the result. Since its known to be a
"scalar" attribute, only the first row is fetched.
The "better" way to do these correlated column properties it to devise
the query in such a way that LIMIT is not needed. In this case, you
probably want to be selecting func.max(FundamentalData.reportdate)
which should eliminiate the need for LIMIT. That would also make
your attribute more compatible with eagerloading too (although theres
some trac tickets involving eagerloading in conjunction with
correlated subqueries - its doable but is a little quirky atm).
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
