python - Filter method is behaving unexpectedly

Question

I'm trying to introduce type hints into an existing codebase, but I'm running into an issue when I attempt to type my query.

from sqlalchemy.orm.query import Query

class DbContext:
    def __init__(self, db_host, db_port, db_name, db_user, db_password):

        engine = create_engine(...)

        session = sessionmaker(bind=engine)
        self.Session: Session = session(bind=engine)

...

def fetch(context: DbContext, filters: ...):
    sub_query: Query = context.Session.query(...)

Before I added type hints, filtering dynamically was simply a matter of:

if filters.name is not None:
    sub_query = sub_query.filter(
        Person.name.ilike(f"%{filters.name}%"))

However, now with hinting I'm getting this error:

Expression of type "None" cannot be assigned to declared type "Query"

Sure enough, filter appears to return None:

(method) filter: (*criterion: Unknown) -> None

I navigated to the source and it appears the method does indeed not return anything.

def filter(self, *criterion):
    for criterion in list(criterion):
        criterion = expression._expression_literal_as_text(criterion)

        criterion = self._adapt_clause(criterion, True, True)

        if self._criterion is not None:
            self._criterion = self._criterion & criterion
        else:
            self._criterion = criterion

There's obviously a disconnect somewhere, as assigning None to sub_query should result in an error which the hint is warning against, but I need to perform the assignment for the filtering to actually work:

# Does NOT work, filtering is not applied
if filters.name is not None:
  sub_query.filter(
               Person.name.ilike(f"%{filters.name}%"))

# Works but Pylance complains
if filters.name is not None:
  sub_query = sub_query.filter(
               Person.name.ilike(f"%{filters.name}%"))

This is my first foray into Python, would love some guidance as to what is going on here!

question from:https://stackoverflow.com/questions/65602240/filter-method-is-behaving-unexpectedly

Answer 1

You are missing two things:

• You need to install typing stubs for SQLAlchemy.
• The Query.filter() method has a decorator that defines what is returned.

Typing stubs for SQLAlchemy

You want to install the sqlalchemy-stubs project, it provides stubs for the SQLAlchemy API.

Note that even with this stub installed you still will see issues with Pyright (the checking tool underpinning the Pylance extension), because static stubs cannot fully represent the dynamic nature of some parts of the SQLAlchemy API, such as model column definitions (e.g. if your Person model has a column called name, defined with name = Column(String), then the stubs can't tell Pyright that name will be a string). The sqlalchemy-stubs project includes a plugin for the mypy type checker to handle the dynamic parts better, but such plugins can't be used with other type checkers.

With the stubs installed, Pylance can tell you about filter:

Query.filter() decorator details

The Query.filter() method implementation is not actually operating on the original instance object; it has been annotated with a decorator:

    @_generative(_no_statement_condition, _no_limit_offset)
    def filter(self, *criterion):
        ...

The @_generative(...) part is significant here; the definition of the decorator factory shows that the filter() method is essentially replaced by this wrapper method:

    def generate(fn, *args, **kw):
        self = args[0]._clone()
        for assertion in assertions:
            assertion(self, fn.__name__)
        fn(self, *args[1:], **kw)
        return self

Here, fn is the original filter() method definition, and args[0] is the reference to self, the initial Query instance. So self is replaced by calling self._clone() (basically, a new instance is created and the attributes copied over), it runs the declared assertions (here, _no_statement_condition and _no_limit_offset are such assertions), before running the original function on the clone.

So, what the filter() function does, is alter the cloned instance in place, and so doesn't have to return anything; that's taken care of by the generate() wrapper. It is this trick of swapping out methods with utility wrappers that confused Pyright into thinking None is returned, but with stubs installed it knows that another Query instance is returned instead.