Yes, SQLAlchemy can do more queries than Django's ORM.
No, you are unlikely to need these in practice for most web apps (zzzeek's example (for a generic User<->Address schema) is "Give me all households in New York with exactly two occupants where neither occupant has any residences outside of the city." Really? When was the last time you had a query like this in a web app?)
Yes, SQLAlchemy is a PITA to learn and use, unless you use the Django/Rails style declarative base interface.
Yes, you will lose out on basically all of the Django infrastructure if you use SQLAlchemy: django-admin, fixtures, introspecting the schema to generate models, django-south, etc.
If you need complex queries or like writing web frameworks from scratch, SQLAlchemy is the way to go. If you just want to use Django and get your user data out of a table, then just use Django's ORM.
> No, you are unlikely to need these in practice for most web apps (zzzeek's example (for a generic User<->Address schema) is "Give me all households in New York with exactly two occupants where neither occupant has any residences outside of the city." Really? When was the last time you had a query like this in a web app?)
I write web applications in the financial analysis sector, and the general technique of writing queries where subqueries must be composed together to combine groups of coarse-grained sets into an ever more specifically narrowed down set is very common - we do reporting of a fairly complex temporally-oriented schema and we can throw up bar charts and excel spreadsheets right up on the client, straight from the DB, without loading intermediary objects into memory or anything like that. The queries are highly composed, we're using aggregates to flatten out sets, then joining to that, we're using window functions in some cases, and we can do it all straight in the ORM with not a literal scrap of SQL anywhere. When the dataset grows from 1000 rows to 1000000 we'll be able to scale since we're using the relational database as it was meant to be used.
I learned how to write those kinds of queries when I worked at MLB.com. There, we wrote scripts that generated all kinds of elements of the homepage and article pages, reams of baseball stats, game wrapups, schedules - again all pulled straight from a really, really vast schema spreading over three databases. Before I worked at MLB I barely knew what a join was.
Looking through my source here, I just counted nine such queries just in two source files related to reporting alone. If you come out to Pycon this year, come find me and I can show you some of what I do.
Obviously the example in my post/talk had to be contrived since I couldn't exactly put up code from work. Perhaps I'll try to think of a more real world example for my Pycon talk this year.
I think you just proved my point. If I had to pick problem domains for which Django's ORM is poorly suited, "complex temporally-oriented schema ... in the financial analysis sector", or something like it, would be pretty high up on the list :)
Having just read through that thread, and zzzeek's earlier post (http://www.reddit.com/r/Python/comments/olech/is_django_cons...) I still don't see that any of this has changed from the last time I read a similar thread.
My take:
Yes, SQLAlchemy can do more queries than Django's ORM.
No, you are unlikely to need these in practice for most web apps (zzzeek's example (for a generic User<->Address schema) is "Give me all households in New York with exactly two occupants where neither occupant has any residences outside of the city." Really? When was the last time you had a query like this in a web app?)
Yes, SQLAlchemy is a PITA to learn and use, unless you use the Django/Rails style declarative base interface.
Yes, you will lose out on basically all of the Django infrastructure if you use SQLAlchemy: django-admin, fixtures, introspecting the schema to generate models, django-south, etc.
If you need complex queries or like writing web frameworks from scratch, SQLAlchemy is the way to go. If you just want to use Django and get your user data out of a table, then just use Django's ORM.