情况
用户进行购买,这些购买作为交易存储在 3 个不同的表中(取决于类型(。我需要计算女性和男性用户的交易/购买总额,所以我需要查看所有 3 个表。
为此,我在用户表中创建了一个@property
:
@property
def count_credits_purchases(self):
trans = object_session(self).query(Transaction_1).filter(Transaction_1.type == "credits").with_parent(self).count()
trans_vk = object_session(self).query(Transaction_2).filter(Transaction_2.type == "credits").with_parent(self).count()
trans_stripe = object_session(self).query(Transaction_3).filter(Transaction_3.type == "credits").with_parent(self).count()
value = trans + trans_vk + trans_stripe
return int(value)
我正在尝试使用 sqlalchemyfunc.sum()
来计算购买总额:
total_purchases_males_credits = db_session.query(func.sum(Users.count_credits_purchases))
.filter(Users.date_added >= start_date, Users.date_added <= end_date, Users.gender == "1")
.scalar()
问题
AttributeError: 'property' object has no attribute 'translate'
翻译方法是字符串方法,这里发生了什么?我肯定会在count_credits_purchases
中返回一个整数.
我做了一个测试,检查每个用户的值总是正确的:
all_users = db_session.query(Users).limit(200)
for user in all_users:
print (user.count_credits_purchases) # gives correct result
我可以创建一个变量并在循环中计算它,但它效率非常低,如果有 1k 用户,可能需要 50 小时。我需要了解如何使用@property
属性
正如使用描述符和混合文档所说,您应该使用hybrid_property
才能在查询中使用它们。
看看文档中的示例:
class EmailAddress(Base): __tablename__ = 'email_address' id = Column(Integer, primary_key=True) # name the attribute with an underscore, # different from the column name _email = Column("email", String) # then create an ".email" attribute # to get/set "._email" @property def email(self): return self._email
虽然我们的
EmailAddress
对象将通过电子邮件描述符将值穿梭到_email映射属性中,但类级别EmailAddress.email
属性没有可用于 Query 的常用表达式语义。为了提供这些,我们改用hybrid
扩展
这里最好的解决方案可能是使用@hybrid_property
但我在使其工作时遇到了问题。
我想出了一个完全不同的解决方案,使用经典的方法。这是超级快的,到目前为止我没有看到任何缺点:
# Normal method to calculate | Best case would probably be @hybrid_method
def count_credits_purchases(self, start_date, end_date, gender):
trans = db_session.query(Transaction_1).filter(Transaction_1.type == "credits", Transaction_1.user_id == Users.id).filter(Users.date_added >= start_date, Users.date_added <= end_date, Users.gender == gender).count()
trans_vk = db_session.query(Transaction_2).filter(Transaction_2.type == "credits", Transaction_2.user_id == Users.id).filter(Users.date_added >= start_date, Users.date_added <= end_date, Users.gender == gender).count()
trans_stripe = db_session.query(Transaction_3).filter(Transaction_3.type == "credits", Transaction_3.user_id == Users.id).filter(Users.date_added >= start_date, Users.date_added <= end_date, Users.gender == gender).count()
value = trans + trans_vk + trans_stripe
return value
调用 python:
total_purchases_males_credits = Users().count_credits_purchases(start_date, end_date, "1")
我仍然想知道与hybrid_property相比,这种方法有多好?
编辑:
也可以使用@hybrid_method
:
@hybrid_method
def count_credits_purchases(self, start_date, end_date, gender):
trans = db_session.query(Transaction_1).filter(Transaction_1.type == "credits", Transaction_1.user_id == Users.id).filter(Users.date_added >= start_date, Users.date_added <= end_date, Users.gender == gender).count()
trans_vk = db_session.query(Transaction_2).filter(Transaction_2.type == "credits", Transaction_2.user_id == Users.id).filter(Users.date_added >= start_date, Users.date_added <= end_date, Users.gender == gender).count()
trans_stripe = db_session.query(Transaction_3).filter(Transaction_3.type == "credits", Transaction_3.user_id == Users.id).filter(Users.date_added >= start_date, Users.date_added <= end_date, Users.gender == gender).count()
value = trans + trans_vk + trans_stripe
return value
并使用它:
total_purchases_males_credits = db_session.query(func.sum(Users.count_credits_purchases(start_date, end_date, "1"))).scalar()