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 <[email protected]> wrote: > > 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. > > > -- 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.
