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?
I am crossposting this from stackoverflow
<https://stackoverflow.com/questions/63564747/how-to-do-a-case-insensitive-query-for-sqlalchemy-association-proxy-attributes>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/da34bbda-deab-496d-9937-7a4d3b938616n%40googlegroups.com.
