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 Version and table example code below In [81]: sa.__version__ Out[81]: '0.5.0beta4' import datetime import sqlalchemy as sa import sqlalchemy.orm as orm from sqlalchemy.ext.declarative import declarative_base engine = sa.create_engine("mysql://johnh:[EMAIL PROTECTED]/trdlnksec") Base = declarative_base(bind=engine) 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) def __init__(self, ticker, date, price, volume): self.ticker = ticker self.date = date self.price = price def __repr__(self): return "PriceData('%s', %r, %r')"%(self.ticker, self.date, self.price) 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) 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(2008,1,2), 101, 2000.) i3 = PriceData('IBM', datetime.date(2008,1,3), 102, 2000.) q4 = FundamentalData('IBM', datetime.date(2007,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(q1) session.add(q2) session.add(q3) session.add(q4) session.commit() --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---