[Python] SqlAlchemy: How to create a class that runs business logic upon a query?

I'd like to create a class/object that I can use for querying, that contains business logic.

Constraints:

• Ideally that class/object is not the same one that is responsible for table creation.
• Alembic should not get confused.
• SQLAlchemy Version: 1.4 and 2.x.

How do I do that? Is that even possible?

Use Case


My database table has two column: value_a and show_value_a. show_value_a specifies if the value is supposed to be shown on the UI or not. Currently, all processes that query value_a have to check if show_value_a is True; If not, the value of value_a will be masked (= set to None) upon returning.

Masking the value is easy to forget. Also, each process has their own specific query (with their specific JOINs), so it's ineffective to do this in some kind of pattern form.

Example


Table definiton

from sqlalchemy import Column, String, Boolean

class MyTable(Base):
__tablename__ = "mytable"

valueA = Column("value_a", String(60), nullable=False)
showValueA = Column("show_value_a", Boolean, nullable=False)



Data

value_a | show_value_a
---------|---------------
"A" | True
"B" | False
"C" | True



Query I'd like to do

values = session.query(MyTable.valueA).all()
# returns ["A", None, "C"]


↑ Querying the field will intrinsically check if show_value_a is True. If it is, the value is returned. If not, None is returned

Continue reading...