Thanks, that's brilliant. Very much obliged for the fast response. I can
now resolve an issue as nothing to be done, which is always nice :)
On 3 January 2014 18:00, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
> On Jan 3, 2014, at 11:53 AM, Mark Bird <mark.a.b...@gmail.com> 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 sqlalchemy+unsubscr...@googlegroups.com.
>
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
