SQLAlchemy, SQLite: ordering case insensitive

[ragnar]

I do a select(order_by=mytable.c.name) and it works as expected, except
that I use an sqlite database and the ordering is done case sensitive.
I read the sqlite manual and you can write "ORDER BY name COLLATE
NOCASE" to do case insensitive ordering, but how do I add the "COLLATE
NOCASE" keywords through a SQLAlchemy expression?

[Michael Bayer]

several ways to approach that, without it being built in as an
expression.

as a string:

order_by=["name COLLATE NOCASE"]

or just using the lower() function (to me this would be more obvious)

order_by=[func.lower(table.c.name)]

or you could use sql's _CompoundClause:

from sqlalchemy.sql import _CompoundClause

order_by = [_CompoundClause(None, table.c.name, "COLLATE NOCASE")]

Id go with func.lower() probably...

[ragnar]

func.lower() was indeed very nice. Thank you for the reply.

I seem to have missed the sqlalchemy.func package entirely before and
it seems to be rather sparingly documented and it isn't listed in
package contents when I do help(sqlalchemy). Is there anywhere where I
can read more about the available functions in sqlalchemy.func?

[Michael Bayer]

func is just a catchall, whatever attribute you call off of it gets
sent to __getattr__ and becomes a function call. its designed to allow
any random database-specific call or custom stored procedure.

however, it does have some extra tricks going on, in that it will
"de-parenthesize" certain ANSI calls that are known to not want
parenthesis, like "CURRENT_TIMESTAMP".

the docs are here:
http://www.sqlalchemy.org/docs/sqlconstruction.myt#sql_whereclause_functions

which you probably found, and it does need a good amount more
explanation. a. that you can call whatever name you want, and it
becomes a function b. that it knows about certain ANSI calls and c.
that you can create "selectables" out of functions now, which is useful
for calling stored procedures that return result sets.

its not in the generated docs because its just an instance variable,
that points to an instance of _FunctionGenerator which is "private".

[ragnar]

Ah, that explains it. Thanks for the answer