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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---