Can sqlalchemy clauses be evaluated without a database?

Question

I make extensive use of the ORM facuilities in sqlalchemy, so in many contexts, I already have data loaded from the database, and want to check conditions or perform calculations on the python objects that are already loaded; I also want/need to do more batch oriented tasks that are better expressed by executing sql against the database (and not loading data at all). I would like to use the same code to express the same calculations for both uses, so that I don't have to bend over backwards for a database connection or write each computation twice (once in regular python, again as queries) and run the risk that they disagree.

suppose I have:

from sqlalchemy import Integer, Column
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    bar = Column(Integer)

bool_clause = Foo.bar > 10
int_clause = Foo.bar + 10

a_foo = Foo(bar=5)

Is there a way to get

>>> something(bool_clause, a_foo)
False
>>> something(int_clause, a_foo)
15

without first persisting a_foo to a database and then executing the query? I specifically want a way to express the clause so that it can also be used in the context of a database query, but still useful without one.

One option would be to change the clauses to functions:

bool_clause = lambda foo=Foo: foo.bar > 10
int_clause = lambda foo=Foo: foo.bar + 10

>>> bool_clause(a_foo)
False
>>> int_clause(a_foo)
15

but I find this to be less readable than the original way the clauses were expressed.

Answer 1

There's a few ways this kind of thing can be approached.

One way is head on, there is a module in SQLAlchemy used by the Query.update() and Query.delete() methods which does this called sqlalchemy.orm.evaluator. It's capable only of expressing a very limited set of expression operators:

>>> from sqlalchemy.orm.evaluator import EvaluatorCompiler
>>> print EvaluatorCompiler().process(bool_clause)(a_foo)
False
>>> print EvaluatorCompiler().process(int_clause)(a_foo)
15

It doesn't do more complex expressions such as in_() however we are open to any number of reasonable operations being added to this module if you feel like contributing.

Now the way the use case you have is usually done is using hybrid properties and methods. In this use case, we take advantage of the awesomeness of Python in that when we have <anything>.someattr <some operator> <somethingelse>, we can swap out self and cls for <anything>. So your example would be:

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    bar = Column(Integer)

    @hybrid_method
    def bool_clause(self, other):
        return self.bar > other

    @hybrid_method
    def int_clause(self, other):
        return self.bar + other

>>> a_foo = Foo(bar=5)
>>> print a_foo.bool_clause(10)
False
>>> print a_foo.int_clause(10)
15

This is actually using the same idea as your suggestion to use lambdas, just expressed more nicely.

The two approaches can be combined as well. One nice thing about the evaluator is that it handles conjunctions like or_() and and_(). Without this, hybrids require that you break up the methods into "instance" and "expression" methods if you need to use something like "and" or "or".