pythonsqlalchemyexpressionpeewee

How can I pass an expression to use in a SQLAlchemy filter or where clause around?


I am trying to reproduce a behavior from ORMs like sqlalchemy and peewee that allow to pass an entire expression as a single argument to a function. For example in sqlalchemy or in peewee that i prefer to use as a reference because the source code is a lot easier to assimilate, we can do as follow:

grandma = Person.select().where(Person.name == 'Grandma L.').get()

What i am trying to understand and reproduce is how to handle the expression provided as argument to where(), the Person.name == 'Grandma L.' part, to get it from within the function so i can convert it into an SQL statement later on.

I know i could put the expression in quotes to pass it as a one string argument but that is not very pythonic and neither peewee nor sqlalchemy had to do this. I tried to see how they do it by examining the source code and what i believe to be de where() function used for select queries but there is almost nothing in this function and i can't figure out how they handle the expression has an argument. They seem to simply have *expression as parameter which like *args accepts an unknown number of arguments. I also checked the where() function of sqlalchemy and they also somehow use the form *whereclause to receive the expression.

I know even less how to do it with complex expression like this:

query = Person.select().where((Person.birthday < d1940) | (Person.birthday > d1960)))

From what i understand after a lot of testing python will always evaluate the expression passed as argument and instead pass the result of the expression to the function, so is it possible to get the expression itself from inside the function?


Solution

  • You don't need to pass "the expression itself" -- the result of evaluating the expression is an object that describes to SQLAlchemy what the filters you want to apply are. (SQLAlchemy does this by overloading <, ==, and other operators for the column objects such that instead of returning the result of a single comparison, they return an object that describes that comparison itself; from the calling convention you've shown, I assume Peewee does something similar).

    Thus:

    conditions = [(Person.birthday < d1940) | (Person.birthday > d1960)]
    

    ...can have the result passed around as much as you like (and can have other conditions added to the list), eventually being used as:

    query = Person.select().where(*conditions)
    

    Let's look at a runnable example:

    from sqlalchemy import String, Integer, Column, create_engine
    from sqlalchemy.orm import Session, DeclarativeBase
    
    class Base(DeclarativeBase):
        ...
    
    class Person(Base):
        __tablename__ = 'person'
        id = Column(Integer, primary_key=True)
        name = Column(String)
        birth_year = Column(Integer)
    
    engine = create_engine('sqlite:///:memory:', echo=True)
    Base.metadata.create_all(engine)
    
    query_terms = [
      (Person.name == 'Grandma L.')
    ]
    print(query_terms)
    

    The output of that print is something like:

    [<sqlalchemy.sql.elements.BinaryExpression object at 0x7ffff719ba10>]
    

    That is, it's not a specific True or False value, but an object that SQLAlchemy can later use when that object is passed to where() or similar.