我有以下sqlalchemy
类(简化):
class Persons(Base):
__tablename__ = 'm_persons'
birthDate = db.Column(db.Date, nullable=True)
@hybrid_property
def age(self):
bd = self.birthDate
return relativedelta(date.today(), bd).years
@age.expression
def age(cls):
bd = cls.birthDate
return relativedelta(date.today(), bd).years
当我使用属性将值打印为
^{pr2}$它能正确地打印出人的年龄。现在,当我尝试在查询中使用该属性时,例如:
Persons.query.filter(Persons.age >= min_age).all()
然后我得到了以下错误:
TypeError: relativedelta only diffs datetime/date
我理解为cls.birthDate
是类型
class 'sqlalchemy.orm.attributes.InstrumentedAttribute'
所以问题是我缺少什么来获得属性birthDate
的值?在
当然,我一直在“google”搜索并阅读了doc,但找不到它不起作用的原因或解决方案。在
任何帮助都将不胜感激。在
***编辑**** 错误的痕迹:
File "/Users/ext334/Dev/python/asocio/app/members/forms.py", line 174, in __init__
self.person.choices = listPersons(gender=subscription.gender, min_age=subscription.min_age, max_age=subscription.max_age)
File "/Users/ext334/Dev/python/asocio/app/members/forms.py", line 31, in listPersons
print(Persons.query.join(Members).filter(Persons.age >= min_age, Persons.age <= max_age, extract('year',Persons.birthDate) >= minBirthYear, extract('year',Persons.birthDate) <= maxBirthYear, Persons.sex == gender).order_by(Members.lastname, Members.firstname))
File "/Users/ext334/Dev/python/venv36/lib/python3.6/site-packages/sqlalchemy/ext/hybrid.py", line 867, in __get__
return self._expr_comparator(owner)
File "/Users/ext334/Dev/python/venv36/lib/python3.6/site-packages/sqlalchemy/ext/hybrid.py", line 1066, in expr_comparator
owner, self.__name__, self, comparator(owner),
File "/Users/ext334/Dev/python/venv36/lib/python3.6/site-packages/sqlalchemy/ext/hybrid.py", line 1055, in _expr
return ExprComparator(cls, expr(cls), self)
File "/Users/ext334/Dev/python/asocio/app/members/models.py", line 235, in age
return relativedelta(date.today(), cls.birthDate).years
File "/Users/ext334/Dev/python/venv36/lib/python3.6/site-packages/dateutil/relativedelta.py", line 102, in __init__
raise TypeError("relativedelta only diffs datetime/date")
TypeError: relativedelta only diffs datetime/date
完整查询:
Persons.query.join(Members).filter(Persons.age >= min_age, Persons.age <= max_age, extract('year',Persons.birthDate) >= minBirthYear, extract('year',Persons.birthDate) <= maxBirthYear, Persons.sex == gender).order_by(Members.lastname, Members.firstname)
全班同学:
class Persons(Base):
"""
Description of a person
"""
__tablename__ = 'm_persons'
user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE', onupdate='CASCADE'))
family_id = db.Column(db.Integer, db.ForeignKey('m_families.id', ondelete='CASCADE', onupdate='CASCADE'))
birthDate = db.Column(db.Date, nullable=True)
sex = db.Column(db.String(1), nullable=False, default='U') # U = unknown, F = Female, M = Male
# return the age in years, not rounded
@hybrid_property
def age(self):
return relativedelta(date.today(), self.birthDate).years
@age.expression
def age(cls):
return relativedelta(date.today(), cls.birthDate).years
好吧,我找到了一个解决办法,或者也许我更了解它是如何工作的。 在@年龄.表情您可以执行数据库的功能。所以我决定计算这里面的年龄@年龄.表情基于MySQL
代码如下:
基本上,这是计算从出生日期到现在之间的年数。所以flask sqlalchemy查询是
^{pr2}$生成的sql语句是:
因此@年龄.表情返回
year(from_days(to_days(%s) - to_days(m_persons.birthDate)))
,这在查询中得到了很好的理解作为总结,我想说的是@年龄.表情只能应用数据库函数,不能真正对列本身进行操作。在
相关问题 更多 >
编程相关推荐