CODEWITHSUNDEEP
pythonflasksqlalchemy
Anar Salimkhanov
Anar Salimkhanov

Reputation: 847

How to remove a row from a query result set in SQLAlchemy?

I need a function that does something like:

query = Member.query.filter(Member.age > 3)
query = filter_query(query)

def filter_query(query):
    for q in query:
        if not q.someAttr == "someValue":
            query.remove(q) # <-- this doesn't work. is there anything similar?
    return query

Please, don't ask why I need this. It's a long story )

UPDATE

I decided to use maybe not the most beautiful but still a solution that works for now.

query = Member.query.filter(Member.age > 3)
query = filter_query(query)

def filter_query(query):
    id_list = []
    for q in query:
        if q.someAttr == "someValue":
            id_list.append(q.id)
    return Member.query.filter(Member.id.in_(id_list))

Upvotes: 0

Views: 1488

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55669

The problem is that a query must be run in Sqlite which includes an ILIKE filter on non-ASCII text, but the default Sqlite build does not support case-insensitive matching on such text.

One possible workaround is to store a case-normalised copy of the text in another column. This assumes that doubling the storage for the text is not an issue.

A simple way to do this would look like this:

class Likeable(Base):
    __tablename__ = 'likeable'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(32))
    lowercase_name = sa.Column(sa.String(32))

Instances can be created like this:

likeable = Likeable(name='Россия', lowercase_name='россия')

and queried like this

session.query(Likeable).filter(Likeable.lowercase_name.like('р%'))

This is ok, but requires that name and lowercase_name are always updated simultaneously. We can get around this by masking lowercase_name with a hybrid property, and intercepting assignments to name with an attribute listener. The listener detects when name is modified and passes the new value to lowercase_name's setter.

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import Session


Base = declarative_base()


class Likeable(Base):
    __tablename__ = 'likeable'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(32))
    _lowercase_name = sa.Column(sa.String(32))

    def __repr__(self):
        return f"Likeable(name='{self.name}', lowercase_name='{self.lowercase_name}')"

    @hybrid_property
    def lowercase_name(self):
        return self._lowercase_name

    @lowercase_name.setter
    def lowercase_name(self, value):
        # str.casefold is preferred over str.lower for case-insensitive matching
        # https://docs.python.org/3/library/stdtypes.html#str.casefold
        self._lowercase_name = value.casefold()


@sa.event.listens_for(Likeable.name, 'set')
def receive_set(target, value, oldvalue, initiator):
    target.lowercase_name = value

Running this code:

engine = sa.create_engine('sqlite:///', echo=True)
Base.metadata.create_all(bind=engine)

session = Session(bind=engine)
likeables = [Likeable(name=n) for n in ['Россия', 'Русский', 'Французский']]
session.add_all(likeables)
session.commit()
session.close()

session = Session(bind=engine)
q = session.query(Likeable).filter(Likeable.lowercase_name.like('р%'))
for r in q:
    print(r)
session.close()

Produces this output:

Likeable(name='Россия', lowercase_name='россия')
Likeable(name='Русский', lowercase_name='русский')

This is demonstration code. For production, you would want to add checks to ensure that name and lowercase_name can't get out of sync.

Upvotes: 1

Related Questions