[sqlalchemy] turn filtered query with subquery into relationship
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.00, 'ibm' '2001-01-01')
Holding(200.00, 'ibm' '2001-01-03')
Holding(300.00, 'ibm' '2001-01-05')
Desired holdings query:
Holding(300.00, '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 sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] turn filtered query with subquery into relationship
On Apr 30, 2012, at 10:54 AM, dan wrote:
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.
The desired_holdings() query is pretty complicated and I'm not seeing a win by
trying to get relationship() to do it. relationship() is oriented towards
maintaining the persistence between two classes, not as much a reporting
technique (and anything with max()/group_by in it is referring to reporting).
I would stick @property on top of desired_holdings, use object_session(self) to
get at session, and be done.
This use case is described at
http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#building-query-enabled-properties
.
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.00, 'ibm' '2001-01-01')
Holding(200.00, 'ibm' '2001-01-03')
Holding(300.00, 'ibm' '2001-01-05')
Desired holdings query:
Holding(300.00, 'ibm' '2001-01-05')
--
You received this message because you are subscribed to the Google Groups
