SQLAlchemy filter_by not working with float column type

user_18174191 0
Viewed 58

I am trying to filter by lat/long in a table of sites I have to prevent duplicate entries. However, I'm finding that filtering for these columns doesn't find a match that I would expect. Does anyone know why this filter might not be working? Thanks for any help

I am running these queries and comparing the results:

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

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

print(f'Filter test: {filter_test}')
no_filter = session.query(Sites).all()
for rec in no_filter:
   if rec.lat == data_dict['lat']:
       print(rec.country, rec.lat, rec.lng)
       print(rec.lat == data_dict['lat'])

The result from the first section (Querying with filtering) prints:

Filter test: []

However, the result of the second section (Querying all THEN filtering) prints:

United States 33.7838 -117.225
True
United States 33.7838 -117.225
True

My Sites table in SqlAlchemy looks like this:

class Sites(Base):
    __tablename__ = "sites"
    get_table_name = get_table_name
    country = Column(String(15))
    zipcode = Column(Integer)
    lat = Column(Float)
    lng = Column(Float)

Here is an image that shows a section of my sites table, with the records I am looking to filter for included: enter image description here

sqlalchemy python
2 Answers

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

user_58335900

I was able to figure out a solution by using a tolerance value. If the difference between the argument and the values in the table are below some Epsilon (0.005 in my case), then it properly fetches the expected results.

filter_test = session.query(Sites.lat, Sites.lng).filter(
        and_(
            func.abs(Sites.lat-data_dict['lat']) < 0.005,
            func.abs(Sites.lng-data_dict['lng']) < 0.005
        )
    ).all()

I looked at this link for help: https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.func

user_181741910
Related