Dear List,
Hoping for help with following unanswered stackoverflow question. If the
approach is not the best, that feedback is good as well.
In the case below is it better to just not use relationships and just use
the method with the query?
Thanks
--------------------------------------------------------------------
In the code below I want to replace *all_holdings* in Account with a
property called *holdings* that returns the *desired_holdings* (which are
the holdings representing the latest known quantity which can change over
time). I'm having trouble figuring out how to construct the call to
relationship.
In addition any comments on the appropriateness of the pattern (keeping
historic data in a single table and using a max date subquery to get most
recent), better alternatives, improvements on the query appreciated.
from sqlalchemy import Column, Integer, String, Date, DateTime, REAL,
ForeignKey, func
from sqlalchemy.orm import relationship, aliased
from sqlalchemy.sql.operators import and_, eq
from sqlalchemy.ext.declarative import declarative_base
from db import session
import datetime
import string
Base = declarative_base()
class MySQLSettings(object):
__table_args__ = {'mysql_engine':'InnoDB'}
class Account(MySQLSettings, Base):
__tablename__ = 'account'
id = Column(Integer, primary_key=True)
name = Column(String(64))
all_holdings = relationship('Holding', backref='account')
def desired_holdings(self):
max_date_subq = session.query(Holding.account_id.label('account_id'),
Holding.stock_id.label('stock_id'),
func.max(Holding.as_of).label('max_as_of')). \
group_by(Holding.account_id,
Holding.stock_id).subquery()
desired_query = session.query(Holding).join(Account,
Account.id==account.id).join(max_date_subq).\
filter(max_date_subq.c.account_id==account.id).\
filter(Holding.as_of==max_date_subq.c.max_as_of).\
filter(Holding.account_id==max_date_subq.c.account_id).\
filter(Holding.stock_id==max_date_subq.c.stock_id)
return desired_query.all()
def __init__(self, name):
self.name = name
class Stock(MySQLSettings, Base):
__tablename__ = 'stock'
id = Column(Integer, primary_key=True)
name = Column(String(64))
def __init__(self, name):
self.name = name
class Holding(MySQLSettings, Base):
__tablename__ = 'holding'
id = Column(Integer, primary_key=True)
account_id = Column(Integer, ForeignKey('account.id'), nullable=False)
stock_id = Column(Integer, ForeignKey('stock.id'), nullable=False)
quantity = Column(REAL)
as_of = Column(Date)
stock = relationship('Stock')
def __str__(self):
return "Holding(%f, '%s' '%s')"%(self.quantity, self.stock.name,
str(self.as_of))
def __init__(self, account, stock, quantity, as_of):
self.account_id = account.id
self.stock_id = stock.id
self.quantity = quantity
self.as_of = as_of
if __name__ == "__main__":
ibm = Stock('ibm')
session.add(ibm)
account = Account('a')
session.add(account)
session.flush()
session.add_all([ Holding(account, ibm, 100, datetime.date(2001, 1, 1)),
Holding(account, ibm, 200, datetime.date(2001, 1, 3)),
Holding(account, ibm, 300, datetime.date(2001, 1, 5)) ])
session.commit()
print "All holdings by relation:\n\t", \
string.join([ str(h) for h in account.all_holdings ], "\n\t")
print "Desired holdings query:\n\t", \
string.join([ str(h) for h in account.desired_holdings() ], "\n\t")
The results when run are:
All holdings by relation:
Holding(100.000000, 'ibm' '2001-01-01')
Holding(200.000000, 'ibm' '2001-01-03')
Holding(300.000000, 'ibm' '2001-01-05')
Desired holdings query:
Holding(300.000000, 'ibm' '2001-01-05')
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/KKB3-3r5kSAJ.
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.
