Is it possible to evaluate sqlalchemy sentences without a database?

Question

I make extensive use of the ORM features in sqlalchemy, so in many contexts I already have data loaded from the database and you want to check conditions or perform calculations on already loaded python objects; I also want / need to perform more batch-oriented tasks, which are better expressed when sql is executed for the database (and not for loading data at all). I would like to use the same code to express the same calculations for both uses, so I don’t have to bend back to connect to the database or write down each calculation twice (once in regular python, again as queries) and run 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 any way to get

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

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

One option is to change the feature suggestions:

 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 think this is less readable than the original way of expressing sentences.

Answer 1

There are several ways you can approach this way.

One way is that SQLAlchemy has a module in the Query.update () and Query.delete () methods that does this with the name sqlalchemy.orm.evaluator. It can only express 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 does not execute more complex expressions such as in_() , however we are open to any number of reasonable operations added to this module if you want to contribute.

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

 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 actually uses the same idea as your suggestion to use lambdas, just a more expressive expression.

Both approaches can be combined. One great thing about the evaluator is that it handles joins like or_() and and_() . Without this, hybrids require you to decompose methods into instance and expression methods if you need to use something like "and" or "or".