Last Updated: February 25, 2016
·
1.434K
· iaincampbell

Building dynamic sqlalchemy queries with PostgreSQL

If you're querying for whether or not a string value exists in a column, be sure to make the column nullable in the model definition.

some_str = db.Column(db.String(30), nullable=True)

That way you can query as so:

if str(column_type)!='SMALLINT' and check in (0,1):
    if check==0:
        items = items.filter(func.lower(getattr(self.model, k)).is_(None))
    else:
        items = items.filter(func.lower(getattr(self.model, k)).isnot(None))

where check is a boolean value, eg from a querystring:

?some_str=1

You can't use != as you might expect, because you're building the query dynamically.