Friday, 20 September 2019

SQLAlchemy filter_by query failing when float used

I had a model defined as

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)

and when adding new objects via an API I would first check that the new object (the post_data) did 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]
}

I have been trawling similar posts on this site but I'm going around in circles.



from SQLAlchemy filter_by query failing when float used

No comments:

Post a Comment