in Sqlalchemy what is the difference between filter and filter_by

Asked

Viewed 377 times

0

Is there any difference between the function filter and filter_by in Sqlalchemy?

If it exists, when should I use each of them properly?

python sqlalchemy

1 answer

2

filter

If you check the method documentation Query.filter(*criterions) you will see that it receives a variable number of positional parameters. Examples of use would be:

session.query(MyClass).filter(MyClass.name == 'some name')
session.query(MyClass).filter(MyClass.name == 'some name', MyClass.id > 5)
session.query(MyClass).filter(MyClass.name == 'some name', 
                              MyClass.id > 5,
                              MyClass.other == 'teste')

As you can see, Query.filter will receive a sequence with the search criteria.

filter_by

Now, checking the documentation of the method Query.filter_by(**kwargs) you may already notice that it receives a variable amount of named parameters. Some examples of use:

session.query(MyClass).filter_by(name='some name')
session.query(MyClass).filter_by(name='some name', id=5)
session.query(MyClass).filter_by(name='some name', id=5, other='teste')

What’s the difference between them?

The main difference is that filter works with SQL expressions and filter_by works only with equality between the key and values of its arguments.

In practice, filter_by only works with comparison of equality between the keys and their values, while filter can work with most versatile operators.

session.query(MyClass).filter(MyClass.name == 'some name')
# seria o mesmo que
session.query(MyClass).filter_by(name='some name')

# Não possuem versão compatível usando `filter_by`
session.query(MyClass).filter(MyClass.id < 5)
session.query(MyClass).filter(MyClass.id != 8)

This is proven when you look at the method code Query.filter_by:

class Query(Generative):

    def filter(self, *criterion):
        ...

    def filter_by(self, **kwargs):
        clauses = [
            _entity_descriptor(self._joinpoint_zero(), key) == value
            for key, value in kwargs.items()
        ]
        return self.filter(*clauses)

See that the filter_by under the table transforms the named arguments into SQL expressions of equality and passes to filter.

Recommended readings

Browser other questions tagged python sqlalchemy

You are not signed in. Login or sign up in order to post.