Difference Between Filter and Filter_By in SQLalchemy

Difference between filter and filter_by in SQLAlchemy

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

Difference between .filter() and .where() in sqlalchemy

According to the documentation, there is no difference.

method sqlalchemy.orm.Query.where(*criterion)

A synonym for Query.filter().

It was added in version 1.4 by this commit. According to the commit message the reason to add it was to Convert remaining ORM APIs to support 2.0 style.

You can read more about "2.0 style" here.

SQLAlchemy filter_by not working with float column type

I suspect this could be an issue in equal comparison to floating point number. It can be rather problematic to compare a python float value to a database floating point data type.

The safest way is likely to cast to decimal (decimal(n,m)) when performing the comparison

from sqlalchemy import cast, DECIMAL

data_dict = {'lat':33.7838, 'lng':-117.225}

filter_test = session.query(Sites.lat, Sites.lng).filter(
        and_(
            cast(Sites.lat, Decimal(10,4)) == data_dict['lat'],
            cast(Sites.lng, Decimal(10,4)) == data_dict['lng']
        )
    ).all()

There are other options as well (multiply by a constant 10^n and cast to integer) when running the comparison, or also running a comparison for similar values / rounding values.

There is a similar discussion on a separate question

SQLAlchemy filter based on comparison of own columns

To retrieve rows where total > previous_total the SQLAlchemy query would be

my_query = session.query(MyModel).filter(MyModel.total > MyModel.previous_total)

or if you are using something that injects the session into the model class, like flask-sqlalchemy:

my_query = MyModel.filter(MyModel.total > MyModel.previous_total)

---

Related Topics