SQLAlchemy filter_by query failing when float used

Question

I have a model defined as follows:

class EnergyProfiles(db.Model):
    __tablename__ = "energy_profiles"
    id = db.Column(db.Integer, primary_key=True)
    device_id = db.Column(db.String(64), index=True, unique=False, nullable=False)
    device_hardware = db.Column(db.String(64), index=True, unique=False, nullable=False)
    location = db.Column(db.String(64), index=True, unique=False, nullable=False)
    time = db.Column(db.String(64), index=True, unique=False, nullable=False)
    accompanied = db.Column(db.Boolean)
    wellbeing = db.Column(db.String(64), index=True, unique=False, nullable=False)
    battery = db.Column(db.Integer, index=True, unique=False, nullable=False)

When I add new objects via an API I would like to check that the new object (the post_data) does not already exist. This check was easy with

energy_profile_existing = EnergyProfiles.query.filter_by(**post_data).first()

After changing the battery column type, however, from db.Integer to db.ARRAY(db.Float()) the previous query.filter_by fails with a postgres error (ignore the user text below, it's docker compose output logs)

 operator does not exist: double precision[] = numeric[]
user_1           | LINE 3: WHERE energy_profiles.battery = ARRAY[0.1,20.1]
user_1           |                                       ^
user_1           | HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

The post_data contains battery as part of the JSON object e.g.

{
    "device_id": "CP99",
    "device_hardware": "Pycom",
    "location": "irregular",
    "time": "daytime",
    "accompanied": false,
    "wellbeing": "ok",
    "battery": [0.11, 35.22]
}

Based on the error message, I am guessing that the array values in your db are stored as double precision but sqlalchemy interprets the array values from your json object as numeric. You might be able to resolve the issue by casting the array to numeric (but that will complicate your filter syntax a bit) or by converting your db array column to a numeric array. — benvc
– benvc, Commented Sep 19, 2019 at 14:33
@benvc Not SQLA, but Postgres itself, when passed an array of numeric constants (the ARRAY[0.1, 20.1]): "A numeric constant ...; otherwise it is taken to be type numeric. Constants that contain decimal points and/or exponents are always initially presumed to be type numeric." — Ilja Everilä
– Ilja Everilä, Commented Sep 21, 2019 at 19:47

John Moutafis · Accepted Answer · 2019-09-26 08:45:32Z

1

+100

As @benvc and @IljaEverilä pointed out when you pass an array without any explicit casting containing non-integer numeric constants, it is assumed that they are of type numeric.

What you can do is cast the array's contents to float and to do that you can use the array([]) literal to declare the array and cast() it to ARRAY(Float) using the datatype:

{
    "device_id": "CP99",
    "device_hardware": "Pycom",
    "location": "irregular",
    "time": "daytime",
    "accompanied": false,
    "wellbeing": "ok",
    "battery": cast(array([0.11, 35.22]), ARRAY(Float))
}

answered Sep 26, 2019 at 8:45

John Moutafis

23.3k11 gold badges74 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Philip O'Brien Over a year ago

works a charm, thank you (and @benvc and @IljaEverilä) for the excellent explanation!

Collectives™ on Stack Overflow

SQLAlchemy filter_by query failing when float used

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related