SQL Alchemy hybrid_property with stored procedure

Radu Nicoara 0
Viewed 32

I am using SQLalchemy with postgresql, and wrote a procedure on my database, called calculate_validation_for_file(self.versionId)

Now, I want to write a hybrid_property that would automatically call this function in my selects.

In a form like SELECT name, id, calculate_validation_for_file(versionId) as isValid, deleted FROM my_table

I tried the following code, but I get an error:

@hybrid_property
def isValid(self):
    return func.calculate_validation_for_file(self.versionId)

But this returns an error: Boolean cannot represent a non boolean value: <Function instance>

I also tried with

@hybrid_property
def isValid(self):
    return select(func.calculate_validation_for_file(self.versionId))

But then I get the error: Boolean cannot represent a non boolean value: <Select instance>

So how do I write a hybrid_property using a stored procedure within my database?

stored-procedures sqlalchemy sql postgresql python
1 Answers

Because this value is in your database, and not in your mapper's dictionary, this attribute is more appropriately stored as a column_property, which will map a SQL expression to your class:

from sqlalchemy.orm import column_property
from sqlalchemy import select, func

class Version(Base):
    __tablename__ = 'version'

    versionId = Column(Integer, primary_key=True)

    isValid = column_property(
        func.calculate_validation_for_file(self.versionId)
    )

Note: some of the neat semantic arguments are possible due to SqlAlchemy being smart.

Yaakov Bressler0
Related