Jakub M. Jakub M. - 4 months ago 18
Python Question

How to filter in `sqlalchemy` by string length?

How to filter in

sqlalchemy
by string length?

This code snippet:

sess.query(db.ArticlesTable).filter(or_(
and_(db.ArticlesTable.shorttext.length > 0),
...


gave me the following error:

File "./aggregate_news.py", line 69, in is_acceptable
db.ArticlesTable.shorttext.length > 0),
File ".../sqlalchemy/orm/attributes.py", line 211, in __getattr__
key)
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator'
object associated with ArticlesTable.shorttext has an attribute 'length'


Where
ArticlesTable
is:

class ArticlesTable(Base):
__tablename__ = TABLE_ARTICLES
id = Column(Integer, primary_key=True)
shorttext = Column(String)
...

Answer

You need to use the func SQL function generator to create a LENGTH() function:

from sqlalchemy.sql.expression import func

sess.query(db.ArticlesTable).filter(or_(
    and_(func.length(db.ArticlesTable.shorttext) > 0),
Comments