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

Question

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))

Thanks for the idea but didn't work ) `sqlalchemy.exc.ArgumentError: Valid strategies for session synchronization are 'evaluate', 'fetch', False` — Anar Salimkhanov, May 08 '20 at 00:06
Would it be OK to bring the query results into a list of dicts & then delete the dicts you don't want? — Ben, May 08 '20 at 01:24
Yes, but how to convert it back to BaseQuery object? I have no idea. — Anar Salimkhanov, May 08 '20 at 03:37
This question confuses me. The requirement _seems_ to be to remove an item from a query's results on the client side, but the solution in the update involves calling `filter` on the query, which will execute another query on the database. But if we are going to query the database anyway, why not just apply `filter(Model.someAttr == 'somevalue')` to the original query? — snakecharmerb, May 08 '20 at 05:56
As pointed out by @snakecharmerb, `for q in query:` executes the query (adding another `filter()` doesn't in itself), so you're running it twice. — Ilja Everilä, May 08 '20 at 06:57
In my case, filter doesn't work, because of SQLite database that contains data in cyrillic. This is a real pain ) And hybrid_method doesn't work either. That's why I need to loop BaseQuery object and remove some objects from it and return BaseQuery object. Thanks ) — Anar Salimkhanov, May 08 '20 at 14:25
"filter doesn't work, because of SQLite database that contains data in cyrillic" - can you elaborate on this? I didn't think sqlite had any limitations for unicode (except maybe case-insensitive matching). — snakecharmerb, May 08 '20 at 14:58
Yes, the problem occurs with case insensitive `like` query ) Unfortunately, I can't find the page that I found a few days ago, where it's said something like "supporting case insensitive querying of cyrillic text would double the size of SQLite file" — Anar Salimkhanov, May 08 '20 at 17:09
That's unlucky :( So the problem is: You retrieve a list of model instances, you perform the equivalent of a case-insensitive `like`, in python, and now you specifically need to transform the filtered results back into a `query` object because you need perform further filter/grouping/ordering in the database? — snakecharmerb, May 08 '20 at 17:22
Exactly ) I was surprised to know nothing like `remove()`, `delete()`, `del` can remove an object from BaseQuery object. — Anar Salimkhanov, May 08 '20 at 17:35
The trouble is, the values aren't there to be tested until the `query` is executed, and at that point you no longer have a `query`, just a list of objects. — snakecharmerb, May 08 '20 at 17:46

snakecharmerb · Answer 1 · 2020-11-26T19:29:03.657

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.

Great! I'll check it as soon as possible. There is another problem: the SQLlite file, in my case, is generated by mdb-export that exports data from MDB file. I'll see how it works in my project. Thanks for the tip. — Anar Salimkhanov, May 09 '20 at 12:49
A neater approach might be to [use a case-insensitive collation](https://stackoverflow.com/a/70794113/5320906). — snakecharmerb, Apr 02 '22 at 05:41

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

1 Answers1