[FIXED] SQLAlchemy column defined by 'column_property' not showing up when accessing the model's columns

Issue

I want a column, total in my sqlalchemy model that is equal to amount * price. The column value can be accessed by object.total but the column name doesn't show up in model.__table__.columns.keys()

I have tried setting total as a hybrid_property and a column_property. Neither works to include the column when I access the columns.

This is my model:

class RawMaterials(db.Model):
    __tablename__ = 'raw_materials'
    id = db.Column(db.Integer, primary_key=True)
    type = db.Column(db.String(64))
    product = db.Column(db.String(64))
    amount = db.Column(db.Integer)
    price = db.Column(db.Integer)
    total = column_property(amount * price)

    def __repr__(self):
        return '<id:{} product:{}>'.format(self.id, self.product)

and this is where I query the model for columns:

@app.route('/tables')
@login_required
def tables(methods=["GET", "POST"]):
    if request.args.get('data'):
        table = request.args.get('data').lower().replace(" ","_")
    else:
        table = 'raw_materials'
    database = get_db_model(table)
    materials = database.query.all()
    columns = database.__table__.columns.keys()
    new_mats = format_data(materials, columns)
    title = table.replace("_", " ").upper()
    return render_template('tables.html.j2', title=title, materials=materials, columns=columns)

If I print out the columns variable into the console, it returns: ['id', 'type', 'product', 'amount', 'price'] and I want it to return: ['id', 'type', 'product', 'amount', 'price', 'total']

I expect total to be in the columns but it is not.

Solution

When using the column_property() function, you need to make sure that the expression is compatible with the SELECT statement emitted for your RawMaterials class. You can try to use a Hybrid, instead.

from sqlalchemy.ext.hybrid import hybrid_property

class RawMaterials(db.Model):
    __tablename__ = 'raw_materials'
    id = db.Column(db.Integer, primary_key=True)
    type = db.Column(db.String(64))
    product = db.Column(db.String(64))
    amount = db.Column(db.Integer)
    price = db.Column(db.Integer)

    def __repr__(self):
        return '<id:{} product:{}>'.format(self.id, self.product)

    @hybrid_property
    def total(self):
        return self.amount * self.price

Here the total property returns the multiplication of the amount and price attributes. With an instance of RawMaterials, this multiplication occurs in Python, using standard Python descriptors.

Answered By - Gabe