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
-~----------~----~----~----~------~----~------~--~---
