如何在flask sqlchem中添加多列并将其作为单列返回

Question

我试图创建一个数据库模型，其中的列驱动其他列的列因此，在本例中，它是一个薪酬系统，其中total_benefits是一个列，它是添加其他列与总收益的结果

from flask_login import UserMixin
from datetime import datetime
from app import db
class Payroll(db.Model):
id = db.Column(db.Integer, primary_key=True)
firstname = db.Column(db.String(500),unique=False, nullable=False)
lastname = db.Column(db.String(500),unique=False, nullable=False)
email = db.Column(db.String(500),unique=True, nullable=False)
id_number = db.Column(db.String(500),unique=True, nullable=False)
bank_details = db.Column(db.String(500),unique=True, nullable=False)
retainer = db.Column(db.Float, unique=False, nullable=False)
discretionary_allowance = db.Column(db.Float, unique=False, nullable=True)
weekly_incentives = db.Column(db.Float, unique=False, nullable=True) 
overtime = db.Column(db.Float, unique=False, nullable=True) 
coupons_value = db.Column(db.Float, unique=False, nullable=True)
performance_incentives = db.Column(db.Float, unique=False, nullable=True)
anniversary_payout = db.Column(db.Float, unique=False, nullable=True)
shift_allowance = db.Column(db.Float, unique=False, nullable=True)
back_pay = db.Column(db.Fl`your text`oat, unique=False, nullable=True)
agent_benefits = db.Column(db.Float, unique=False, nullable=True)
total_earnings = db.column_property(retainer + discretionary_allowance + weekly_incentives + overtime + coupons_value + performance_incentives + anniversary_payout + shift_allowance +back_pay + agent_benefits, db.Float)
medical_aid = db.Column(db.Float, unique=False, nullable=True)
nssa = db.Column(db.Float, unique=False, nullable=True)
canteen = db.Column(db.Float, unique=False, nullable=True)
cicm = db.Column(db.Float, unique=False, nullable=True)
zol = db.Column(db.Float, unique=False, nullable=True)
withholding_tax = db.Column(db.Float, unique=False, nullable=True)
medical_loan = db.Column(db.Float, unique=False, nullable=True)
staff_Loan = db.Column(db.Float, unique=False, nullable=True)
independent_contractors_deduction = db.Column(db.Float, unique=False, nullable=True)
fixed_deductions = db.Column(db.Float, unique=False, nullable=True)
fixed_deductions_months = db.Column(db.Integer, unique=False, nullable=True)
total_fixed_deductions = db.column_property(fixed_deductions * fixed_deductions_months, db.Float)
total_deductions = db.column_property(medical_aid + nssa + canteen + cicm + zol + withholding_tax + medical_loan + staff_Loan + independent_contractors_deduction + total_fixed_deductions, db.Float)
net_Pay = db.column_property(total_earnings - total_deductions, db.Float)

def __repr__(self):
return f"Net Salary for ('{self.firstname}','{self.lastname}','is','{self.net_Pay}')" 

，但我收到以下错误:

net_Pay = db.column_property(total_earnings - total_deductions, db.Float)
~~~~~~~~~~~~~~~^~~~~~~~~~~~~~~~~~
TypeError: unsupported operand type(s) for -: 'ColumnProperty' and 'ColumnProperty'

Answer 1

当使用column_property()方法时，属性所在的模型类必须与SELECT表达式兼容。可以在SQLAlchemy中使用hybrid_property()法。你可以在这里找到细节。

from flask_login import UserMixin
from datetime import datetime
from app import db
from sqlalchemy.ext.hybrid import hybrid_property
class Payroll(db.Model):
id = db.Column(db.Integer, primary_key=True)
firstname = db.Column(db.String(500),unique=False, nullable=False)
lastname = db.Column(db.String(500),unique=False, nullable=False)
email = db.Column(db.String(500),unique=True, nullable=False)
id_number = db.Column(db.String(500),unique=True, nullable=False)
bank_details = db.Column(db.String(500),unique=True, nullable=False)
retainer = db.Column(db.Float, unique=False, nullable=False)
discretionary_allowance = db.Column(db.Float, unique=False, nullable=True)
weekly_incentives = db.Column(db.Float, unique=False, nullable=True) 
overtime = db.Column(db.Float, unique=False, nullable=True) 
coupons_value = db.Column(db.Float, unique=False, nullable=True)
performance_incentives = db.Column(db.Float, unique=False, nullable=True)
anniversary_payout = db.Column(db.Float, unique=False, nullable=True)
shift_allowance = db.Column(db.Float, unique=False, nullable=True)
back_pay = db.Column(db.Fl`your text`oat, unique=False, nullable=True)
agent_benefits = db.Column(db.Float, unique=False, nullable=True)
medical_aid = db.Column(db.Float, unique=False, nullable=True)
nssa = db.Column(db.Float, unique=False, nullable=True)
canteen = db.Column(db.Float, unique=False, nullable=True)
cicm = db.Column(db.Float, unique=False, nullable=True)
zol = db.Column(db.Float, unique=False, nullable=True)
withholding_tax = db.Column(db.Float, unique=False, nullable=True)
medical_loan = db.Column(db.Float, unique=False, nullable=True)
staff_Loan = db.Column(db.Float, unique=False, nullable=True)
independent_contractors_deduction = db.Column(db.Float, unique=False, nullable=True)
fixed_deductions = db.Column(db.Float, unique=False, nullable=True)
fixed_deductions_months = db.Column(db.Integer, unique=False, nullable=True)

def __repr__(self):
return f"Net Salary for ('{self.firstname}','{self.lastname}','is','{self.net_Pay}')"
@hybrid_property
def total_earnings(self):
return self.retainer + self.discretionary_allowance + self.weekly_incentives + self.overtime + self.coupons_value + self.performance_incentives + self.anniversary_payout + self.shift_allowance + self.back_pay + self.agent_benefits
@hybrid_property
def total_fixed_deductions(self):
return self.fixed_deductions * self.fixed_deductions_months
@hybrid_property
def total_deductions(self): 
return self.medical_aid + self.nssa + self.canteen + self.cicm + self.zol + self.withholding_tax + self.medical_loan + self.staff_Loan + self.independent_contractors_deduction + self.total_fixed_deductions
@hybrid_property
def net_Pay(self): 
return self.total_earnings - self.total_deductions

因此，您想要的Payroll实例的计算在Python中执行。

对于Payroll实例，可以通过以下方式访问total_earnings属性:

payroll.total_earnings