I'm a big fan of Pyramid because (among many other reasons) I think ORMs are a poor abstraction and therefore do not want to be forced into using one (a la Django). I didn't find the learning curve terrible either--it's just MVC (er, "MVT") with some configuration/logging/routing capabilities built in.
Being able to do dynamic routing (via Traversal) is also cool. I really like being able to attach custom objects/attributes to the request object--that lets me write my own datalayer (without an ORM) and have it shared as a singleton across the lifetime of the request. It's also cool that you can write "views" (aka "controllers") either as flat functions or classes.
I personally don't write much server-side HTML munging anymore so I don't have much to say about Chameleon templates. Last time I used them I found the documentation a little lacking but otherwise it worked well. I still prefer Jade.
What do you use to query a relational database, if not an ORM? Do you build your SQL with string concatenation? In my experience that gets messy very quickly for complicated queries, so you end up needing some sort of SQL builder abstraction.
For Mongo I write a data layer that has methods like GetFoo() or UpdateFoo() where the methods have implicit knowledge of key structure (and query model objects for validation, etc)
For SQL I would do something similar but use (for example) SQLAlchemy Core (not ORM) to do DB-agnostic SQL generation. That would essentially give me a SQL API somewhat similar to pymongo.
For some things ORM's get in the way, but avoiding them by your own data layer sounds like a pretty big time-suck.
Being able to write a single line of SQLAlchemy to build relationships into your models seems way simpler than implementing relationships in a custom data layer.
I don't deny that it's more work. The question is whether it's worth the investment. I think for large, complex codebases (not little one-off apps) the gains in performance and long-term maintainability are worth it. Having the ability to know the exact performance implications of a query (and not having to waste brain cycles on questions about lazy vs eager loading, etc) is more desirable.
Being able to do dynamic routing (via Traversal) is also cool. I really like being able to attach custom objects/attributes to the request object--that lets me write my own datalayer (without an ORM) and have it shared as a singleton across the lifetime of the request. It's also cool that you can write "views" (aka "controllers") either as flat functions or classes.
I personally don't write much server-side HTML munging anymore so I don't have much to say about Chameleon templates. Last time I used them I found the documentation a little lacking but otherwise it worked well. I still prefer Jade.