Having come from a rails background and later switching to django I think it comes down to a matter of preference. Django's ORM gets the job done but leaves something to be desired (ugly syntax, no multiple database support, also watch out for implicit cascading deletes!). Conversion to using SQLAlchemy was supposed to address the gaps, but the branch for the upgrade seems pretty dead... But overall I'm pretty happy due to the transparency of django's framework code. I've implemented some customizations that would have been a lot harder to pull off in rails.
If you had to choose a python web framework, I'd suggest taking a look at pylons which is more flexible (also, by default it uses mako templating which > django's templating and supports SQLAlchemy).
I've been learning it recently and I'm not sure what to make of it, specifically the ORM component. It certainly has a good reputation as far as ORM's go, but I'm not sure if even a good ORM fits a large, popular, or complex app, or is perhaps more suitable for protoyping and low-traffic uses.
But since SQL is pretty necessary for most web apps, it makes sense to pick some abstraction layer, and as far as I can tell SQLA is best of breed for scripting languages, so I'm trying to work out how to apply it most effectively.
Oh yeah...One more thing. The good reputation of SQLAlchemy - is unfounded. Let me explain.
See it does all the basic stuff and so do the others.
Then some relatively famous programmer who is also a blogger wants to do something esoteric so they evaluate all wrappers and eventually find that SQLAlchemy can do it if you are willing to write brain damaging code.
Then because after many many hours of head banging they finally make it work they can't wait to gloat over the sustained brain damage and how SQL Alchemy rocks, so they write it up in a blog.
Then guess what, simpletons like me read the famous peoples blogs, get suitably impressed and sustain permanent brain damage, if we survive we join the bandwagon and gloat too - with good reason, this thing is impossible for humans to use. If we fail, we do what we should have done in the first place, look for alternatives.
PS: Now that I read it again it sounds like I am talking about Ruby on Rails!!
2. If you are going to use SQL, Find a wrapper that is minimalistic for your current needs. Work with lesser and you will work more but you will also work with more freedom and sanity.
Thanks for your thought-provoking comments, actually I had quite forgotten that I didn't necessarily need SQL (despite reading all the threads about that here)!
Trouble is, I understand the theory of in-memory data logged to disk, but as I'm just a humble hacker (self-taught amateur) there seem a lot of unknowns in such an approach. SQL, though a pain, is at least familiar and abundantly documented.
Guess I'll do a little more research, and a little more hacking, since I would be quite happy to drop SQL entirely!
Actually, I am self taught too and most hackers here are probably much better than me. I use python, if you do the same then try out Zope DB and schevo.org. These are Object based DBMS - non SQL. The problem like someone pointed out is that such systems are not language agnostic.
I know nothing about sql wrappers but I do know whenever I have a scaling problem it is ALWAYS the sql. I doubt if any wrapper will save me from this. As your database grows larger you need to fine tune your sql, indexes, and so on.
Having come from a rails background and later switching to django I think it comes down to a matter of preference. Django's ORM gets the job done but leaves something to be desired (ugly syntax, no multiple database support, also watch out for implicit cascading deletes!). Conversion to using SQLAlchemy was supposed to address the gaps, but the branch for the upgrade seems pretty dead... But overall I'm pretty happy due to the transparency of django's framework code. I've implemented some customizations that would have been a lot harder to pull off in rails.
If you had to choose a python web framework, I'd suggest taking a look at pylons which is more flexible (also, by default it uses mako templating which > django's templating and supports SQLAlchemy).