CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Phoenix
Phoenix

Reputation: 399

String to numerical in Flask-Sqlalchemy

I have a flask application tied to a postgres db. I am filtering table Stock like following,

Stock.query.filter(Stock.availablequantity < Stock.minimumquantity)

The problem: I see wrong outputs like 23 is less than 5!!

availablequantity and minimumquantity are saved as string in db (and I can not change this for other reasons) and I don't know how to convert these quantities in the query itself. Any help is really appreciated.

Upvotes: 1

Views: 661

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55629

You can call the columns' cast method to cast (convert) to the type that you want in the query.

# from db import Integer may work in flask-sqlalchemy
from sqlalchemy import Integer

Stock.query.filter(Stock.availablequantity.cast(Integer) < Stock.minimumquantity.cast(Integer))

This will generate this SQL:

SELECT stock.id AS stock_id, stock.availablequantity AS stock_availablequantity, stock.minimumquantity AS stock_minimumquantity 
FROM stock 
WHERE CAST(stock.availablequantity AS INTEGER) < CAST(stock.minimumquantity AS INTEGER)

This isn't very efficient: if this query is being run frequently on a large table then changing the column types in the database will be a better solution.

This answer describes a way to get better performance (though indexing in the database) when dealing with queries over different types - it's about comparing strings and dates but the approach should be similar for integers.

Upvotes: 2

Related Questions