Flask SQLAlchemy filter and filter_by don't work with float values

Question

I'm writing API using Flask, Flask-RESTful, Flask-SQLAlchemy and MySQL. The problem is that filter() and filter_by don't work at all.

Model:

from app import db
from datetime import datetime

class AreaModel(db.Model):
    __tablename__ = 'aqi_records'

    id = db.Column(
        db.Integer,
        primary_key=True
    )
    aqi = db.Column(
        db.Integer,
        index=False,
        unique=False,
        nullable=False
    )
    latitude = db.Column(
        db.Float,
        index=False,
        unique=False,
        nullable=False
    )
    longitude = db.Column(
        db.Float,
        index=False,
        unique=False,
        nullable=False
    )
    created = db.Column(
        db.DateTime,
        index=False,
        unique=False,
        nullable=False,
        default=datetime.now()
    )

    def __repr__(self):
        return '<Area {}>'.format(self._string_coords())

    def _string_coords(self):
        return str(self.latitude) + '-' + str(self.longitude)

    def as_dict(self):
        r = {c.name: getattr(self, c.name) for c in self.__table__.columns}
        r['created'] = str(r['created'])
        return r

Resource:

from flask_restful import Resource, request
from models import AreaModel

class AreaResource(Resource):
    def get(self):
        area_coords = request.args.get('area_coords')

        if not '-' in area_coords:
            return {'code': 400, 'message': 'Invalid coordinate format'}, 400

        latitude, longitude = area_coords.split('-')
        latitude, longitude = float(latitude), float(longitude)

        area = AreaModel.query.filter_by(latitude=latitude).first() # this is the line with the problem

        if not area:
            return {'code': 404, 'message': 'Area not found'}, 404

        return area.as_dict()

When I use AreaModel.query.all() I get all records in table. But if I add filter I always get None as a result and I can't understand why

Edit 1: received data (coords) is correct; type: float

Edit 2: .get() isn't working too

I suspect it would be better to store lat/long as strings - float values may not reliably round-trip through the querying process. I tried a quick test using [1.5, 1.33, 1.625, 1.11, 1.34567] and only 1.5 and 1.625 were found. — snakecharmerb
– snakecharmerb, Commented Aug 31, 2020 at 13:09
See also stackoverflow.com/questions/1302243/selecting-a-float-in-mysql and stackoverflow.com/questions/6135395/…, for example — snakecharmerb
– snakecharmerb, Commented Aug 31, 2020 at 13:16
See also dev.mysql.com/doc/refman/8.0/en/problems-with-float.html — snakecharmerb
– snakecharmerb, Commented Aug 31, 2020 at 13:22
@snakecharmerb would you mind posting your answer as an answer (not a comment), so I can mark it as the best solution? — andrewyazura
– andrewyazura, Commented Sep 2, 2020 at 4:52

snakecharmerb · Accepted Answer · 2020-09-03 09:10:11Z

Floats are tricky to work with, because the literal representation of a float may not correspond exactly to the value used in computations, or stored in a database. This behaviour is discussed in depth in the answers to this question. Specific instances of querying with floats in MySQL can be found here and here.

To work around this problem, the MySQL docs recommend using the DECIMAL type in place of FLOAT or DOUBLE.

In this particular case, I think you have choice between using DECIMAL (and the Python Decimal type) or strings to represent latitudes and longitudes.

if you intend to perform numerical computations with the values, DECIMAL is probably the better choice
if you are only querying against exact values, you could use store the values as strings.

Here is a short example demonstrating how querying with floats does not always produce expected results:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import orm

Base = declarative_base()


class FloatTest(Base):
    __tablename__ = 'floattest'

    id = sa.Column(sa.Integer, primary_key=True)
    lat = sa.Column(sa.Float)

    def __repr__(self):
        return f'{self.__class__.__name__}(lat={self.lat})'


engine = sa.create_engine('mysql:///test', echo=True)
Base.metadata.drop_all(bind=engine, checkfirst=True)
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)


floats = [1.5, 1.33, 1.625, 1.11, 1.34567]

session = Session()
for v in floats:
    session.add(FloatTest(lat=v))
session.commit()

for v in floats:
    res = session.query(FloatTest).filter(FloatTest.lat == v).first()
    print(v, res)
print()
session.close()

Output (SQL logs removed):

1.5 FloatTest(lat=1.5)
1.33 None
1.625 FloatTest(lat=1.625)
1.11 None
1.34567 None

Collectives™ on Stack Overflow

Flask SQLAlchemy filter and filter_by don't work with float values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related