python - Filter on hybrid property evaluates to "where false"
问题描述
I set up a mapping...
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.ext.hybrid import hybrid_property
Base = declarative_base()
class Chap(Base):
__tablename__ = 'chap'
id = Column(Integer, primary_key=True)
tophats = Column(Integer)
exclamation = Column(String)
@hybrid_property
def hat_brag(self):
return "I have "+str(self.tophats)+" tophats!"
@hybrid_property
def cold_complain(self):
return self.exclamation+", it's cold!"
...and I created a chap.
>>>c1 = Chap(tophats=5, exclamation="Blimey")
Now I want to query this chap based on how he complains about the weather:
>>>print(Session().query(Chap).filter(Chap.cold_complain == "Blimey, it's cold!"))
SELECT chap.id AS chap_id, chap.tophats AS chap_tophats, chap.gloves AS chap_gloves, chap.exclamation AS chap_exclamation
FROM chap
WHERE chap.exclamation || :exclamation_1 = :param_1
That SQL code looks right, but now I want to query him on another hybrid_property, one that contains an int
converted to a str
...
>>>print(Session().query(Chap).filter(Chap.hat_brag == "I have 5 tophats!"))
SELECT chap.id AS chap_id, chap.tophats AS chap_tophats, chap.exclamation AS chap_exclamation
FROM chap
WHERE false
WHERE false. That doesn't look like the right query! Anyone know what's going on?
解决方案
在查询中,SQLAlchemy 将使用混合属性的 getter 方法而无需专用@...expression()
方法来生成用于查询的必要 SQL 对象。getter 方法绑定到类,而不是实例,因此self
在这种情况下将引用您的Chap
类。
对于您的cold_complain
属性,这意味着Chap.exclamation + ", it's cold!"
返回,产生一个chap.exclamation || :exclamation_1
SQL 表达式;+
转换为 SQL 连接运算符。
但是对于你的hat_brag
属性返回一个字符串;执行的表达式是真的"I have "+str(Chap.tophats)+" tophats!"
,它变成'I have Chap.tophats tophats!'
:
>>> "I have "+str(Chap.tophats)+" tophats!"
'I have Chap.tophats tophats!'
那是一个固定的静态字符串。接下来,这将成为您过滤器的一部分,使用== "I have 5 tophats!"
. 这两个静态字符串值不相等:
>>> "I have "+str(Chap.tophats)+" tophats!" == "I have 5 tophats!"
False
这就是发送到数据库的 SQL 查询中使用的内容。
您在这里要做的是使用该expression()
选项并定义查询的 SQL 友好版本:
from sqlalchemy.sql.expression import cast
class Chap(Base):
# ...
@hybrid_property
def hat_brag(self):
return "I have "+str(self.tophats)+" tophats!"
@hat_brag.expression
def hat_brag(cls):
return "I have " + cast(cls.tophats, String) + " tophats!"
现在,该expression
方法用于查询,以及 Python 中实例上的原始函数:
>>> c1.hat_brag
'I have 5 tophats!'
>>> print(Session().query(Chap).filter(Chap.hat_brag == "I have 5 tophats!"))
SELECT chap.id AS chap_id, chap.tophats AS chap_tophats, chap.exclamation AS chap_exclamation
FROM chap
WHERE :param_1 || CAST(chap.tophats AS VARCHAR) || :param_2 = :param_3