How to format a decimal and string as part of a hybrid expression?

Question

Question

I created a hybrid property which is composed of a string and a decimal formatted as percentage but I am getting a TypeError when using the hybrid expression. I've tried several variations on the f-string including converting it to float first but I still get the error on the same line. What is the best way to do this string formatting and concatenation on the hybrid property expression?

I want to know why 'result_1' is producing an error, and 'result_2' works correctly

Model

from decimal import Decimal as D
class SupplierDiscount(Base):
    __tablename__ = "tblSupplierDiscount"
    id = Column(Integer, primary_key=True)
    discount = Column(DECIMAL(5, 4), nullable=False)
    description = Column(String, nullable=False)
    
    
    @hybrid_property
    def disc_desc(self):
        return f'{self.description}: {self.discount * 100:.4f}%'


    @disc_desc.expression
    def disc_desc(cls):
        return f'{cls.description}: {cls.discount * 100:.4f}%' # Error generated here

result_1 - Preferred method - but results in error

result_1 = session.query(SupplierDiscount.id.label("SDId"),
                         SupplierDiscount.disc_desc.label("SDDDesc")
                         ).all()
print('Below is from result_1')
print(result_1)
for i in result_1:
    print(i.id, i.disc_desc)

Error produced in result_1

TypeError: unsupported format string passed to BinaryExpression.__format__

result_2 - This works but this is not the preferred method

result_2 = session.query(SupplierDiscount).all()
print('Below is from result_2')
print(result_2)
for i in result_2:
    print(i.id, i.disc_desc)

Environment

SQLAlchemy==1.3.20
PostgreSQL 13

rfkortekaas · Accepted Answer · 2021-03-29T07:04:58.260

There is a distinction between class-level and instance-level access. The hybrid property is used on instance-level and uses python string formatting to get the desired result. As the expression is used for class-level access this needs to be defined as an SQL expression to return the desired result. See the docs for reference.

Knowing this we can rewrite the code to the following to achieve the same result on class-level and instance-level:

from sqlalchemy import Column, Integer, create_engine, DECIMAL, String, func
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import Session

Base = declarative_base()

class Discount(Base):
    __tablename__ = 'Discount'
    id = Column(Integer, primary_key=True)
    description = Column(String)
    discount = Column(DECIMAL(5,4))

    @hybrid_property
    def disc_desc(self):
        return f'{self.description}: {self.discount * 100:.4f}%'

    @disc_desc.expression
    def disc_desc(cls):
        return cls.description + ' ' + func.cast(cls.discount * 100, String) + '%'


engine = create_engine(dburl)

Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

with Session(engine) as session:
    discount = Discount(description='test', discount=5.4)

    session.add(discount)
    session.commit()

    disc = session.query(Discount).first()
    print('Instance-level access')
    print(disc.disc_desc)
    print('')

    disc = session.query(Discount.disc_desc).first()
    print('Class-level access')
    print(disc.disc_desc)

This results in:

Instance-level access
test: 540.0000%

Class-level access
test: 540.0000%