python - sqlalchemy column_property 基于其他行

问题描述

我有模型expense，income它们分别具有多对多关系refunds和expenses. 我想在expense表上创建一个 column_property，它被调用refunded_amount并根据以下伪代码填充（费用由相同的过滤refund.income_id）：

expenses = [exp1, exp2]
payment = income.amount
for exp in expenses:
    amt = exp.amount
    if amt <= payment:
        exp.refunded_amount += amt
        payment -= amt
    else:
        exp.refunded_amount = payment
        payment = 0

如何将其转换为 sqlalchemy 并将其包含在refunded_amountcolumn_property 的 select 语句中？这些是当前的模型，但就像这样refunded_amount，每项费用都包含与该特定收入相关的所有费用的总和。

refund_table = Table(
    "refund", Base.metadata,
    Column("expense_id", Integer, ForeignKey("expense.id"), primary_key=True),
    Column("income_id", Integer, ForeignKey("income.id"), primary_key=True)
)


class Income(Base):
    __tablename__ = "income"

    id = Column(Integer, primary_key=True)
    date = Column(DateTime, nullable=False)
    amount = Column(Float)
    refund = Column(Boolean, default=False, nullable=False)
    expenses = relationship("Expense", secondary=refund_table,
                            back_populates="refunds")


class Expense(Base):
    __tablename__ = "expense"

    id = Column(Integer, primary_key=True)
    date = Column(DateTime, nullable=False)
    refunds = relationship("Income", secondary=refund_table,
                           back_populates="expenses")

    refunded_amount = column_property(
        select(
            [func.sum(Income.amount)],
            and_(
                refund_table.c.expense_id == id,
                refund_table.c.income_id == Income.id
            ),
            refund_table
        ).label("refunded_amount")
    )

重要的是，如果可能的话，我想使用声明性并避免回退到@property我可以使用对象的当前会话的经典 Python，因为如sqlalchemy 文档中所述：

普通描述符方法作为最后的手段很有用，但在通常情况下的性能不如混合和列属性方法，因为它需要在每次访问时发出 SQL 查询。

标签： pythonsqlalchemy

如果您只取最小值Expense.amount并且sum(Income.amount)返回所需的结果：

refunded_amount = column_property(
    select(
        [func.min(amount, func.sum(Income.amount))],
        and_(
            refund_table.c.expense_id == id,
            refund_table.c.income_id == Income.id
        ),
        refund_table
    ).label("refunded_amount")
)

python - sqlalchemy column_property 基于其他行

问题描述

解决方案

推荐阅读