On Thu, Oct 9, 2008 at 6:28 PM, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
>
> On Oct 9, 2008, at 3:28 PM, John Hunter wrote:
>
>>
>> I have some tables with financial data -- one table has price data on
>> a given date and another data has fundamental data on a given report
>> date. I am wondering if it is possible to create another object that
>> is a ratio on a join between values in the two tables, eg in pseudo
>> code
>>
>> # divide the current price by the most recent sales figure
>> price_sales = price_table.price / fundamental_table.sales where
>> fundamental_table.reportdate<=price_table.date order by
>> fundamental_table.reportdate limit 1
>>
>> I would also like this price_sales to be an attribute that is itself
>> queryable, eg, so I can express
>>
>> select all where ratio_data.price_sales<2 and and price_data.price>5
>>
>> I am a bit of a sqlalchemy newbie -- I have written the price data and
>> fundamental tables below. If there is a way to express the above
>> ratio data as a handy sqlalchemy map, I'd appreciate any suggestions
>
>
> if the attribute is "attached" to either PriceData or FundamentalData,
> the general route towards this kind of thing is to use
> column_property(). You can place subqueries which correlate to the
> base table in those. If you're looking for a third mapped object
> along the lines of RatioData, you can map such a class to a select()
> object which represents the query, although this seems more like an
> attribute on PriceData.
>
> the column_property() is usable in queries and you can also customize
> how it compares using a user-defined PropComparator.
>
> a brief example is at:
> http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_mapper_expressions
Hey Michael,
Thanks for your answer. This has been tremendously helpful. I now
have an example that is doing more-or-less what I want for my
toy-example (see below). In this example, I went ahead and attached
the ratio to the price data, because as you suggested this is a fairly
natural place for it (for any fundamental reportdate there are
multiple price data points at which I might want to compute the
ratio).
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....
import datetime
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
engine = sa.create_engine("sqlite:///test.db")
Base = declarative_base(bind=engine)
class FundamentalData(Base):
__tablename__ = 'fundamentals'
ticker = sa.Column(sa.String(12), primary_key=True)
reportdate = sa.Column(sa.Date, primary_key=True)
sales = sa.Column(sa.FLOAT)
income = sa.Column(sa.FLOAT)
def __init__(self, ticker, reportdate, sales, income):
self.ticker = ticker
self.reportdate = reportdate
self.sales = sales
self.income = income
def __repr__(self):
return "FundamentalData('%s', %r, %r, %r)"%(self.ticker,
self.reportdate, self.sales, self.income)
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'))
price_sales = orm.column_property(
sa.select(
[price/FundamentalData.sales],
(ticker==FundamentalData.ticker) & (date>=FundamentalData.reportdate),
).label('price_sales'))
def __init__(self, ticker, date, price, volume):
self.ticker = ticker
self.date = date
self.price = price
def __repr__(self):
return "PriceData('%s', %r, %r); ps=%r, reportdate=%s"%(
self.ticker, self.date, self.price, self.price_sales, self.reportdate)
if __name__=='__main__':
Session = orm.sessionmaker()
session = Session(bind=engine)
Base.metadata.drop_all()
Base.metadata.create_all()
i1 = PriceData('IBM', datetime.date(2008,1,1), 100, 1000.)
i2 = PriceData('IBM', datetime.date(2007,7,2), 101, 2000.)
i3 = PriceData('IBM', datetime.date(2007,4,3), 102, 2000.)
a1 = PriceData('ACXM', datetime.date(2007,7,1), 100, 11000.)
a2 = PriceData('ACXM', datetime.date(2007,7,2), 101, 12000.)
a3 = PriceData('ACXM', datetime.date(2007,7,3), 102, 12000.)
q4 = FundamentalData('IBM', datetime.date(2008,12,1), 30000., 3000.)
q3 = FundamentalData('IBM', datetime.date(2007,9,1), 20000., 2000.)
q2 = FundamentalData('IBM', datetime.date(2007,6,1), 10000., 1000.)
q1 = FundamentalData('IBM', datetime.date(2007,3,1), 10001., 1001.)
session.add(i1)
session.add(i2)
session.add(i3)
session.add(a1)
session.add(a2)
session.add(a3)
session.add(q1)
session.add(q2)
session.add(q3)
session.add(q4)
session.commit()
for o in session.query(PriceData):
print o
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
