On Jan 3, 2014, at 11:53 AM, Mark Bird <[email protected]> wrote:
> I am using SQLAlchemy 0.8.1 with a Postgres backend, and have noticed that
> the ilike operator does not seem to be compiling to an ILIKE operator in raw
> SQL, but instead it is doing lower() on both sides of the comparison:
>
> E.g. (names changed to protect the innocent)
>
> >> s = session()
>
> >> print s.connection().engine
> Engine(postgresql://user:passwd@server:port/database)
>
> >> q = s.query(Table).filter(Table.column.ilike("FISH%"))
>
> >> print q
> SELECT column
> FROM table
> WHERE lower(column) LIKE lower(:column_1)
>
> This could be a bug with what is printing out the SQL? I looked at the source
> code and the Postgres dialect is supposed to use ILIKE here. Any idea why
> this is not happening?
the select() construct produced by the Query is not bound to the engine’s
session, so it has no dialect and thus calling str() will use the
DefaultDialect. Compiling it with the Postgresql dialect produces the desired
result, which is the same as that which would occur if you executed the
statement against the postgresql-bound engine.
>>> from sqlalchemy import create_engine
>>> from sqlalchemy import Table, Column, MetaData, String
>>> t1 = Table('t1', MetaData(), Column('data', String))
>>> engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
>>> from sqlalchemy.orm import Session
>>> q = Session().query(t1).filter(t1.c.data.ilike('HELLO%'))
>>> print q
SELECT t1.data AS t1_data
FROM t1
WHERE lower(t1.data) LIKE lower(:data_1)
>>> print q.statement.compile(engine)
SELECT t1.data
FROM t1
WHERE t1.data ILIKE %(data_1)s
>
> --
> 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 post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
