python之加入条件以急切地加载 sqlalchemy orm

from sqlalchemy.orm import subqueryload, joinedload, eagerload 
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func,Float, sql 
from sqlalchemy.orm import relation 
from sqlalchemy.ext.declarative import declarative_base 
from sqlalchemy.orm import sessionmaker 
from sqlalchemy import create_engine 
 
engine = create_engine('sqlite:///testdb.sqlite') 
engine.echo = True 
Base = declarative_base() 
session = sessionmaker() 
session.configure(bind=engine) 
Base.metadata.create_all(engine) 
s= session() 
 
class Stock(Base): 
__tablename__ = 'stock' 
stock_id = Column(Integer, primary_key=True) 
name = Column(String) 
prices = relation("StockPrice") 
 
class StockPrice(Base): 
__tablename__ = 'stock_price' 
stock_id = Column(Integer, ForeignKey('stock.stock_id'), primary_key=True) 
date = Column(String, primary_key=True) 
price = Column(Float) 
source = Column(String, primary_key=True) 
user = Column(String) 
 
Base.metadata.create_all(engine) 
 
stockprice1 = StockPrice(stock_id = 1, date="2014-10-29", price="170.0", source="X Firm", user="U1") 
stockprice2 = StockPrice(stock_id = 1, date="2014-10-30", price="175.0", source="X Firm", user="U2") 
stock1 = Stock(stock_id = 1, name = "GOOGLE", prices=[stockprice1, stockprice2]) 
 
stockprice1 = StockPrice(stock_id = 2, date="2014-10-29", price="150.0", source="X Firm", user="U1") 
stockprice2 = StockPrice(stock_id = 2, date="2014-10-30", price="155.0", source="X Firm", user="U2") 
stock2 = Stock(stock_id = 2, name = "YAHOO", prices=[stockprice1, stockprice2]) 
 
s.add_all([stock1, stock2]) 
s.commit() 

预先加载股票价格:

stock = s.query(Stock).options(joinedload(Stock.prices)).filter(Stock.stock_id == 1).one()

一种快速加载给定日期股票价格的方法:

stock = s.query(Stock).options(joinedload(Stock.prices)).filter(Stock.stock_id == 1).filter(StockPrice.date == "2014-10-30") .one()

但该方法的问题是，如果您有可能与 Stock 相关的 StockPrice 表，并且如果您想加载给定日期的所有关系，那么在加入所有关系后结果集会变得非常庞大。过滤器在 WHERE 子句中添加条件，而我需要一种方法来指定连接条件以预先加载。

stock = s.query(Stock).options(joinedload(Stock.prices, #condition 在这里不起作用)).filter(Stock.stock_id == 1).one()

请您参考如下方法:

代替 joinedload(Stock.prices) 执行以下操作:

stock = (s.query(Stock) 
 
         # @note: this replaces `joinedload(Stock.prices)` 
         .join(StockPrice, 
               and_(StockPrice.stock_id == Stock.stock_id, 
                    StockPrice.date == "2014-10-30") 
               ) 
 
         # effectively *trick* SQLAlchemy into thinking that above we loaded all 
         # items for the relationship *Stock.prices* 
         .options(contains_eager(Stock.prices)) 
 
         ).get(1) # will retrieve the instance for Stock.stock_id = 1