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.
>
> ```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)
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(?))
> ```
> 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/da34bbda-deab-496d-9937-7a4d3b938616n%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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/cc2bca1b-25ee-4f84-8d30-de1c87a95f10%40www.fastmail.com.
