How to do a case-insensitive query for association proxy attributes?
97 views
Skip to first unread message
Jacob Pavlock
Aug 27, 2020, 2:27:26 PM8/27/20
to sqlalchemy
I have two classes, a Track and an Album. In order to have album fields easily accessible from a track, I created an association proxy attribute.
```python
class Track(MusicItem, Base):
__tablename__ = "tracks"
_id = Column(Integer, primary_key=True)
_album_id = Column(Integer, ForeignKey("albums._id"))
artist = Column(String, nullable=False, default="")
path = Column(_PathType, nullable=False, unique=True)
title = Column(String, nullable=False, default="")
_album_obj = relationship("Album", back_populates="tracks")
album = association_proxy('_album_obj', 'title')
```
```python
class Album(MusicItem, Base):
__tablename__ = "albums"
_id = Column(Integer, primary_key=True)
artist = Column(String, nullable=False, default="")
title = Column(String, nullable=False, default="")
tracks = relationship("Track", back_populates="_album_obj", cascade="all, delete")
```
However, I am having issues when trying to do a case-insensitive query for an album.
When creating a query filter, the following works fine for normal (non-association proxy) attributes of a Track.
```python
attr = getattr(Track, field)
attr = sqlalchemy.func.lower(attr)
```
But, when `attr` is an association proxy attribute, the following error occurs:
```python
moe/core/query.py:187: in query
items = session.query(query_cls).filter(*query_filters).all()
.venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py:3346: in all
return list(self)
.venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py:3508: in __iter__
return self._execute_and_instances(context)
.venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py:3533: in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1011: in execute
return meth(self, multiparams, params)
.venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:298: in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1124: in _execute_clauseelement
ret = self._execute_context(
.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1316: in _execute_context
self._handle_dbapi_exception(
.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1510: in _handle_dbapi_exception
util.raise_(
.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py:182: in raise_
raise exception
.venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1276: in _execute_context
self.dialect.do_execute(
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
self = <sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f54a0692430>
cursor = <sqlite3.Cursor object at 0x7f54a0638b90>
statement = 'SELECT tracks._id AS tracks__id, tracks._album_id AS tracks__album_id, tracks.artist AS tracks_artist, tracks.path AS tracks_path, tracks.title AS tracks_title \nFROM tracks \nWHERE lower(?) = ?'
parameters = (ColumnAssociationProxyInstance(AssociationProxy('_album_obj', 'title')), 'tmp')
context = <sqlalchemy.dialects.sqlite.base.SQLiteExecutionContext object at 0x7f54a05b7fa0>
def do_execute(self, cursor, statement, parameters, context=None):
> cursor.execute(statement, parameters)
E sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type.
E [SQL: SELECT tracks._id AS tracks__id, tracks._album_id AS tracks__album_id, tracks.artist AS tracks_artist, tracks.path AS tracks_path, tracks.title AS tracks_title
E FROM tracks
E WHERE lower(?) = ?]
E [parameters: (ColumnAssociationProxyInstance(AssociationProxy('_album_obj', 'title')), 'tmp')]
E (Background on this error at: http://sqlalche.me/e/13/rvf5)
.venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py:593: InterfaceError
```
Not sure if helpful, but here's some more simplified code on the query. I can expand on this if needed.
```python
query_filters = [attr == query_value]
items = session.query(Track).filter(*query_filters).all()
```
I have also seen this relevant piece in the docs on creating a custom comparator, but I could not figure out how to apply this to my code. I added it alongside the Track/Album classes, but it did not help anything and the same error occurs.
```
class MyComparator(ColumnOperators):
def operate(self, op, other):
return op(func.lower(self), func.lower(other))
```
What's the best way to handle case-insensitive queries with association proxy attributes?
Mike Bayer
Aug 27, 2020, 3:42:43 PM8/27/20
to noreply-spamdigest via sqlalchemy
On Thu, Aug 27, 2020, at 2:27 PM, Jacob Pavlock wrote:
I have two classes, a Track and an Album. In order to have album fields easily accessible from a track, I created an association proxy attribute.```pythonclass Track(MusicItem, Base):__tablename__ = "tracks"_id = Column(Integer, primary_key=True)_album_id = Column(Integer, ForeignKey("albums._id"))artist = Column(String, nullable=False, default="")path = Column(_PathType, nullable=False, unique=True)title = Column(String, nullable=False, default="")_album_obj = relationship("Album", back_populates="tracks")album = association_proxy('_album_obj', 'title')``````pythonclass Album(MusicItem, Base):__tablename__ = "albums"_id = Column(Integer, primary_key=True)artist = Column(String, nullable=False, default="")title = Column(String, nullable=False, default="")tracks = relationship("Track", back_populates="_album_obj", cascade="all, delete")```However, I am having issues when trying to do a case-insensitive query for an album.When creating a query filter, the following works fine for normal (non-association proxy) attributes of a Track.```pythonattr = getattr(Track, field)attr = sqlalchemy.func.lower(attr)
So there is a non-smoothness to how this is right now, but it's not very straightforward for the association proxy to do this right now and a new feature would need to be added for func.lower() to work directly in that way, returning a new expression object that would apply the "lower()" function inside of itself, since you'll note that a plain LIKE comparison produces this SQL:
SELECT tracks._id AS tracks__id, tracks._album_id AS tracks__album_id, tracks.artist AS tracks_artist, tracks.title AS tracks_title
FROM tracks
WHERE EXISTS (SELECT 1
FROM albums
WHERE albums._id = tracks._album_id AND albums.title LIKE ?)
I can't really come up with a way to make it do the right thing for that right now since it would have to be some kind of deferred object that can take func.lower() into itself somehow and generate the right output later. https://github.com/sqlalchemy/sqlalchemy/issues/5541 is added but I have nothing for it right now.
You can get case insensitive using ilike():
print(s.query(Track).filter(Track.album.ilike("%title%")).all())
SELECT tracks._id AS tracks__id, tracks._album_id AS tracks__album_id, tracks.artist AS tracks_artist, tracks.title AS tracks_title
FROM tracks
WHERE EXISTS (SELECT 1
FROM albums
WHERE albums._id = tracks._album_id AND lower(albums.title) LIKE lower(?))
--SQLAlchemy -The Python SQL Toolkit and Object Relational MapperTo post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.---You received this message because you are subscribed to the Google Groups "sqlalchemy" group.To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/da34bbda-deab-496d-9937-7a4d3b938616n%40googlegroups.com.
Jacob Pavlock
Aug 29, 2020, 7:33:33 PM8/29/20
to sqlalchemy
Mike,
Thanks a lot for the response and also for considering this as a future feature. I've switched to using `attr.ilike(value)` in the meantime and it is working great.
Reply all
Reply to author
Forward
0 new messages