State of web programming in python:
Django: More powerful? No. Faster, more seductive. In the long run you'll outgrow it, if you are any good. It gets you from zero to twenty five percent almost instantly, then gets in your way. The ORM is a terrible mess. The template language is easily the worst (slowest by 10x, slow enough that common templates take 300ms to render, insanely unusable 500 line tracebacks), of any of the common python templating languages. Once you swap out the ORM and the templates, you suddenly realize what is left is also a piece of crap. Django middleware are horrid. I remember trying to write a database session middleware and an error-reporting middleware in django, only to determine it is literally impossible (there are edge cases where any middleware can be skipped and never see the request). When having to deal with it, I use it's oddball WSGI adapter and park WSGI middleware in front if it. I can not say enough bad things about django, it is rotten through and through compared to the alternatives. The admin interface is neat, but is basically a toy once you run into real complexity or data volume. I might use django if I were making a content based application on contract, since the admin interface would be a huge piece of free functionality, or a very simple web app, like a hotel website or something.
Pylons: WSGI based, thank the lord. This means you can write real, flexible, efficient, predictable middleware. I was a huge Pylons fan from around the beginning of 07 until I discovered Werkzeug near the end of 08. I still lurk in #pylons quite a bit, and it was at my urging that the Banquet library (a common interface to all template systems) was discarded (although it may have been on it's way out anyway). Pylons uses great libraries under the hood (webob, beaker, etc), and I would recommend it without hesitation. The __call__ method of the BaseController class used to be a complete mess, I would guess that it still is. Luckily you only run afoul of it very occasionally. the __before__ and __after__ methods are handy. When I stopped using it they were still recommending AuthKit officially, but the mood of #pylons seemed to be (and I agree) that AuthKit is a horrifying abortion that should never be used. The canonical stack with Pylons is Pylons-SQLAlchemy-Mako. I would swap Mako with Jinja2, although who use Mako tend to be loyal to it, and there aren't too many huge warts (the way it does template inheritance makes my head hurt though). TLDR-Pylons good.
Werkzeug/Flask: My personal favorite. You are building a web application, you should have all the moving parts where you can adjust them as necessary without bolting on bizarre hacks, and Werkzeug lets you do that. It may be a bit of a jump for a beginner. Flask is an attempt to make a 'framework' out of Werkzeug, and it seems to be getting some momentum. If you know what you are doing, I believe Werkzeug is the best thing out there for Python web programming.
Tornado/Twisted: Either is great. Due to the pain of dealing with them (you need special event driven db adapters, etc) I would only use them for long-polling applications or the long polling portions of a general application.
Also rans: Turbogears: crap, web.py: crap. Also also rans: Zope (if you don't know what it is already, you probably don't want it).
How to deploy:
mod_wsgi for Apache is the one true way to deploy python web applications. Anything else (fastcgi, reverse proxy, etc etc) is a hard to maintain, unreliable, mess. I would put mod_wsgi on each webserver, and have a machine running nginx or varnish or pound as a load balancer. Varnish is the best if you want to do sophisticated caching or edge side includes, nginx is easy and fine also. Everybody seems to want to argue for something besides mod_wsgi, since Apache is so unfashionable. Part of me wants to encourage everyone to do so, because it makes it drop dead easy for me to walk in and 'fix' your apps reliability problems in about a half a day, and look like a genius in the process. You can deploy python applications to run multiple threads per instance, but realistically due to technical limitations of the interpreter you don't want to run many threads. I suspect less than 10 is the optimal amount. Luckily you can have mod_wsgi spin up multiple processes as well. I would have it recycle the processes after around every 25000 requests, even if you don't have any memory leaks in your app.
I recommend SQLAlchemy. There isn't really anything else worth mentioning. Even if you hate ORMs (there is a strong argument for and against), the sql expression language SQLAlchemy gives you is basically the most amazing thing there is. It takes quite a bit of time to master. If you use SQLAlchemy, use the defaults for the Session until you understand it, or you will end up spending a lot of time and trouble mucking with it.
Nosql: I don't trust it. I suppose I might be becoming a dinosaur, but I also haven't had a major use case where I really needed it.
Other: These don't really have to do with python specifically, but I'll throw them in at the end here:
I recommend Redis over Memcached. I strongly recommend Postgresql over Mysql (unless you don't understand what a join is). If you have lots of images (say over 2000), put them in S3 from day one. Some stopgap solution is more painful to fix later than earlier.
Django is incredibly easy to get going, and you can rewrite bits of it as you need to. It's reasonably easy to switch out the templating language if you need to, and I haven't had any problems like you describe with the ORM or admin app. For prototyping apps I wouldn't use anything else. I'm currently running with nginx/FastCGI and haven't seen any problems.
Pylons and SQLAlchemy, on the other hand, I found hideous - lots and lots of boilerplate, particularly when setting up models, weird tracebacks that had no relation to the original problem, poorly integrated bits - I basically couldn't populate databases from a unit test, then run a controller, then check the results, since SQLAlchemy would lose the connection after the controller.
What you say makes no sense. SQLAlchemy just uses the usual adapter for whatever database you are using. What does "loses the connection" mean to you? SQLAlchemy, by default, uses a pool of connections. I suspect you tried it for an hour and gave up without really understanding what you were doing.
Maybe there's a way to do it properly within SQLAlchemy, but if so it's completely non-obvious from a few days of searching the web, Pylons documentation, Pylons book, mailing list, etc.. I wasn't trying to do anything fancy, just set up a very basic unit test.
Here's one of my old comments with some more detail:
"Instance <Foo at 0x103779f90> is not bound to a Session" was the error that I was getting.
The following is more of a screed than a personal response to your post, so please don't take it personally.
This is something I run into quite a bit more than you might expect. Partly it is because I used to spend a lot of time in #sqlalchemy, #pylons, #pocoo, #python, and #postgresql on freenode (I'm 'ltbarcly' on freenode, feel free to msg me HNers!). For some reason it is common to believe that you can sit down and write applications that use an ORM and relational database without knowing both the ORM and relational databases.
If you are going to use relational databases, you should be prepared to become an expert in SQL, Database drivers, unicode (and why utf-8 or any other encoding isn't the same as unicode, in the same way that jpg or gif isn't the same as image data), indexing, data types, normalization tradeoffs, transaction isolation levels, locking semantics, ACID tradeoffs, how to read and understand query planner output, and much more. This is a lot to know, but if it were a college class it would be less than a semester long.
If you are going to use an ORM you have to know everything you would need when you use a relational database, and in addition how and when the ORM performs queries, how it maps to the database, what queries it will produce and when for any given action or attribute access, how db transactions map into the orm operations, how to perform common SQL operations via the orm (rather than loading more data than you need and filtering or joining via the application).
This however does work in Django - presumably by somehow passing the same DB connection to the test server.
- plugin ecosystem. Not necessarily at the beginning, but tha architecture should make it easy enough to add plugins (or apps, or extensions or whatever they are called by the framework) that developers feel encouraged to do so.
- high profile sites or projects, or at least a high volume of sites, so developers can answer manager's question, "Well, who uses it ?". Again, no framework will have this at the beginning but it will need this to get big (bit of a chicken and egg situation).
- good documentation, preferably but not exclusively online. Good == well written, up to date, well organized and consistent.
- good marketing: maintainers who can blow their own trumpet in the blogosphere, conferences, books, Twitter etc etc.
Now the code may be crap, and it may not have this feature or that feature, or it may or may not be easy to extend or customize. But the above points are required if it is to stand a chance.
Django, despite the flaws you mention (and I agree with some of them, and could add a few of my own) meets these criteria. It has the sites, the documentation, a healthy ecosystem of apps (though of varying quality), and is marketed by people who work, or used to work, in the newspaper business.
Flask, as an up-and-coming framework, is just a couple months old, so obviously doesn't have the ecosystem or the high profile/high volume yet. But it is well-positioned to take off : the documentation is excellent, and it has a good plugin architecture, with about a dozen or so "official" extensions already. Whether or not it does take off depends on many other factors, but I'd say it's future looks quite promising. It may need some aggressive marketing, or at least one or two high profile sites.
Pylons, OTOH, doesn't look so healthy. Documentation, last I looked, was out of date, poorly organized, and contradictory (AuthKit is recommended in the docs, newbies use AuthKit, get confused by its crapiness, go on IRC, and get told "don't use Authkit" - why is it still in the docs at all ?).
It's not so easy to add plugins, except at the WSGI level, which is often the wrong level of abstraction, plus there is the confusion of "entry points" and how to make it all work with Paster. As a result (and also because of the pisspoor docs) despite being around almost as long as Django, Pylons doesn't have a good plugin ecosystem. It has one high profile site - Reddit - but otherwise for the time it's been around I can't think of many others.
My default stack is Werkzeug+SQLAlchemy+Jinja2. Deployed on Apache/mod_wsgi. If a customer wants Windows/IIS, I deploy using isapi-wsgi.