For certain use-cases (like constructing queries for an ORM) or other things where you're effectively passing around curried ideas to eventually be executed, I think cascading methods is a huge win.
What you are referring to is a design pattern called fluent interfaces[1]. They do make for very usable APIs when used to represent pipelines and filters. They are also used heavily in creating domain specific language features. In your SQL example it works very well such as in SQLAlchemy. But in that example, the chained methods are building a query as opposed to mutating the actual data. Splitting hairs.
In the case of SQLAlchemy you are not. The cascading methods on query objects create new query objects as it should be. Mutating objects with cascading methods is horrible API design as it suggests immutability where there is none.
the API in some ways comes from that of Hibernate, which does actually mutate in place (see http://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html...). I felt that keeping the existing object un-mutated is a lot more intuitive. I think the names that you choose for the methods do make a difference, e.g. Hibernate is saying "add()" which for whatever reason seems to imply "mutation" to me, not sure why.
Having used hibernate extensively (and considering many of it's features remain completely unmatched in any other ORM I've encountered), I can say that I strongly dislike the idea of mutating the query in place. There's huge advantages to each chain returning an independent and valid query that can be executed, referenced, and added to. I don't see any upside to the Hibernate approach here, and it violates "Prefer Immutability".
Oh right, that's fair. Same in Django (and you're right, mutation in this case would be horrible design), I suppose I was mixing the metaphorical with the actual.
