SQLalchemy is kind of a pain in the ass to actually use, though. The DSL doesn't feel very Pythonic, it's weird and confusing. The way it traverses the Object graph when loading associations between models is magical and opaque and I could never predict when it was going to automatically work and when it wouldn't.
I actually would rather be writing Ruby on Rails, because it's _less magic_ than SQLalchemy.
But my very point was not to make it traverse object graphs at all. You can write nice SQL using it, with selects, joins, functions, etc. All these parts, SQL clauses, are composable, so you can factor out common parts.
Yes, I'm fine working with lists of tuples, not "model objects". Object graphs don't map all to well to the RDBMS model all too well. It's best done on case-by-case basis, if you care about performance at all.
If you aim to have a pythonic data layer, I've heard good things about pony orm[1]. I mean,you can't get much more pythonic then the example they have on their website. But I haven't used it my self.
The one that bit me with sqlalchemy is joins. You have to structure your sqlalchemy object in a specific way to do joins. Usually I write my sql query then spend half an hour trying to convert it to sqlalchemy.
I actually would rather be writing Ruby on Rails, because it's _less magic_ than SQLalchemy.