Search code examples
pythonsqliteflasksqlalchemyflask-sqlalchemy

sqlalchemy.exc.ArgumentError: Column expression, FROM clause, or columns clause element expected, got sqlalchemy.orm.attributes.InstrumentedAttribute

I have 2 flask models. Simplified below:

class Product(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  weight = db.Column(db.Integer, default=0)

class Package(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  quantity = db.Column(db.Integer, default=0)
  product_id = db.Column(db.Integer, db.ForeignKey('product.id'))
  product = db.relationship('Product')

  @hybrid_property
  def total_weight(self):
     return self.quantity * (self.product.weight if self.product else 0)

  @total_weight.expression
  def total_weight(cls):
     weight_column = select([Product.weight]).where(Product.id == cls.product_id).label('product_weight')
     
     return case([(cls.product_id.isnot(None), cast(cls.quantity * weight_column, Integer)),], else_=0).label('total_weight')

I'm trying find the sum of total_weight of all packages, using the following query:

db.session.query(func.sum(Package.total_weight)).scalar()

I also tried:

db.session.query(func.sum(Package.total_weight.expression())).scalar()

But in both cases, I get the following error: sqlalchemy.exc.ArgumentError: Column expression, FROM clause, or other columns clause element expected, got [<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x1103e0220>]. Did you mean to say select(<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x1103e0220>)?

I'm looking for either the correct sql express or an alternative to hybrid_property expression.

If it helps, my current database is a sqlite3 database.

Solution

  • You seem to be using legacy syntax despite having SQLAlchemy 2.0 installed.

    Change

    select([Product.weight])

    to

    select(Product.weight)

    and

    case([(cls.product_id.isnot(None), cast(cls.quantity * weight_column, Integer)),], else_=0).label('total_weight')

    to

    case((cls.product_id.isnot(None), cast(cls.quantity * weight_column, Integer)), else_=0).label('total_weight')

    In both cases you are removing square brackets which are no longer used in SQLA 2.0. See this entry in the migration guide.