[python] 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?

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)

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.

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