CODEWITHSUNDEEP
pythonsqlalchemy
bodacydo
bodacydo

Reputation: 79299

Difference between filter and filter_by in SQLAlchemy

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

Upvotes: 426

Views: 314654

Answers (5)

industArk
industArk

Reputation: 369

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.

Upvotes: 9

zzzeek
zzzeek

Reputation: 75107

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.

Upvotes: 141

enomad
enomad

Reputation: 1135

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)

Upvotes: 75

Daniel
Daniel

Reputation: 27549

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'))

Upvotes: 551

Johannes Charra
Johannes Charra

Reputation: 29913

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

Upvotes: 43

Related Questions