411

Could anyone explain the difference between filter and filter_by functions in SQLAlchemy? Which one should I be using?

Anand Tripathi
  • 14,556
  • 1
  • 47
  • 52
bodacydo
  • 75,521
  • 93
  • 229
  • 319

5 Answers5

537

filter_by is used for simple queries on the column names using regular kwargs, like

db.users.filter_by(name='Joe')

The same can be accomplished with filter, not using kwargs, but instead using the '==' equality operator, which has been overloaded on the db.users.name object:

db.users.filter(db.users.name=='Joe')

You can also write more powerful queries using filter, such as expressions like:

db.users.filter(or_(db.users.name=='Ryan', db.users.country=='England'))

Jonathan Hartley
  • 15,462
  • 9
  • 79
  • 80
Daniel
  • 26,899
  • 12
  • 60
  • 88
  • 27
    How does this work under the hood? Would not `db.users.name=='Ryan'` evaluate once to a constant and then be meaningless from then on? It seems like one would need to use a lambda for this to work. – Hamish Grubijan Feb 27 '13 at 23:11
  • 60
    the equality operator is overloaded – Daniel Feb 27 '13 at 23:12
  • 12
    `type(model.column_name == 'asdf')` → `sqlalchemy.sql.elements.BinaryExpression` – Nick T May 22 '17 at 16:53
  • 31
    Be careful when using `.filter`. a query like `id=12345`, `query(users).filter(id == id)` will not filter on `users.id`. Instead, it will evaluate `id == id` as `True` and return all users. You need to use `.filter(users.id == id)` (as demoed above). I made this mistake earlier today. –  Feb 22 '19 at 23:00
138

We actually had these merged together originally, i.e. there was a "filter"-like method that accepted *args and **kwargs, where you could pass a SQL expression or keyword arguments (or both). I actually find that a lot more convenient, but people were always confused by it, since they're usually still getting over the difference between column == expression and keyword = expression. So we split them up.

SuperShoot
  • 9,880
  • 2
  • 38
  • 55
zzzeek
  • 72,307
  • 23
  • 193
  • 185
  • 36
    I think your point about `column == expression` vs. `keyword = expression` is the key point to make about the difference between `filter` and `filter_by`. Thanks! – Hollister Dec 12 '10 at 18:03
  • This is a nice related question: [http://stackoverflow.com/questions/19506105/flask-sqlalchemy-query-with-keyword-as-variable]. – Soferio Sep 12 '14 at 14:10
  • 3
    I'm new to sqlalchemy so excuse me if this is a stupid question, but filter_by() doesn't seem to allow for even the very simple conditions such as "price >= 100". So, why have filter_by() function anyway, if you only can use it for the very simplest condition such as "price = 100"? – PawelRoman Oct 12 '14 at 20:39
  • 20
    because people like it – zzzeek Oct 13 '14 at 00:19
  • 3
    Is there any performance difference between them? I was thinking that `filter_by` might be a bit faster than `filter`. – Devi Apr 30 '15 at 05:38
  • 12
    The point of using `filter_by` is to be able to write jut the field name, for that class, no questions asked - while `flter` requires the actual column object - which usually will require one to type (and to read) at least a redundant class name. So, if one wants to filter by equality, it is rather convenient. – jsbueno May 18 '16 at 14:32
  • I like `filter_by` for building convenience functions into an app, which allow users to run simple queries by just passing in a table name and dictionary of equality filters. Beyond this, though, it's difficult to see why I wouldn't just use `filter`. – Todd Jun 09 '20 at 15:20
  • Sometime filter_by is trick, eg. filter_by(customer.created_time < some_time) it cannot select the correct value, for some reason, the `<` always got true. – Jaden May 23 '21 at 18:42
69

It is a syntax sugar for faster query writing. Its implementation in pseudocode:

def filter_by(self, **kwargs):
    return self.filter(sql.and_(**kwargs))

For AND you can simply write:

session.query(db.users).filter_by(name='Joe', surname='Dodson')

btw 

session.query(db.users).filter(or_(db.users.name=='Ryan', db.users.country=='England'))

can be written as 

session.query(db.users).filter((db.users.name=='Ryan') | (db.users.country=='England'))

Also you can get object directly by PK via get method:

Users.query.get(123)
# And even by a composite PK
Users.query.get(123, 321)

When using get case its important that object can be returned without database request from identity map which can be used as cache(associated with transaction)

AgentBawls
  • 109
  • 12
enomad
  • 1,053
  • 9
  • 16
  • These code examples are misleading: Declarative base table classes and instances have neither filter nor query methods; they use the session. – Turtles Are Cute Mar 16 '16 at 19:45
  • I reproduce `users.filter` from previous answer. And may be its my fault :) `query` attribute is [query_property](http://docs.sqlalchemy.org/en/latest/orm/contextual.html?highlight=query_property#sqlalchemy.orm.scoping.scoped_session.query_property) and its quite a standard sugar nowadays – enomad Mar 16 '16 at 21:47
42

filter_by uses keyword arguments, whereas filter allows pythonic filtering arguments like filter(User.name=="john")

Luper Rouch
  • 9,304
  • 7
  • 42
  • 56
Johannes Charra
  • 29,455
  • 6
  • 42
  • 51
7

Apart from all the technical information posted before, there is a significant difference between filter() and filter_by() in its usability.

The second one, filter_by(), may be used only for filtering by something specifically stated - a string or some number value. So it's usable only for category filtering, not for expression filtering.

On the other hand filter() allows using comparison expressions (==, <, >, etc.) so it's helpful e.g. when 'less/more than' filtering is needed. But can be used like filter_by() as well (when == used).

Just to remember both functions have different syntax for argument typing.

industArk
  • 189
  • 2
  • 5